CODEWITHSUNDEEP
c#sqlasp.net-mvclinq
Pavel Kašelják
Pavel Kašelják

Reputation: 351

SQL to IQueryable LINQ

Hello I am trying to convert the following SQL statement into its LINQ equivalent and since I am really new to .net (coding for one day) i have gotten stuck on this for hours now.

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

This is the 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 greatly appreciate anyones 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; }

}

Upvotes: 5

Views: 1732

Answers (5)

Prajapati Vikas
Prajapati Vikas

Reputation: 300

try this,

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

Upvotes: 2

bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

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

Upvotes: 3

Barbara
Barbara

Reputation: 1009

It might be preferable to do this with a join.

The argument is: If a user borrows huge amounts of books, or there is an error in the data, then your subquery could return a lot of IDs, and SQL 'IN' clauses on long lists can get really slow.

Using a join:

SQL query:

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

Linq statement:

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

Upvotes: 4

Aydin
Aydin

Reputation: 15294

Navigation would make everything more simple.

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

Upvotes: 3

Alex
Alex

Reputation: 13224

Assuming your context is called db, you could do the following query

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

Upvotes: 4

Related Questions