LINQ query join only last value

Question

I am making a ASP.NET C# library system. Het a screenshot of the (MSSQL) DB-design:

To display the books, I use the Rentals table. But there is my problem:

• I left join the Books table with the Rentals table (because also books who are not yet rented once, need to be displayed (see book with id 3)
• When I just do left join, I see my books offcourse multiple times in my books-overview...
• SO: I should need a LINQ query that joins Books with Rentals, but if the books are already been rented multiples times, he should only join with the last value in the table of that book_id
• Look at the image below: The query should only select the green-selected values...
• (extra info: if someone rent a book, the returned value = 0, if he returns the book, it becomes 1)

The query I have now is:

var query = (from b in db.Books
                     join a in db.Authors on b.author_id equals a.author_id
                     join c in db.Categories on b.category_id equals c.category_id
                     join r in db.Rentals on b.book_id equals r.book_id into lf
                     from r in lf.DefaultIfEmpty()
                     select new BookDetails(
                                b.book_id,
                                b.title,
                                b.ISBN,
                                b.description,
                                b.author_id,
                                a.firstName,
                                a.lastName,
                                b.category_id,
                                r.returned == null ? 1 : r.returned)
                     ).ToList();

But like I said, this displays me rented books multiple times... I've been thinking about something with the "MAX" property? (but does this works with linq?)

Answer 1

You can group the books by book ID and then select first item from each group. That's a neat way how to simulate "distinct". In your case that would be

var query = (from b in db.Books
             join a in db.Authors on b.author_id equals a.author_id
             join c in db.Categories on b.category_id equals c.category_id
             join r in db.Rentals on b.book_id equals r.book_id into lf
             from r in lf.DefaultIfEmpty()
             group new{ Book = b, Author = a, Rental = r }
                 by b.book_id into booksById
             let item = booksById.First()
             select new BookDetails(
                 item.Book.book_id,
                 item.Book.title,
                 item.Book.ISBN,
                 item.Book.description,
                 item.Book.author_id,
                 item.Author.firstName,
                 item.Author.lastName,
                 item.Book.category_id,
                 item.Rental.returned == null
                     ? 1 : item.Rental.returned))
             .ToList();