0

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?

8
  • Your resulting SQL is not complete - your subquery is not returning a column and it should be ORDER BY instead of ORDERBY. reduce your actual code down to a reasonable size (e.g. reduce the number of columns returned) so we can see the ACTUAL sql. Commented May 13, 2015 at 15:52
  • The actual SQL is huge, that is why I warned this is pseudo code. Commented May 13, 2015 at 15:53
  • But your pseudo-code SQL is not valid, so we have no way to know what the actual result is or how to change the code appropriately. Commented May 13, 2015 at 15:54
  • Of course it is not valid, is just expressing how the orderby is put out of the subquery, that is all. It is not need it to paste 1000 lines of sql. Commented May 13, 2015 at 16:07
  • I tried something like this in LinqPad, and it seems generate the SQL that you want. Commented May 14, 2015 at 2:47

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks this explains it. I think the same thing happens with GroupBy. Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.