实体框架以意外顺序组成SQL

Question

我有一个查询，必须从有序查询中获取前15个项目，以符合一些细节。所以我正在做类似的事情：

var ten = repository
            .Orders
            .OrderByDescending(p => p.ClerkCode)
            .Select(o=>o.OrderId)
            .Take(10)
            ;
var orders = repository
                .Orders
                .Where(o => ten.Contains(o.OrderId))
                .Include(o => o.Products);

因此，子查询获得了所有符合条件的项目，而主查询只是完成了信息。这会生成以下查询：

SELECT
[Project2].[OrderId] AS [OrderId],
[Project2].[CustomerId] AS [CustomerId],
[Project2].[ClerkCode] AS [ClerkCode],
[Project2].[C1] AS [C1],
[Project2].[ProductOrderId] AS [ProductOrderId],
[Project2].[ProductId] AS [ProductId],
[Project2].[OrderId1] AS [OrderId1],
[Project2].[Quantity] AS [Quantity]
FROM ( SELECT
    [Extent1].[OrderId] AS [OrderId],
    [Extent1].[CustomerId] AS [CustomerId],
    [Extent1].[ClerkCode] AS [ClerkCode],
    [Extent2].[ProductOrderId] AS [ProductOrderId],
    [Extent2].[ProductId] AS [ProductId],
    [Extent2].[OrderId] AS [OrderId1],
    [Extent2].[Quantity] AS [Quantity],
    CASE WHEN ([Extent2].[ProductOrderId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Orders] AS [Extent1]
    LEFT OUTER JOIN [dbo].[ProductOrders] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[OrderId]
    WHERE  EXISTS (SELECT
        1 AS [C1]
        FROM ( SELECT TOP (10) [Extent3].[OrderId] AS [OrderId]
            FROM [dbo].[Orders] AS [Extent3]
            ORDER BY [Extent3].[ClerkCode] DESC
        )  AS [Limit1]
        WHERE [Limit1].[OrderId] = [Extent1].[OrderId]
    )
)  AS [Project2]
ORDER BY [Project2].[OrderId] ASC, [Project2].[C1] ASC

我可以看到是在有序子查询上进行select top。

现在出现了问题。我想拿10个不同的物品，所以我将查询更改为：

var ten = repository
            .Orders
            .OrderByDescending(p => p.ClerkCode)
            .Select(o=>o.OrderId)
            .Distinct()
            .Take(10)
            ;
var orders = repository
                .Orders
                .Where(o => ten.Contains(o.OrderId))
                .Include(o => o.Products);

现在生成的SQL就是这样：

SELECT
[Project2].[OrderId] AS [OrderId],
[Project2].[CustomerId] AS [CustomerId],
[Project2].[ClerkCode] AS [ClerkCode],
[Project2].[C1] AS [C1],
[Project2].[ProductOrderId] AS [ProductOrderId],
[Project2].[ProductId] AS [ProductId],
[Project2].[OrderId1] AS [OrderId1],
[Project2].[Quantity] AS [Quantity]
FROM ( SELECT
    [Extent1].[OrderId] AS [OrderId],
    [Extent1].[CustomerId] AS [CustomerId],
    [Extent1].[ClerkCode] AS [ClerkCode],
    [Extent2].[ProductOrderId] AS [ProductOrderId],
    [Extent2].[ProductId] AS [ProductId],
    [Extent2].[OrderId] AS [OrderId1],
    [Extent2].[Quantity] AS [Quantity],
    CASE WHEN ([Extent2].[ProductOrderId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Orders] AS [Extent1]
    LEFT OUTER JOIN [dbo].[ProductOrders] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[OrderId]
    WHERE  EXISTS (SELECT
        1 AS [C1]
        FROM ( SELECT TOP (10) [c].[OrderId] AS [OrderId]
            FROM [dbo].[Orders] AS [c]
        )  AS [Limit1]
        WHERE [Limit1].[OrderId] = [Extent1].[OrderId]
    )
)  AS [Project2]
ORDER BY [Project2].[OrderId] ASC, [Project2].[C1] ASC

现在，执行select top的子查询不是由ClerkCode订购的。

我该如何修复？

Answer 1

预期的行为是它返回源中唯一项目的无序序列。

- https://msdn.microsoft.com/en-us/library/bb348456

---

它取决于实现，但是Distinct似乎可以清除任何先前设置的订购。