Ordering missing in entity framework query

Question

I have the following C# code generating a query on a sqlserver.

return c.Bags
        .Where(b => b.RollformerId == RollformerId
           && (!b.Order.OnHold.HasValue || b.Order.OnHold.Value == false)
           && (!b.Order.Archive.HasValue || b.Order.Archive.Value == false)
           && (!b.Order.Inactive.HasValue || b.Order.Inactive.Value == false)
           && (b.BagStatus.BagStatusId == notStarted
                          || b.BagStatus.BagStatusId == inProgress))
        .OrderBy(b => b.Priority)
        .ThenBy(b => b.ScheduleDate)
        .SelectMany(b => b.Packs
                     .OrderBy(p => p.PackSequence))
        .FirstOrDefault();

It generates the following sql code:

SELECT 
[Limit1].[BatchID] AS [BatchID], 
[Limit1].[RollformerID] AS [RollformerID], 
[Limit1].[Description] AS [Description], 
[Limit1].[OrderID] AS [OrderID], 
[Limit1].[BagId] AS [BagId], 
[Limit1].[PackSequence] AS [PackSequence], 
[Limit1].[PackStatusId] AS [PackStatusId], 
[Limit1].[Priority] AS [Priority], 
[Limit1].[ProductID] AS [ProductID], 
[Limit1].[DeliveryID] AS [DeliveryID]
FROM ( SELECT TOP (1) 
    [Extent4].[BatchID] AS [BatchID], 
    [Extent4].[DeliveryID] AS [DeliveryID], 
    [Extent4].[Priority] AS [Priority], 
    [Extent4].[ProductID] AS [ProductID], 
    [Extent4].[RollformerID] AS [RollformerID], 
    [Extent4].[Description] AS [Description], 
    [Extent4].[OrderID] AS [OrderID], 
    [Extent4].[BagId] AS [BagId], 
    [Extent4].[PackSequence] AS [PackSequence], 
    [Extent4].[PackStatusId] AS [PackStatusId]
    FROM    [dbo].[Bag] AS [Extent1]
    INNER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[OrderID]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Extent1].[OrderId] = [Extent3].[OrderID]
    INNER JOIN [dbo].[Batches] AS [Extent4] ON [Extent1].[BagId] = [Extent4].[BagId]
    WHERE ([Extent2].[OnHold] IS NULL OR [Extent3].[OnHold] = 0) 
    AND ([Extent3].[Archive] = 0 OR [Extent3].[Archive] IS NULL) 
    AND ([Extent3].[Inactive] = 0 OR [Extent3].[Inactive] IS NULL) 
    AND ([Extent1].[RollformerId] = @p__linq__0) 
    AND ([Extent1].[BagStatusId] IN (@p__linq__1,@p__linq__2))
)  AS [Limit1]

Comparing the output to the original, it appears that the ordering statements have been completely ignored in the generated SQL.

Could someone please identify what is wrong with my statement to have the ordering ignored.

The solutions provided in Ordering not working in Entity Framework query did not suit this situation (at least to my satisfaction)

UPDATE:

The first set of orderby is on the main entity, where I wish to select the first one within the order, and the subsequent selectmany is on the detail records, or which I wish to get the first.

On thinking about it, the selectmany is not required in this instance as I only want the packs from the first of the result.

I am reluctant to post this as an answer because I don't know if it's the case but it might help you think about the problem. I think it's to do with the fact that none of your order by parts really affect the outcome. It's like having ORDER BY in a view - pointless because you want to ORDER BY the final outcome. So I think you need to have your .OrderBy after your SelectMany and FirstOrDefault on that rather than buried inside. — LoztInSpace
– LoztInSpace, Commented May 10, 2015 at 6:58
@LoztInSpace Conceptually I can see that making sense somewhat - but an OrderBy() after FirstOrDefault() doesn't make too much sense - this is an odd one for sure. — jdphenix
– jdphenix, Commented May 10, 2015 at 7:04
Sorry - not what I am saying. Do .Where.SelectMany.OrderBy.FirstOrDefault. — LoztInSpace
– LoztInSpace, Commented May 10, 2015 at 7:06
You're spot-on - SelectMany doesn't return an IOrderedEnumerable like OrderBy does, so it would need to be after the SelectMany call. — jdphenix
– jdphenix, Commented May 10, 2015 at 7:13
I am most likely missing a FirstOrDefault before the selectmany which is to give me the result I would like, which is probably what is misleading everybody. — sweetfa
– sweetfa, Commented May 10, 2015 at 7:52

