Linq to entities: how to do a subquery

Question

Hi there,

How can i do this query with Linq To Entities

SELECT * FROM TableA 
WHERE MyID IN 
(
    SELECT MyID 
    FROM TableB
)

The thing is that TableB does not exist as an entity because it contains only foreign keys to other tables, and there is no direct link between TableA and TableB

Answer 1

Without TableB being an entity, I am not sure what you could do. If you did make TableB an entity, so that it was in your model, you should be able to do something like the following (not sure if this is exactly correct, but the concept should be sound):

var tableAResults = from ta in db.TableA
                    where 
                    (
                        from tb in db.TableB 
                        select db.MyID
                    ).Contains(ta.MyID)
                    select ta;

If you are using EF v1.0, this kind of query may not work. EF v1.0 had some significant limitations on the linq queries it supported, and its SQL generator was terrible. I do not have quite as much experience with EF v4.0 (from .NET 4.0 betas) as I do with v1.0, but I do believe the above query should be possible, and it should be translated to something very similar to you're original SQL query.

(If you are using EF v1.0, plan for a migration to EF v4.0 soon, and have it ready to go once .NET 4.0/VS2010 is out. EF v1.0 has countless annoying limitations, quirks, stupid rules, etc. that just make it a nitemare to use...this one is quite mild compared to some.)

Answer 2

Presuming TableB doesn't appear because it is part of a many-to-many relationship between TableA and some TableC (the only way your question makes sense to me), you would do:

var q = from a in Context.TableA
        where a.TableC.Any() // replace "TableC" with the relationship property name
        select a;

Easy, but you need to get your head around how the relationships work.