SubSonic 2.2 SqlQuery 对象为 SQL Server 2008 和 SQL Server 2005 的 WHERE...IN 语句生成非常不同的 sql

Question

有谁知道为什么 SubSonic 2.2 SubSonic.SqlQuery 对象在针对 SQL Server 2005 或 SQL Server 2008 运行时会为相同的 C# 代码生成非常不同的 sql？

我有一个网站在 SubSonic 2.2/SQL Server 2005 上运行了一段时间。我刚刚将数据库升级到 mssql 2008，但遇到以下错误：

SqlException（0x80131904）：关键字“AND”附近的语法不正确

我在失败时转储了 SqlQuery.ToString()，并注意到在 SQL Server 2005 和 SQL Server 2008 上运行完全相同的代码库之间存在以下差异。源代码：

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));

SQL Server 2005 的自动生成的 sql 是：

 SELECT * FROM     
(SELECT ROW_NUMBER() OVER ( ORDER BY CreatedOn DESC ) AS Row
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchTerms]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[DaysMonitored]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Incidents]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Relevance]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Deleted]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[LastUpdatedTime]     
FROM [dbo].[Vw_SearchIndexQuery2_Mtx]    
WHERE [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
FROM [dbo].[SearchIndexQueryGroup_Map]  
WHERE [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] =   @SearchIndexQueryGroupId0 )  )             
AS PagedResults WHERE  Row >= 1 AND Row <= 20

SQL Server 2008 的自动生成的 sql：

DECLARE @Page int      
DECLARE @PageSize int       
SET @Page = 1      
SET @PageSize = 20       
SET NOCOUNT ON       
-- create a temp table to hold order ids      
DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)       
-- insert the table ids and row numbers into the memory table      
INSERT INTO @TempTable      (        _keyID      )      
SELECT        [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]           
 FROM [dbo].[Vw_SearchIndexQuery2_Mtx]  
 WHERE [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
 IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
  FROM [dbo].[SearchIndexQueryGroup_Map]  
  WHERE [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] 
                         = @SearchIndexQueryGroupId0 
    ) 
   /* it's at this next AND where the error is thrown */
 AND [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
 IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
  FROM [dbo].[SearchIndexQueryGroup_Map]  
  AND [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] 
                         = @SearchIndexQueryGroupId0 
                  )  
ORDER BY CreatedOn DESC       
-- select only those rows belonging to the proper page          
SELECT [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchTerms]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[DaysMonitored]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Incidents]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Relevance]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Deleted]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[LastUpdatedTime]   
FROM [dbo].[Vw_SearchIndexQuery2_Mtx]       
INNER JOIN @TempTable t ON [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] =    t._keyID      
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

我知道为什么会发生错误 - 通过我上面评论的 AND ，该 sql 无效。我只是不明白为什么 SubSonic 在 SQL Server 2008 上工作后会生成无效的 SQL。您会看到，对于 SQL Server 2008，它使用临时表，而且看起来它也重复了 WHERE...IN 子-询问。我想可能是 ISO 兼容级别，因为升级后的 DB 设置为 100。所以我测试了将其设置为 90 和 90。 80 和 SubSonic 生成与每种情况相同的 sql。 （顺便说一句，为 SQL Server 2005 生成的使用“select rownumber() over...as row”的代码在 SQL Server 2008 上执行得很好。）

有谁知道为什么会发生这种情况以及如何追踪它？

非常感谢，

特里

Answer 1

这可能已经在源代码中修复了？我建议您尝试 github 中的最新源代码，看看问题是否已得到解决。

基本上，正如您所说 - 2005 生成器重写了 ANSISqlGenerator 提供的 BuildPagedSelectStatement() 方法。 2008生成器继承自2005，因此它应该使用与2005相同的分页方法。

这可以通过比较这些两个 文件：

我相信您还会看到您在 SQL 2000 数据库上描述的行为，因为它不会覆盖 ANSISqlGenerator.BuildPagedSelectStatement （） 方法。

另外，我认为没有使用兼容性级别 确定 SQL 生成器：

    public static bool IsSql2008(DataProvider provider)
    {
        return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 ||
        provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1;
    }

并且，假设 ANSI 生成器 是实际使用的，AND 的问题可能是由 BuildPagedSqlStatement() 中的这一行引起的：

    //have to doctor the wheres, since we're using a WHERE in the paging
    //bits. So change all "WHERE" to "AND"
    string tweakedWheres = wheres.Replace("WHERE", "AND");

它需要比这更聪明，尽管您提出的关于重复子句的观点也需要有待解决。

Answer 2

刚刚遇到这个错误，很高兴我找到了这个页面，因为我不知道为什么会发生这种情况。

运行 SQL Server 2008 SP1 似乎会导致 IsSql2008 为 FALSE。由于返回的 DatabaseVersion （在我的计算机上）返回为“Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) ...”，这不符合 IsSql2008 函数的标准。

看起来这个问题在 github 代码库上还没有得到解决？

为什么不做一些更简单的事情，比如

if (provider.DatabaseVersion.Contains("SQL Server 2008"))

Answer 3

此问题记录在此处。我刚刚分叉并提交了修复程序，因此应该很快就会包含在内。