CODEWITHSUNDEEP
c#databaseentity-framework-4linq-to-entities
online_user
online_user

Reputation: 45

Efficient way to sub Queries in Linq

here is my linq code:

BOOK entity = db.BOOKS
             .Where(s => s.ID == (from p in db.LIBRARY
                                  from b in db.BOOKS
                                  where (p.ID == 123) && (p.idpage == b.idpage)
                                  select b.fields));

My actual oracle code is:

SELECT DISTINCT BOOKS.ID 
FROM LIBRARY,BOOKS 
WHERE LIBRARY.ID = 123 AND LIBRARY.ID = BOOKS.ID

But its showing the error in s.ID that..

Delegate 'System.Func Project.Models.BOOKS,int,bool' does not take 1 arguments

Why does this happen? Are there any workarounds?

Upvotes: 3

Views: 745

Answers (4)

Ahmad Mageed
Ahmad Mageed

Reputation: 96547

Your SQL is using a join, so you can do the same thing in LINQ. Either of these approaches will suffice:

// join
var query = (from b in db.BOOKS
            join p in db.LIBRARY on b.IdPage equals p.IdPage 
            where p.ID == 123
            select b.Id).Distinct();

// 2 from statements (SelectMany) can also be used as a join
var query = (from b in db.BOOKS
            from p in db.LIBRARY
            where p.ID == 123 && b.IdPage == p.IdPage 
            select b.Id).Distinct();

// fluent syntax
var query = db.BOOKS
              .Where(b => db.LIBRARY.Any(p =>
                  p.ID == 123 && b.IdPage == p.IdPage))
              .Select(b => b.Id)
              .Distinct();

Upvotes: 2

Thomas Levesque
Thomas Levesque

Reputation: 292645

Your subquery returns a sequence of values, not a single values, so you can't compare it to a scalar property like ID. You should use First on the result of the subquery to get the first result (or Single if there should be only one)

BOOK entity = db.BOOKS
             .Where(s => s.ID == (from p in db.LIBRARY
                                  from b in db.BOOKS
                                  where (p.ID == 123) && (p.idpage == b.idpage)
                                  select b.fields).First());

Upvotes: 1

Nelson Reis
Nelson Reis

Reputation: 4810

s.ID is comparing to an Enumerable, so you get the error.
At the end of the LINQ query, add a SingleOrDefault().

Upvotes: 1

StriplingWarrior
StriplingWarrior

Reputation: 156654

You should be able to use the navigation properties on your BOOKS class to do something like this:

var bookIds = db.BOOKS.Where(b => b.LIBRARIES.Any(l => l.ID == 123))
     .Select(b => b.ID)

Upvotes: 0

Related Questions