CODEWITHSUNDEEP
sqlsql-serverentity-frameworkentity-framework-6
vtortola
vtortola

Reputation: 35945

Entity Framework composing SQL in unexpected order

I have a query that must get the top 15 items from a ordered query that fulfill some details. So I was doing something like:

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

So the subquery gets all the items that qualifies, and main query just completes the information. This generates the following query:

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

I can see is doing the select top on an ordered subquery.

Now comes the problem. I want to take 10 distinct items, so I change my query to this:

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

And now the generated SQL is like this:

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

And now the subquery doing the select topis not ordered by ClerkCode.

How can I fix this?

Upvotes: 0

Views: 56

Answers (1)

jjj
jjj

Reputation: 4997

The expected behavior is that it returns an unordered sequence of the unique items in source.

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

It's implementation dependent, but Distinct seems like it would clear any previously set ordering.

Upvotes: 1

Related Questions