如何将EF家长按儿童子女的数量排序？

Question

我有Teams of Coders of Projects of Tasks。

如何使用方法语法和代码优先方法按照实体框架中的任务的数量对团队进行有效排序(可能没有加载子程序的整个集合内容，我只需要排序)？

public class MyAppContext : DbContext
{
    public DbSet<CTeam> Teams { get; set; }
    // ...etc
}

[Table("Team")]
public class CTeam
{

    public int ID { get; set; }

    [MaxLength(128)]
    public string Title { get; set; }

    [Display(Name = "Coders")]
    public virtual ICollection<CCoder> Coders { get; set; }
}



[Table("Coder")]
public class CCoder
{

    public int ID { get; set; }

    [MaxLength(128)]
    public string Name { get; set; }

    public virtual ICollection<CProject> Projects { get; set; }

    public int TeamID { get; set; }
    public virtual CTeam Team { get; set; }
}

//  CProject and CTask similarly

或者，如果您能够提示一下原始SQL在这方面应该是什么样子，这也会有所帮助。谢谢!

更新

在具有关系Builder -> NewBuildingObject -> NewBuildingHouse -> NewBuildingLayout的项目中

关于观众的方便性，特奥多·伊万诺夫的回答：

context.Teams
.OrderBy(x => x.Coders
   .SelectMany(y => y.Projects)
   .SelectMany(y => y.Tasks)
   .Count());

使EF生成此代码：

SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[Title] AS [Title]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Title] AS [Title], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM   [dbo].[NewBuildingObject] AS [Extent2]
            INNER JOIN [dbo].[NewBuildingHouse] AS [Extent3] ON [Extent2].[ID] = [Extent3].[ObjectID]
            INNER JOIN [dbo].[NewBuildingLayout] AS [Extent4] ON [Extent3].[ID] = [Extent4].[HouseID]
            WHERE [Extent1].[ID] = [Extent2].[BuilderID]) AS [C1]
        FROM [dbo].[Builder] AS [Extent1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC

亚库布·马萨德的回答是：

var teams = context.Teams
    .OrderBy(team =>
        team.Coders.SelectMany(coder =>
            coder.Projects.SelectMany(project => project.Tasks)).Count());

制定了这个代码：

SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[Title] AS [Title]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Title] AS [Title], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM  [dbo].[NewBuildingObject] AS [Extent2]
            INNER JOIN  (SELECT [Extent3].[ObjectID] AS [ObjectID]
                FROM  [dbo].[NewBuildingHouse] AS [Extent3]
                INNER JOIN [dbo].[NewBuildingLayout] AS [Extent4] ON [Extent3].[ID] = [Extent4].[HouseID] ) AS [Join1] ON [Extent2].[ID] = [Join1].[ObjectID]
            WHERE [Extent1].[ID] = [Extent2].[BuilderID]) AS [C1]
        FROM [dbo].[Builder] AS [Extent1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC

Answer 1

你可以尝试这样的方法

context.Teams
.OrderBy(x => x.Coders
   .SelectMany(y => y.Projects)
   .SelectMany(y => y.Tasks)
   .Count());

Answer 2

您可以这样使用SelectMany：

var teams = context.Teams
    .OrderBy(team =>
        team.Coders.SelectMany(coder =>
            coder.Projects.SelectMany(project => project.Tasks)).Count());