Reputation: 11056
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.
Upvotes: 56
Views: 78624
Reputation:
// 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
Upvotes: 0
Reputation: 3015
from f in foo
where f.FooID equals model.FooBar.SingleOrDefault(fBar => fBar.barID = 1000).FooID
select new
{
f.Columns
};
Upvotes: 0
Reputation: 110221
var foos = Foo.Where<br>
( f => FooBar.Where(fb.BarId == 1000).Select(fb => fb.FooId).Contains(f.FooId));
Upvotes: 0
Reputation:
// 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
Upvotes: 1
Reputation: 124044
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;
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;
Upvotes: 88
Reputation: 33300
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;
Upvotes: 65
Reputation: 83316
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
Upvotes: 0
Reputation: 70354
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
Upvotes: 2
Reputation: 49423
from f in Foo
where f.FooID ==
(
FROM fb in FooBar
WHERE fb.BarID == 1000
select fb.FooID
)
select f;
Upvotes: 3