AnsiSqlGenerator causes SQL syntax error for "where ... in (new select...)" statement

[blankers]

Problem: duplicate clause is being generated by the following SqlQuery object when using AnsiSqlGenerator:

SubSonic.SqlQuery q = new Select()
  .From(Views.VwSearchIndexQuery2Mtx)
  .Paged(pageNumber, maximumRows)
  .Where(VwSearchIndexQuery2Mtx.Columns.SearchIndexQueryId)
    .In(
        new Select(SearchIndexQueryGroupMap.Columns.SearchIndexQueryId)
          .From(Tables.SearchIndexQueryGroupMap)
          .Where(SearchIndexQueryGroupMap.Columns.SearchIndexQueryGroupId)
          .IsEqualTo(searchIndexQueryGroupId));

This statement works using the Sql2005 & Sql2008 generators.

Reference: http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme

[The-Running-Dev]

This is the same issue that's caused by the one documented in issue 7. The IsSQL2008 function returns false if you are using R2 or SP1 of SQL Server 2008.

[lenrock]

how to fix:

in ANSISqlGenerator.cs > virtual string BuildPagedSelectStatement() change string tweakedWheres = wheres.Replace("WHERE", "AND"); to: string tweakedWheres = Sugar.Strings.ReplaceFirst(wheres, "WHERE", "AND", StringComparison.OrdinalIgnoreCase);

and add to sugar > String new method :

///

    /// Replace only first match
    /// </summary>


    /// <param name="originalString"></param>
    /// <param name="oldValue"></param>
    /// <param name="newValue"></param>
    /// <param name="comparisonType"></param>
    /// <returns></returns>
    public static string ReplaceFirst(string originalString, string oldValue, string newValue, StringComparison comparisonType)
    {
        int startIndex = 0;

            startIndex = originalString.IndexOf(oldValue, startIndex, comparisonType);
            if (startIndex != -1)
            {

                originalString = originalString.Substring(0, startIndex) + newValue + originalString.Substring(startIndex + oldValue.Length);

                startIndex += newValue.Length;
            }


        return originalString;
    }