CODEWITHSUNDEEP
c#sqllinqentity-framework
ab1428x
ab1428x

Reputation: 804

Linq to SQL sort by fields in 2 entities (parent-child)

I have 2 entities called Request and Days. Request has many days and what I am having problems with is properly sorting my entities the way I want to.

Days has a certain field called Hours and I need to sort it first by the Hours field in days (however, first are ALL of the fields that have only one day) and then, by then number of Days in a Request.

I've tried many orderby/thenby combinations and can't get this quite right.

Here is a recent one I've tried:

sortingFunction = x => x.Days.OrderBy(h => h.Hours).Count();

Any help with this?

Upvotes: 2

Views: 142

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

from r in db.Requests
let daysCount = r.Days.Count()
orderby daysCount == 1 ? r.Days.FirstOrDefault().Hours : Int32.MaxValue, 
        daysCount
select r

Generated SQL query will look like:

SELECT
[Project4].[Id] AS [Id],
[Project4].[Foo] AS [Foo]
FROM ( SELECT
    CASE WHEN ((1 = [Project3].[C1]) AND ([Project3].[C1] IS NOT NULL)) 
         THEN [Project3].[C2] ELSE 2147483647 END AS [C1],
    [Project3].[Id] AS [Id],
    [Project3].[Foo] AS [Foo]
    [Project3].[C1] AS [C2]
    FROM ( SELECT
        [Project1].[Id] AS [Id],
        [Project1].[Foo] AS [Foo]
        [Project1].[C1] AS [C1], // count of days
        (SELECT TOP (1) // C2 is hours of first day
            [Extent3].[Hours] AS [Hours]
            FROM [dbo].[Days] AS [Extent3]
            WHERE [Project1].[Id] = [Extent3].[RequestId]) AS [C2]
        FROM (SELECT
            [Extent1].[Id] AS [Id],
            [Extent1].[Foo] AS [Foo]
            (SELECT
                COUNT(1) AS [A1]
                FROM [dbo].[Days] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[RequestId]) AS [C1]
            FROM [dbo].[Requests] AS [Extent1]
        )  AS [Project1]
    )  AS [Project3]
)  AS [Project4]
ORDER BY [Project4].[C1] ASC, [Project4].[C2] ASC

Upvotes: 1

Related Questions