How to combine same result into one row after inner join in LINQ

I’m new in ASP.NET and LINQ. I want to display book name, publisher name, and author name in index page. One book can have more than one author.

my query for display the result is like this

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});

Then i create the viewModel for displaying the result, the viewModel code look like this

foreach (var obj in query)
        {

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

using Join() method it works like Inner Join in SQL, the result of query is like this

| 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           |

But I expect the output of query to be like this

| BookName | PublisherName | Author             |


| Book A   | Publisher B   | Author A           |

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

| Book C   | Publisher B   | Author C           |

How to join the author value to the same row when it have the same value on book id? Is there any LINQ method to combining it?

Answer

You can group your query by book and publisher and use string.Join to concat the authors name

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))
    });