时间戳

首页 > 解决方案 > 复杂的 LINQ 查询 - 多表关系

asp.net - 复杂的 LINQ 查询 - 多表关系

问题描述

我有一个书籍数据库，其中包含作者的 ICollection。我想使用 LINQ 根据 AuthorId 返回作者对象。

Book db

int BookId
string Name { get; set; }
public ICollection<Author> Authors { get; set; }

Author db
int AuthorId
string Name
ICollection<Quote> Quotes { get; set; }
ICollection<Penname> Pennames { get; set; } - Edit: Added for clarity

我努力了：

var test = _context.Book.Include(x => x.Authors).Include("Authors.Quotes")
                    .Select(y => y.Authors)

这给了我：

EntityQueryable<ICollection<Authors>>
[0] {HashSet<Author>} [0]{Author} [1]{Author} [3]{Author} 
[1] {HashSet<Author>} [0]{Author} [1]{Author} 
[2] {HashSet<Author>} [0]{Author} [1]{Author}

我只是不知道如何迭代作者列表中的作者。如下所示：

var id = 2

var test = _context.Book.Include(x => x.Authors).Include("Authors.Quotes")
                    .Select(y => y.Authors.Select(x => x.Author).Where(x => x.AuthorId == id))

如果我进行重大更新，我可能会使用弹性...

更新@Marko Papic：

谢谢。奇怪的是，如果我使用下面的内容来获取作者的书籍列表，我会得到我所期望的填充的引号和笔名列表

var test = _context.Book.Include(x => x.Authors)
                   .ThenInclude(x => x.Quotes)
                   .Include(x => x.Authors)
                   .ThenInclude(x => x.Pennames)

但是，如果我使用 SelectMany，那么引号和笔名最终为空

var test = _context.Book.Include(x => x.Authors)
                   .ThenInclude(x => x.Quotes)
                   .Include(x => x.Authors)
                   .ThenInclude(x => x.Pennames)
                   .SelectMany(x => x.Authors).Where(x => x.AuthorId == id);

Author myauthor
int AuthorId = 2
string Name = "Bob"
ICollection<Quote> Quotes = null
ICollection<Penname> Pennames = null

标签： asp.netlinqentity-framework-core

解决方案

您可以使用SelectMany：

var test = _context.Book.Include(x => x.Authors).ThenInclude(x => x.Quotes)
                    .SelectMany(x => x.Authors).Where(x => x.AuthorId == id);

推荐阅读