SQL to IQueryable LINQ

Question

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

}

Also whether or not you were using it or not avoid using `Select *` — bumble_bee_tuna, May 11 '15 at 02:55
@bumble_bee_tuna Yeah I realise that I used that query just for the sake of simplicity when figuring out LINQ — Pavel Kašelják, May 11 '15 at 03:01

score 4 · Answer 1 · answered May 11 '15 at 03:38

4

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

answered May 11 '15 at 03:38

Alex

13,024
33
62

This throws `System.InvalidOperationException` saying that `The model backing the 'LibraryContext' context has changed since the database was created.` I honestly havent got a clue why its doing it. – Pavel Kašelják May 11 '15 at 03:46
@PavelKašelják It appears that you are using entity framework, and not LINQ to SQL as your question suggested Also it seems you have a schema change in code, that does not yet match the existing database content. Refer to: http://stackoverflow.com/questions/21852121/the-model-backing-the-context-context-has-changed-since-the-database-was-cre and http://forums.asp.net/t/1998436.aspx?model+backing+the+context+has+changed+since+the+database+was+created+Consider+using+Code+First+Migrations+to+update+the+database for more information. – Alex May 11 '15 at 04:04
This answer is definitely the simplest way to get SQL that will very closely resemble what was given as the goal in the question. – Arkaine55 May 11 '15 at 04:38
@Arkaine55, true, although other possible queries exist when using a join condition on the book id that will yield the same result – Alex May 11 '15 at 04:40

score 4 · Answer 2 · answered May 11 '15 at 04:33

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

This may indeed be a better performing query in the average case. — Alex, May 11 '15 at 04:48

score 3 · Answer 3 · edited May 11 '15 at 04:58

3

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

edited May 11 '15 at 04:58

Arkaine55

548
6
15

answered May 11 '15 at 02:47

bumble_bee_tuna

3,533
7
43
83

It complains that the `BookID` doesnt exist in the current context right before the `Contains()` – Pavel Kašelják May 11 '15 at 02:55
@PavelKašelják See The Edit I made, Add the `.Select(a => a.BookId))` – bumble_bee_tuna May 11 '15 at 02:55
I did that but it still complains about the `BookID` in this line `Lnq.Books.Where(a => BookID.Contains` I added the context class to my question – Pavel Kašelják May 11 '15 at 02:59
1

Just to note the most recent edits have actually made this 2 separately executed queries with the second essentially passing in the results of ("SELECT BookID FROM Borrows WHERE UserID = 2") as a complex where clause. http://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama – Arkaine55 May 11 '15 at 04:25
@Arkaine55 Yeah I was trying to make it more readable for him – bumble_bee_tuna May 11 '15 at 04:41

Aydin · Answer 4 · 2015-05-11T03:59:59.140

3

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

edited May 11 '15 at 03:59

answered May 11 '15 at 03:05

Aydin

15,016
4
32
42

Doesnt this solution just selects inside the `Borrows` table ? I need to get the books based on what the user has borrowed so I can display the books detail information. I need to join the two tables. And then return the `IQueryable` not `IQueryable` – Pavel Kašelják May 11 '15 at 03:23
Sorry, small mistake I made there, it's not `SelectMany` but instead `Select`. And no, it projects the books... as you'll notice first we search for the books borrowed by the given user Id... then once the search has narrowed down to all the books, it would return `IQueryable` except out `Select` statement then `projects` the actual `Book` – Aydin May 11 '15 at 04:00

score 2 · Answer 5 · answered May 11 '15 at 08:59

2

try this,

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

answered May 11 '15 at 08:59

Prajapati Vikas

294
1
10

can explain what is this and why? – endrik exe Nov 28 '17 at 02:26

SQL to IQueryable LINQ

5 Answers5