gpt4 book ai didi

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

转载 作者:太空宇宙 更新时间:2023-11-03 18:23:48 25 4
gpt4 key购买 nike

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

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

我需要按第一作者对 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

关于c# - 按 Entity Framework 中子列表的第一项对集合进行排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41209374/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com