Linq to SQL在使用TOP和

Question

我使用Linq到Sql (实际上是动态Linq到SQL，允许您在运行时传递字符串，用于where子句、orderby等)。但是我得到了一些不同的结果，这似乎是基于底层的to是使用TOP关键字还是在中间使用。

我试图把这个问题分解成一个小例子，下面是一个场景：

我使用的是存储库模式和下面的方法，该方法简单地将两个表与左外部联接连接起来。

   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

这是我所期望的，是第6至10项。

page1的基本The是

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的基本The是

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]

注意到它在之间使用

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

 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

因此，简单地说，当TOP在之间使用而不是顶层时，我得到了预期的结果。

我在我的应用程序中使用了筛选(where子句)、排序(orderBy)和分页(跳过和接受)，并且需要相当通用地处理这个问题。

• 是否有办法强迫LINQ使用语法之间的而不是顶级？
• 是否应该以不同的方式处理？

F 229

为这个长邮差道歉。

你好，西蒙

Answer 1

无论如何生成SQL (LINQ或其他)，如果ORDER BY列具有重复的值，则每次运行查询时都可以获得不同的结果。

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

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

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