Check a value from a second table within a LINQ Statement

Question

I'm loading an IQueryable of books from the Books table and there's another table named BookLoans. I have a DateTime? property in my ViewModel named Availability. I'm trying to figure out the best way to basically go out during my select statement and see if the current book's BookID shows up in a record within the BookLoans table and if a DateTime? variable named ReturnedWhen is also NULL in that record. I'm basically trying to mark the book as available or checked outed when I populate the the IQueryable.

It's been suggested that I try to do this all as one LINQ statement but I'm trying to figure out if it would be easier to just populate books like the following and then run it through a foreach loop? It's also been suggested that a Join would work but really I'm just trying to figure out the best method and then how to go about actually doing it.

 var books =
 _context.Books
 .Select(r => new BookIndexViewModel
 {
     BookID = r.BookID,
     Title = r.Title,
     Author = r.Author,
     ISBN = r.ISBN,
     GenreName = r.Genre.Name
 }).ToList();

Book Entity:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Open_School_Library.Data.Entities
{
    public class Book
    {
        public int BookID { get; set; }
        public string Title { get; set; }
        public string SubTitle { get; set; }
        public string Author { get; set; }
        public int GenreID { get; set; }
        public int DeweyID { get; set; }
        public int ISBN { get; set; }

        public Genre Genre { get; set; }
        public Dewey Dewey { get; set; }
        public virtual BookLoan BookLoan { get; set; }
    }
}

BookLoan Entity:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Open_School_Library.Data.Entities
{
    public class BookLoan
    {
        public int BookLoanID { get; set; }
        public int BookID { get; set; }
        public int StudentID { get; set; }
        public DateTime CheckedOutWhen { get; set; }
        public DateTime DueWhen { get; set; }
        public DateTime? ReturnedWhen { get; set; }

        public Book Book { get; set; }
        public Student Student { get; set; }
    }
}

BookIndexViewModel:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace Open_School_Library.Models.BookViewModels
{
    public class BookIndexViewModel
    {
        public int BookID { get; set; }
        public string Title { get; set; }
        [Display(Name = "Author")]
        public string Author { get; set; }
        [Display(Name = "Genre")]
        public string GenreName { get; set; }
        public int ISBN { get; set; }
        public string BookLoan { get; set; }
        public DateTime? Availability { get; set; }
    }
}

Answer 1

Here are some simplified versions of your classes

public class Book {
    public int BookId { get; set; }
    public string Title { get; set; }
}

public class BookLoan {
    public int BookId { get; set; }
    public DateTime CheckedOutOn { get; set; }
    public DateTime? ReturnedOn { get; set; }
    public DateTime DueOn { get; set; }
}

public class BookViewModel {
    public int BookId { get; set; }
    public string Title { get; set; }
    public bool IsAvailable { get; set; }
    public DateTime? AvailableOn { get; set; }
} 

Then we can do two things to get the view model list you're looking for:

1. Look at only book loans where ReturnedOn is null
2. Use DefaultIfEmpty to do a left outer join, see https://msdn.microsoft.com/en-us/library/bb397895.aspx

So you end up with one item per book and only a loanWithDefault if the book is currently checked out.

var viewModel = from book in books
            join loan in loans.Where(x => !x.ReturnedOn.HasValue) on book.BookId equals loan.BookId into result
            from loanWithDefault in result.DefaultIfEmpty()
            select new BookViewModel { 
                BookId = book.BookId,
                Title = book.Title,
                IsAvailable = loanWithDefault == null,
                AvailableOn = loanWithDefault == null ? (DateTime?) null : loanWithDefault.DueOn
            };

Here is a working example: https://dotnetfiddle.net/NZc2Xd

Here is a breakdown of the LINQ query:

1. from book in books is the first table you are selecting from
2. loans.Where(x => !x.ReturnedOn.HasValue) limits the loans to a maximum of one item per book (and the loans we care about in this case)
3. join loan in loans on book.BookId equals loan.BookId into result is joining on the loans table so that we get that as part of our result set. The join condition is the book's BookId equals the loan's BookId
4. from loanWithDefault in result.DefaultIfEmpty() basically makes the join on loans a "left outer join". This means that even if there are no loans that have yet to be returned for this book you will get the book in your result set. Otherwise, you would only get unavailable books. See this Stack Overflow answer for a good explanation of different joins. Also, see https://msdn.microsoft.com/en-us/library/bb397895.aspx for how to do left outer joins with LINQ.
5. Finally, you select your view model from the available Book and BookLoan object.

Here is what a similar raw SQL query would look like

select
  Book.BookId,
  Book.BookTitle,
  BookLoan.DueOn
from
  Book
  left outer join BookLoan on
    Book.BookId = BookLoan.BookId
    and BookLoan.ReturnedOn is null