c#之按 Entity Framework 中子列表的第一项对集合进行排序

是否可以按子集合对 IQueryable 集合进行排序？

例如，我有一个 Books 集合。我还有一个 Authors 集合，其中有一列 BookId，所以 BookId 是 Authors 表中的外键.关系是一对多的(一本书很多作者)。

我需要按第一作者对 Books 集合进行排序。这本书不需要有作者。这可以在 Entity Framework 和 Linq 中完成吗？

请您参考如下方法:

你的具体问题的答案(在澄清作者应该按名字排序，而书籍收藏按先前排序的作者列表之后)是这样的:

var query = db.Books 
    .OrderBy(b => b.Authors.OrderBy(a => a.Name).Select(a => a.Name).FirstOrDefault()); 

这将生成一个像这样的 SQL 查询:

SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[Title] AS [Title] 
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Title] AS [Title], 
        (SELECT TOP (1) [Project1].[Name] AS [Name] 
            FROM ( SELECT 
                [Extent2].[Name] AS [Name] 
                FROM [dbo].[Author] AS [Extent2] 
                WHERE [Extent1].[Id] = [Extent2].[BookId] 
            )  AS [Project1] 
            ORDER BY [Project1].[Name] ASC) AS [C1] 
        FROM [dbo].[Book] AS [Extent1] 
    )  AS [Project2] 
    ORDER BY [Project2].[C1] ASC 

请注意，没有作者的书籍将排在第一位。

另一种方法是使用 Min 函数:

var query = db.Books 
    .OrderBy(b => b.Authors.Min(a => a.Name)); 

使用 SQL 翻译:

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Title] AS [Title] 
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Title] AS [Title], 
        (SELECT 
            MIN([Extent2].[Name]) AS [A1] 
            FROM [dbo].[Author] AS [Extent2] 
            WHERE [Extent1].[Id] = [Extent2].[BookId]) AS [C1] 
        FROM [dbo].[Book] AS [Extent1] 
    )  AS [Project1] 
    ORDER BY [Project1].[C1] ASC