CODEWITHSUNDEEP
sqllinqlinq-to-sql
Ian Oxley
Ian Oxley

Reputation: 11056

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

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

Answers (9)

Jimit Shah
Jimit Shah

Reputation:

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

Upvotes: 0

Mox Shah
Mox Shah

Reputation: 3015

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

Upvotes: 0

Amy B
Amy B

Reputation: 110221

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

Upvotes: 0

Jimit Shah
Jimit Shah

Reputation:

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

Upvotes: 1

aku
aku

Reputation: 124044

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;

Upvotes: 88

Samuel Jack
Samuel Jack

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

Graviton
Graviton

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

Daren Thomas
Daren Thomas

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

NakedBrunch
NakedBrunch

Reputation: 49423

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

        )
    select f;

Upvotes: 3

Related Questions