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.

score 87 · Answer 1 · answered Sep 09 '08 at 08:51

87

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;

answered Sep 09 '08 at 08:51

aku

122,288
32
173
203

Quick and to the point - great answer. Even though building the inner query in a separate statement is definitely clear, might as well know how to do it inline. Thanks! – Jason Bunting Oct 14 '09 at 01:04
@aku Is this method preferable to @Samuel Jack's method? – Brian Webster Jul 11 '11 at 19:59
This is much more readable than method chaining syntax. Thanks. – Burak Karakuş Jul 14 '16 at 09:03
Thanks for query!! Minor observation: In vb the query statement to the right of t2s = should be wrapped in brackets. I am in vs2013 – glant Sep 16 '16 at 15:01

score 65 · Accepted Answer · answered Sep 09 '08 at 08:39

65

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;

answered Sep 09 '08 at 08:39

Samuel Jack

32,712
16
118
155

Thanks for the link - that was just what I needed. Thanks to everyone else for their answers as well. – Ian Oxley Sep 09 '08 at 09:24
2

You might have better performance with constructing a Dictionary with the first query, as the Contains() call in the second query can then be done in O(1) as opposed to O(n). – Daren Thomas Sep 09 '08 at 12:03
8

Daren, LINQ to SQL will be transformed to SQL query. Dictionary will be useful when iterating over objects collection. – aku Sep 10 '08 at 01:19
@Samuel_Jack How can I use this in a LinqDatasource? – Simua Aug 19 '14 at 08:41
4

The link is now dead and the answer is much less useful. It would've been better if this answer contained more information from the linked article, precisely to prevent this situation. – Marc Dingena Jun 04 '18 at 09:20
Tank you so much :) – Bilal Mar 28 '20 at 06:52

score 3 · Answer 3 · answered Sep 09 '08 at 07:26

3

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

        )
    select f;

answered Sep 09 '08 at 07:26

NakedBrunch

48,713
13
73
98

Daren Thomas · Answer 4 · 2008-09-09T07:30:43.460

2

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

edited Sep 09 '08 at 07:30

answered Sep 09 '08 at 07:25

Daren Thomas

67,947
40
154
200

score 1 · Answer 5 · edited Jul 11 '11 at 19:52

1

// 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

edited Jul 11 '11 at 19:52

Brian Webster

30,033
48
152
225

answered Jul 06 '09 at 16:58

score 0 · Answer 6 · edited Feb 19 '13 at 00:03

0

// 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

edited Feb 19 '13 at 00:03

LeftyX

35,328
21
132
193

answered Jul 06 '09 at 17:21

score 0 · Answer 7 · answered Sep 09 '08 at 07:34

0

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

answered Sep 09 '08 at 07:34

Graviton

81,782
146
424
602

score 0 · Answer 8 · edited Jul 11 '11 at 19:52

0

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

edited Jul 11 '11 at 19:52

Brian Webster

30,033
48
152
225

answered Sep 15 '08 at 18:02

Amy B

108,202
21
135
185

score 0 · Answer 9 · answered Jan 23 '12 at 07:32

0

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

answered Jan 23 '12 at 07:32

Mox Shah

2,967
2
26
42

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

9 Answers9

General way to implement IN in LINQ to SQL

General way to implement EXISTS in LINQ to SQL

Linked

Related