jdphenix · Accepted Answer · 2015-05-10 08:02:41Z

Based on your update, I think you're aiming for something like this:

return c.Bags
        .Where(b => b.RollformerId == RollformerId
           && (!b.Order.OnHold.HasValue || b.Order.OnHold.Value == false)
           && (!b.Order.Archive.HasValue || b.Order.Archive.Value == false)
           && (!b.Order.Inactive.HasValue || b.Order.Inactive.Value == false)
           && (b.BagStatus.BagStatusId == notStarted
                          || b.BagStatus.BagStatusId == inProgress))
        .OrderBy(b => b.Priority)
        .ThenBy(b => b.ScheduleDate)
        .FirstOrDefault().Packs
        .Select(b => b.Packs)
        .OrderBy(p => p.PackSequence);

This would select and order Bag entities as per your original query, take the top one, then select and order the bag.Packs collection on the selected Bag.

Original Answer below.

@LoztInSpace has the right idea here.

Ultimately, when you use SelectMany() and flatten the results like you do, you'll want to OrderBy(),ThenBy()ect after theSelectOrMany()` call.

Consider the output of this query:

var qry = ctx.Users
    .Where(u => u.EmailAddress != String.Empty)
    .SelectMany(u => u.Surveys)
    .OrderBy(s => s.Time)
    .ThenBy(s => s.Respondent_Uid);

SELECT [Project1].[Id] AS [Id]
    ,[Project1].[Time] AS [Time]
    ,[Project1].[Type] AS [Type]
    ,[Project1].[Respondent_Uid] AS [Respondent_Uid]
    ,[Project1].[SubjectOfSurvey_Uid] AS [SubjectOfSurvey_Uid]
FROM (
    SELECT [Extent2].[Id] AS [Id]
        ,[Extent2].[Time] AS [Time]
        ,[Extent2].[Type] AS [Type]
        ,[Extent2].[Respondent_Uid] AS [Respondent_Uid]
        ,[Extent2].[SubjectOfSurvey_Uid] AS [SubjectOfSurvey_Uid]
    FROM [dbo].[Users] AS [Extent1]
    INNER JOIN [dbo].[Surveys] AS [Extent2] ON [Extent1].[Uid] = [Extent2].[Respondent_Uid]
    WHERE NOT (
            ([Extent1].[EmailAddress] = @p__linq__0)
            AND (
                0 = (
                    CASE 
                        WHEN (@p__linq__0 IS NULL)
                            THEN cast(1 AS BIT)
                        ELSE cast(0 AS BIT)
                        END
                    )
                )
            )
    ) AS [Project1]
ORDER BY [Project1].[Time] ASC
    ,[Project1].[Respondent_Uid] ASC

The order by is where we want there. Contrast to this query:

var qry = ctx.Users
    .Where(u => u.EmailAddress != String.Empty)
    .OrderBy(u => u.FirstName)
    .SelectMany(u => u.Surveys.OrderBy(s => s.Time));

SELECT [Extent2].[Id] AS [Id]
    ,[Extent2].[Time] AS [Time]
    ,[Extent2].[Type] AS [Type]
    ,[Extent2].[Respondent_Uid] AS [Respondent_Uid]
    ,[Extent2].[SubjectOfSurvey_Uid] AS [SubjectOfSurvey_Uid]
FROM [dbo].[Users] AS [Extent1]
INNER JOIN [dbo].[Surveys] AS [Extent2] ON [Extent1].[Uid] = [Extent2].[Respondent_Uid]
WHERE NOT (
        ([Extent1].[EmailAddress] = @p__linq__0)
        AND (
            0 = (
                CASE 
                    WHEN (@p__linq__0 IS NULL)
                        THEN cast(1 AS BIT)
                    ELSE cast(0 AS BIT)
                    END
                )
            )
        )

Note no order at all, because it ultimately doesn't matter.

Nice example. Shame I was too lazy to write one up :) It's a fairly common problem with SQL - a view or sub query has an ORDER BY but not the actual SELECT
Having the orderby after the selectmany changes the result of the query. the selectmany is on detail records whereas the initial ordering is on the master records.
@sweetfa I updated based upon what I believe to be your intent.

Collectives™ on Stack Overflow

Ordering missing in entity framework query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related