LINQ Query Combines Only Last Value

Question

I am creating an ASP.NET C # library system. Take a screenshot (MSSQL) of DB-design:

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

• I left the “Rent” table in the Books table (because books that have not yet been rented once should also be shown) (see book with identifier 3)
• When I just left to join, I see that my books turn off several times in my books - a review ...
• SO : I need a LINQ query that connects books to rents, but if books are already rented several times, it should join the last value in the table of this book_id
• : ...
• ( : - , value = 0, , 1)

:

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

, , ... - "MAX"? ( linq?)

Answer 1

, . , "".

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