How do I write SELECT FROM myTable WHERE id IN (SELECT...) in Linq?

Question

How do you rewrite this in Linq?

SELECT Id, Name FROM TableA WHERE TableA.Id IN (SELECT xx from TableB INNER JOIN Table C....)

So in plain english, I want to select Id and Name from TableA where TableA's Id is in a result set from a second query.

Answer 1

from a in TableA where (from b in TableB join c in TableC on b.id equals c.id where .. select b.id).Contains(a.Id) select new { a.Id, a.Name }

Answer 2

There is no out of box support for IN in LINQ. You need to join 2 queries.

Answer 3

LINQ supports IN in the form of contains. Think "collection.Contains(id)" instead of "id IN (collection)".

from a in TableA
where (
    from b in TableB
    join c in TableC
        on b.id equals c.id
    select b.id
).Contains(TableA.Id)
select new { a.Id, a.Name }

See also this blog post.