在实体框架中按子列表的第一项对集合进行排序

小编典典

在实体框架中按子列表的第一项对集合进行排序

sql

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

例如，我有一个Books收藏。我也有一个Authors具有column的集合BookId，所以BookId是Authors表中的外键。这种关系是一对多的（一本书很多作者）。

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

阅读 168

2021-04-07

共1个答案

小编典典

您的具体问题的答案（在澄清了 作者应按姓名排序，图书集合按先前排序的作者名单排序后 ）是这样的：

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

2021-04-07