Get objects while getting couple of fields from latest child

Question

I have two objects in my model (simplified version below)

class Author
{
    int ID;
    string Name;
    List<Book> Books;
}

class Book
{
    int ID;
    string Title;
    DateTime PublishDate;
    Author Author;
}

I need to get all Authors and for each author, I need the ID and PublishDate of the most recent book.

I tried below but not giving me the expected result:

var authors = db.Authors
    .SelectMany(a => a.Books.OrderByDescending(b => b.PublishDate).DefaultIfEmpty(), (A, B) => new { Author = A, LastBook = B})

I prefer to have Lambda expression (for learning purpose).

Thanks for help.

Answer 1

Select many is not the way to go here. Select many is useful when you have to merge multiple collections into a single collection. Instead you want to select just the most recent book for each author, and then navigate to the author id from that recent book.

Something like the code below should work. I do not have the means to test at the moment, some modifications may be required.

var data = db.Authors
    .Select(author => author.Books.OrderByDescending(book => book.PublishDate).FirstOrDefault())
    .Where(recentBook => recentBook != null)
    .Select(recentBook => new { Author = recentBook.Author, RecentBook = recentBook });

The above query will exclude authors that have no books. You may not need the null check depending on your data. If an author can be in the database but not have any books, you will need the null check. If an author cannot be in the database without first having a book, you can omit the where clause with the null check.

To get all authors regardless of a recent book, you can use this query instead.

var data = db.Authors
    .Select(author => new { Author = author, RecentBook = author.Books.OrderByDescending(book => book.PublishDate).FirstOrDefault() });