Order is missing from entity infrastructure view

I have the following C # code creating a query to 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 seems that the ordering statements were completely ignored in the generated SQL.

Can someone can figure out what is wrong with my expression to ignore the order.

The solutions offered in Order not working in Entity Framework query did not fit this situation (at least to my satisfaction)

UPDATE:

The first set of orderby is on the main object where I want to select the first one in order, and the subsequent selectmany is in the detail entries or which I want to get first.

Think about it, selectmany is not required in this instance as I only need packages from the first result.

+3


source to share


1 answer


Based on your update, I think you are targeting 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 will allow you to select and arrange objects Bag

according to your original request, take the top one, then select and order a collection bag.Packs

on the selected one Bag

.


The original answer is below.

@LoztInSpace has the right idea here.



Ultimately, when you use SelectMany()

and flatten the results like you do, you need OrderBy(),

ThenBy () ect after the

SelectOrMany () `call.

Consider the output of this request:

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

      

Order where we want. 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 that there is no order, because that ultimately doesn't matter.

+1


source







All Articles