在实体框架中按子列表的第一项对集合进行排序 [英] Sort collection by first item of sub list in Entity Framework
问题描述
是否可以通过其子集合对 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
这篇关于在实体框架中按子列表的第一项对集合进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!