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

问题描述

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

Is it possible to sort IQueryable collection by it's sub collections?

例如，我有一个 Books 集合.我还有一个 Authors 集合，该集合具有一列 BookId ，因此 BookId 是 Authors 表中的外键.这种关系是一对多的(一本书很多作者).

For example I have a Books collection. I also have an Authors collection which has a column BookId, so the BookId is a foreign key in Authors table. The relation is one-to-many (one book many authors).

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

I need to sort the Books collection by the first author. The book is not required to have an author. Can this be done in Entity Framework and Linq?

推荐答案

您的具体问题的答案(在澄清了作者应按姓名排序，图书藏书按先前排列的作者名单)是这样的:

The answer to your concrete question (after clarification that The authors should be ordered by name, and the books collection by previously ordered list of authors) is like this:

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

这将生成单个SQL查询，如下所示:

which would generate a single SQL query like this:

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

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

Note that the books w/o author will be the first in the order.

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

Another way is to use Min function:

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

这篇关于在实体框架中按子列表的第一项对集合进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！