MongoDB Convert aggregate query in node.js to LINQ

Question

I have an aggregate query in node.js that I'm struggling to convert to LINQ. Here is the original

const books = await collection.aggregate()
    .group({
        _id: "$author",
        count: { $sum: 1 },
        return_date: { $last: "$return_date" },
        title: { $last: "$title" },
    })
    .sort({ count: -1, _id: 1 })
    .limit(20).toArray();

The best I've been able to do so far is

IMongoQueryable<Popular> query = (from book in booksCollection.AsQueryable()
                                  group book by book.author into g
                                  select new Popular
                                  {
                                      Author = g.Key,
                                      Count = g.Count()
                                  }).OrderByDescending(g => g.Count).ThenBy(g => g.Author).Take(20);

Where Popular is

 public class Popular
 {
     public string? Title { get; set; } = null;
     public string? Author { get; set; } = null;
     public string? ReturnDate { get; set; } = null;
     public int? Count { get; set; }
 }

I can't find a way of including ReturnDate and Title, so I'm having to make an extra query for every result to add these fields. Is there a more elegant way of converting this query?

Answer 1

You can use the GroupBy and projection feature of LINQ to get ReturnDate and Title within one single LINQ query without firing an extra query

IMongoQueryable<Popular> query = (from book in booksCollection.AsQueryable()
                                  group book by book.Author into g
                                  let lastBook = g.OrderByDescending(b => b.ReturnDate).FirstOrDefault()
                                  select new Popular
                                  {
                                      Author = g.Key,
                                      Count = g.Count(),
                                      ReturnDate = lastBook.ReturnDate,
                                      Title = lastBook.Title
                                  })
                                  .OrderByDescending(g => g.Count)
                                  .ThenBy(g => g.Author)
                                  .Take(20);