c# - Linq to SQL 按 2 个实体(父子)中的字段排序

Question

我有 2 个实体，分别称为 Request 和 Days。请求有很多天，我遇到的问题是按照我想要的方式正确排序我的实体。

Days 有一个名为 Hours 的特定字段，我需要先按 Hours 字段以天为单位对其进行排序(但是，首先是所有只有一天的字段)，然后是请求中的天数。

我已经尝试了很多 orderby/thenby 组合，但不能完全正确。

这是我最近试过的一个:

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

有什么帮助吗？

Answer 1

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

生成的 SQL 查询如下所示:

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