How can you handle an IN sub-query with LINQ to SQL?
Solution 1:
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 2:
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 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