使用 TOP 和 Between 时，Linq to SQL 提供不同的结果

Question

我正在使用 Linq to Sql （实际上它是 Dynamic Linq to SQL，允许您在运行时为 where 子句、orderby 等传递字符串）但是我得到了一些不同的结果，它似乎基于底层 T 是否-SQL 使用 TOP 关键字或使用 BETWEEN。

我尝试将问题分解为一个小示例，场景如下：

我正在使用存储库模式和以下方法，该方法只需使用左外连接来连接 2 个表。

   public IQueryable<TestGalleryViewModel> FetchGalleryItems()
    {

        var galleryItems = from painting in Gallery
                           join artist in Artists
                               on painting.ArtistID equals artist.ArtistID

                           into paintingArtists
                           from artist in paintingArtists.DefaultIfEmpty()

                           select new TestGalleryViewModel
                           {

                               Id = painting.PaintingID,
                               ArtistName = artist == default(Artist) ? "" : artist.Surname + " " + artist.Forenames,
                           };

        return galleryItems;
    }

然后，我有一个使用 FetchGalleryItems 方法的小测试方法：

        var query = respository.Test_FetchGalleryItems().Where("ArtistName.Contains(\"Adams Charles James\")");

        var orderedlist = query.OrderBy("ArtistName asc");
        var page1 = orderedlist.Skip(0).Take(5);
        var page2 = orderedlist.Skip(5).Take(5);

OrderedList 包含以下基础值：

176 ADAMS Charles James
620 ADAMS Charles James
621 ADAMS Charles James
660 ADAMS Charles James
683 ADAMS Charles James
707 ADAMS Charles James
735 ADAMS Charles James
739 ADAMS Charles James
740 ADAMS Charles James
741 ADAMS Charles James

这是我所期望的。但是 page1 包含的

707 ADAMS Charles James
683 ADAMS Charles James
660 ADAMS Charles James
621 ADAMS Charles James
620 ADAMS Charles James

内容如您所见不是前 5 项。 Page2 包含

707 ADAMS Charles James
735 ADAMS Charles James
739 ADAMS Charles James
740 ADAMS Charles James
741 ADAMS Charles James

Whis 是我所期望的，它是第 6 到 10 项。

page1 的基础 T-SQL 是

SELECT TOP (5) [t3].[PaintingID] AS [Id], [t3].[value] AS [ArtistName]
FROM (
    SELECT [t0].[PaintingID], 
        (CASE 
            WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(101),'')
            ELSE ([t2].[Surname] + ' ') + [t2].[Forenames]
         END) AS [value]
    FROM [dbo].[Gallery] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ArtistID], [t1].[Surname], [t1].[Forenames]
        FROM [dbo].[Artists] AS [t1]
        ) AS [t2] ON [t0].[ArtistID] = ([t2].[ArtistID])
    ) AS [t3]
WHERE [t3].[value] LIKE '%Adams Charles James%'
ORDER BY [t3].[value]

注意它使用 TOP(5)

page2 的基础 T-SQL 是

SELECT [t4].[PaintingID] AS [Id], [t4].[value] AS [ArtistName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t3].[value], [t3].[Surname], [t3].[Forenames]) AS [ROW_NUMBER], [t3].[PaintingID], [t3].[value]
    FROM (
        SELECT [t0].[PaintingID], 
            (CASE 
                WHEN [t2].[test] IS NULL THEN CONVERT(NVarChar(101),'')
                ELSE ([t2].[Surname] + ' ') + [t2].[Forenames]
             END) AS [value], [t2].[Surname], [t2].[Forenames]
        FROM [dbo].[Gallery] AS [t0]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t1].[ArtistID], [t1].[Surname], [t1].[Forenames]
            FROM [dbo].[Artists] AS [t1]
            ) AS [t2] ON [t0].[ArtistID] = ([t2].[ArtistID])
        ) AS [t3]
    WHERE [t3].[value] LIKE '%Adams Charles James%'
    ) AS [t4]
WHERE [t4].[ROW_NUMBER] BETWEEN 5 + 1 AND 5 + 5
ORDER BY [t4].[ROW_NUMBER]

注意它使用 BETWEEN

当我将 T-SQL 命令粘贴到 SQL Express Management Studio 中时，我得到了我所描述的结果。如果我使用 page2 T-SQL 并将该行修改

 WHERE [t4].[ROW_NUMBER] BETWEEN 5 + 1 AND 5 + 5

为，

WHERE [t4].[ROW_NUMBER] BETWEEN 1 AND 5

我会得到我期望的 page1 结果。即前 5 项。

176 ADAMS Charles James
620 ADAMS Charles James
621 ADAMS Charles James
660 ADAMS Charles James
683 ADAMS Charles James

简而言之，当 T-SQL 使用 Between 而不是 TOP 时，我得到了我预期的结果。

我在我的应用程序中使用过滤（where 子句）、排序（orderBy）和分页（跳过和获取），并且需要相当通用地处理这个问题。

• 有没有办法强制使用 LINQ BETWEEN 语法而不是 顶部？
• 我应该接近这个吗 不同？

对于这么长的帖子表示歉意。

问候， 西蒙

Answer 1

无论 SQL 是如何生成的（LINQ 或其他方式），如果您 ORDER BY 具有重复值的列，则每次运行查询时都会得到不同的结果。

当您ORDER BY [t3].[value]时，您正在对包含许多重复值的列进行排序。

您可以通过从 Management Studio 运行一个非常简单的 SQL SELECT 来测试这一点。每次运行它，您都会得到不同的结果。

获得一致结果的一种方法是像您所做的那样使用 ROW_NUMBER。或者，向 ORDER BY 添加唯一的任何其他列将导致结果始终以相同的顺序返回。其他列是否与您的查询有任何关系并不重要，重要的是它是唯一的。