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

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

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

我试过很多按顺序排列的组合，但都做不好。

这是我最近尝试过的一个：

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

有什么帮助吗？

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

相关内容

最新更新

热门标签：