SQL for IQueryable LINQ

Question

Hello, I'm trying to convert the following SQL statement to its LINQ equivalent, and since I'm really new to .net (one-day encoding), I am stuck with this for hours.

SELECT * 
 FROM Books 
 WHERE BookID IN (SELECT BookID 
              FROM Borrows 
              WHERE UserID = 2) 

This is a model

public class LibUser
{       
    [Key]
    public int UserID { get; set; }
    [Required, StringLength(50), Display(Name = "First Name")]
    public string UserFirstName { get; set; }
    [Required, StringLength(50), Display(Name = "Last Name")]
    public string UserLastName { get; set; }
    [Required, StringLength(10000), Display(Name = "Residence"), DataType(DataType.MultilineText)]
    public string Adress { get; set; }
}

public class Book { 
    [Key]
    public int BookID { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public DateTime Published{ get; set; }
}

public class Borrowed {
    [Key]
    public int BorrowID { get; set; }
    public int UserID { get; set; }
    public int BookID { get; set; } 
}

I would really appreciate help.

EDIT

Context class

public class LibraryContext : DbContext
{

          public LibraryContext()
        : base("libromatic")
    {
    }

    public DbSet<LibUser> LibUsers { get; set; }
    public DbSet<Book> Books { get; set; }
    public DbSet<Borrowed> Borrows { get; set; }

}

Answer 1

You could do something like this:

var Lnq = new LinqDataContext();
var borrowId = Lnq.Borrowed.Where(a => a.UserID == 2).Select(a => a.BookID).ToList();

var bookQuery = Lnq.Books.Where(a => borrowId.Contains(a.BookID))
                         .Select(a => a.YourColumn);

Answer 2

Assuming your context is called db, you can run the following query

var borrowedBooksForUser = db.Books
     .Where(b => db.Borrowed.Any(x => x.UserID == 2 && x.BookID == b.BookID));

Answer 3

.

Argument: if the user borrows a huge number of books or there is an error in the data, then your subquery can return many identifiers, and SQL 'IN' statements in long lists can become very slow.

Connection Usage:

SQL query:

SELECT Books.* FROM Books 
  JOIN Borrows ON Borrows.BookID = Books.BookID
  WHERE Borrows.UserID = 2 

Linq operator:

var allBooksBorrowedByUser2 = db.Borrowed
                .Where(borrow => borrow.UserID == 2)
                .Join(db.Books,
                      borrow => borrow.BookID,
                      book => book.BookID,
                      (borrow, book) => book);

Answer 4

Navigation will make everything simpler.

public class Borrowed {
    [Key]
    public int BorrowID { get; set; }
    public int UserID { get; set; }
    public int BookID { get; set; } 

    // Navigation Properties
    public virtual LibUser User { get; set; }
    public virtual Book Book { get; set; }
}

Borrows.Where(borrow => borrow.UserId == 2)
       .Select(borrow => borrow.Book);

Answer 5

try it,

  var getResult=from b in db.Books
  join bo in db.Borrows on b.BookID=bo.BookID
  where bo.UserID=2