如何用LINQ查询借阅量最高的Top5图书并获取书名

你的代码目前只返回了图书ID，是因为最后Select(q=>q.Key)只取了分组的键（也就是BookId）。下面是几种修改方案，可以拿到图书的Title，同时还能获取借阅次数：

方案一：修改现有代码直接获取Title #

基于你原来的代码调整，分组后从分组内的图书实例中提取Title：

var mostBorrowedBooks = _context.Books
    .Join(_context.Loans, 
        book => book.Id,
        loan => loan.BookId,
        (book, loan) => book)
    .GroupBy(book => book.Id)
    .OrderByDescending(group => group.Count())
    .Take(5)
    .Select(group => new 
    {
        BookId = group.Key,
        Title = group.First().Title,
        BorrowCount = group.Count()
    })
    .ToList();

因为分组是按图书ID聚合的，所以分组内的所有book对象都是同一本，直接取First().Title就能拿到对应的书名。

方案二：更高效的统计+关联写法 #

先从借阅表统计每本书的借阅次数，再关联图书表拿Title，这种方式处理的数据量更小，性能更优：

var mostBorrowedBooks = _context.Loans
    .GroupBy(loan => loan.BookId)
    .OrderByDescending(group => group.Count())
    .Take(5)
    .Join(_context.Books, 
        loanGroup => loanGroup.Key,
        book => book.Id,
        (loanGroup, book) => new 
        {
            BookId = book.Id,
            Title = book.Title,
            BorrowCount = loanGroup.Count()
        })
    .ToList();

方案三：使用GroupJoin关联统计 #

直接将图书和对应的所有借阅记录关联，一次性统计借阅次数：

var mostBorrowedBooks = _context.Books
    .GroupJoin(_context.Loans,
        book => book.Id,
        loan => loan.BookId,
        (book, loans) => new 
        {
            Book = book,
            BorrowCount = loans.Count()
        })
    .OrderByDescending(x => x.BorrowCount)
    .Take(5)
    .Select(x => new 
    {
        BookId = x.Book.Id,
        Title = x.Book.Title,
        BorrowCount = x.BorrowCount
    })
    .ToList();

如果只需要Title列表，可以把Select简化成Select(group => group.First().Title)或者对应方案里的Select(x => x.Title)。

内容的提问来源于stack exchange，提问作者VenusKa