如何在 LINQ 中内部联接后将相同的结果合并到一行中

Question

我是 ASP.NET 和LINQ的新手。我想在索引页中显示书籍名称、出版商名称和作者姓名。一本书可以有多个作者。

我的显示查询结果是这样的

var query = BookAuthors.Join(Authors,
bookAuthor => bookAuthor.AuthorId,
author => author.Id,
(bookAuthor, author) => new { bookAuthor = bookAuthor, AuthorName = author.Name })
.Join(Books,
ba => ba.bookAuthor.BookId,
book => book.Id,
(ba, book) => new { book = book, AuthorName = ba.AuthorName })
.Join(Publishers, 
book => book.book.PublisherId,
publisher => publisher.Id,
(book, publisher) => new { BookId = book.book.Id , BookName = book.book.Name, AuthorName = book.AuthorName,  PublisherName = publisher.Name});

然后我创建视图模型来显示结果，视图模型代码如下所示

foreach (var obj in query)
{
viewModel.Add(new TestViewModel
{
BookId = obj.BookId,
BookName = obj.BookName,
AuthorName = obj.AuthorName,
PublisherName = obj.PublisherName
});
}
return View(viewModel);

使用 Join(( 方法它的工作方式类似于 SQL 中的内部连接，查询结果如下所示

| BookName | PublisherName | Author             |

| Book A   | Publisher B   | Author A           |
| Book B   | Publisher A   | Author A           |
| Book B   | Publisher A   | Author B           |
| Book C   | Publisher B   | Author C           |

但我希望查询的输出是这样的

| BookName | PublisherName | Author             |

| Book A   | Publisher B   | Author A           |
| Book B   | Publisher A   | Author A, Author B |
| Book C   | Publisher B   | Author C           |

当作者值在书籍 ID 上具有相同的值时，如何将作者值连接到同一行？是否有任何 LINQ 方法可以组合它？

Answer 1

您可以按书籍和出版商对查询进行分组，并使用string.Join来连接作者姓名

var result = query.GroupBy(x => new
{
x.BookName,
x.PublisherName
})
.Select(x => new
{
x.Key.BookName,
x.Key.PublisherName,
AuthorName = string.Join(", ", x.Select(y => y.AuthorName))
});