Tutorial :How can you handle an IN sub-query with LINQ to SQL?



Question:

I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL:

SELECT f.*   FROM Foo f  WHERE f.FooId IN (      SELECT fb.FooId      FROM FooBar fb      WHERE fb.BarId = 1000  )  

Any help would be gratefully received.

Thanks.


Solution:1

Have a look at this article. Basically, if you want to get the equivalent of IN, you need to construct an inner query first, and then use the Contains() method. Here's my attempt at translating:

var innerQuery = from fb in FoorBar where fb.BarId = 1000 select fb.FooId;  var result = from f in Foo where innerQuery.Contains(f.FooId) select f;


Solution:2

General way to implement IN in LINQ to SQL

var q = from t1 in table1          let t2s = from t2 in table2                    where <Conditions for table2>                    select t2.KeyField          where t2s.Contains(t1.KeyField)          select t1;  

General way to implement EXISTS in LINQ to SQL

var q = from t1 in table1          let t2s = from t2 in table2                    where <Conditions for table2>                    select t2.KeyField          where t2s.Any(t1.KeyField)          select t1;  


Solution:3

from f in Foo      where f.FooID ==          (              FROM fb in FooBar              WHERE fb.BarID == 1000              select fb.FooID            )      select f;  


Solution:4

Try using two separate steps:

// create a Dictionary / Set / Collection fids first  var fids = (from fb in FooBar              where fb.BarID = 1000              select new { fooID = fb.FooID, barID = fb.BarID })              .ToDictionary(x => x.fooID, x => x.barID);    from f in Foo  where fids.HasKey(f.FooId)  select f  


Solution:5

// create a Dictionary / Set / Collection fids first

Find Other Artilces

var fids = (from fb in FooBar              where fb.BarID = 1000              select new { fooID = fb.FooID, barID = fb.BarID })              .ToDictionary(x => x.fooID, x => x.barID);    from f in Foo  where fids.HasKey(f.FooId)  select f  


Solution:6

Try this

var fooids = from fb in foobar where fb.BarId=1000 select fb.fooID  var ff = from f in foo where f.FooID = fooids select f  


Solution:7

var foos = Foo.Where<br>  ( f => FooBar.Where(fb.BarId == 1000).Select(fb => fb.FooId).Contains(f.FooId));  


Solution:8

from f in foo  where f.FooID equals model.FooBar.SingleOrDefault(fBar => fBar.barID = 1000).FooID  select new  {  f.Columns  };  


Solution:9

// create a Dictionary / Set / Collection fids first

Find Other Artilces

var fids = (from fb in FooBar where fb.BarID = 1000 select new { fooID = fb.FooID, barID = fb.BarID }) .ToDictionary(x => x.fooID, x => x.barID);    from f in Foo where fids.HasKey(f.FooId) select f  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »