Entity Framework sub query

Question

How to write sub queries like these in EF?

select * from table1 where col1 in (select col1 from table2 where col2 = 'xyz')

or

select * from table1 where col1 not in (select col1 from table2 where col2 = 'xyz')

I tried something like these

from t1 in table1
where (from t2 in table2 where col2 = 'xyz' select t2.col1).Contains(t1.col1)
select t1

and

from t1 in table1
where !(from t2 in table2 where col2 = 'xyz' select t2.col1).Contains(t1.col1)
select t1

these queries are working fine LinqPad or Linq to Sql

Answer 1

If there's a foreign key in place, you should just use the association properties and let EF translate it into the proper joins/subqueries, like:

from t1 in table1
where t1.Table2.col2 == "xyz"
select t1

Not sure about your specific scenario, but one key difference is that EF defaults to not doing lazy loading, so you may need to Include() columns (wouldn't have needed this in linq-to-sql or LinqPad which uses linq-to-sql) to eager-load them, or alternatively Load() afterwards.

http://msdn.microsoft.com/en-us/library/bb896249.aspx

If you could end up sharing a little bit more concrete schema, we could tell for sure if that's what's going on, I hope.

Answer 2

This type of subquery can be flattened to a join, which is the way I would choose to write it here:

SQL Version:

SELECT t1.col1, t1.col2, t1.col3, ...
FROM table1 t1
INNER JOIN table2 t2
    ON t1.col1 = t2.col1
WHERE t2.col2 = 'xyz'

Linq Version:

var query =
    from t1 in context.Table1
    where t1.AssociationToTable2.Col2 == "xyz"
    select new { t1.Col1, t1.Col2, ... };

Where AssociationToTable2 is the relationship property - it does the join automatically. Or, if you don't have a relationship:

var query =
    from t1 in context.Table1
    join t2 in context.Table2
        on t1.Col1 equals t2.Col1
    where t2.Col2 == "xyz"
    select new { t1.Col1, t1.Col2, ... };

You can adapt these accordingly for NOT IN, although I'd recommend never to use NOT IN if you can avoid it - performance will sink and it almost always implies an error in design.

If you absolutely must do it the "IN" way, I suggest going over the answers in this question.