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

In case the existing answers aren't what you're looking for, please post what happened when you tried to use those query expressions you posted. Did you get a compile-time error? Runtime error? What was the error? Or did you simply not get the results you expected? — Aaronaught
– Aaronaught, Commented Jan 2, 2010 at 16:57
first of all thanks for the immediate response! this is what I'm trying to accomplish - select * from table1 where somestringmanipulation(col1) in (select somestringmanipulation(col1) from table2 where col2 = 'xyz') where table1 and table2 does not have any relationships. I'm getting the following error when I try to use Contains, but it is giving results in LinqPad LINQ to Entities does not recognize the method 'Boolean Contains[Int32](System.Linq.IQueryable`1[System.Int32], Int32)' method, and this method cannot be translated into a store expression. — Gokulnath
– Gokulnath, Commented Jan 2, 2010 at 17:12
I don't think I can accomplish this without using IN or NOT IN, for time being I had planned to use stored procedure and later on I'll look into stackoverflow.com/questions/374267/… — Gokulnath
– Gokulnath, Commented Jan 2, 2010 at 17:44
Your query as written definitely does not require IN/NOT IN. I don't doubt that the real scenario is more complicated than your example - but I would suggest that you try to find a way to use simple joins, because they are easier to read/maintain and most often result in better performance (especially NOT IN, that's a scary one). Perhaps if you included a more specific example, somebody could help you with that. — Aaronaught
– Aaronaught, Commented Jan 2, 2010 at 18:02
Please update the original question with what you're trying to do; I'll edit my answer accordingly. And be specific - some .NET string operations can be translated to SQL, some can't. — Aaronaught
– Aaronaught, Commented Jan 2, 2010 at 19:41

Community · Accepted Answer · 2017-05-23 12:01:27Z

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.

James Manning · Accepted Answer · 2010-01-02 16:43:47Z

0

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.

answered Jan 2, 2010 at 16:43

James Manning

13.6k2 gold badges43 silver badges64 bronze badges

2 Comments

Aaronaught Over a year ago

Lazy-loading shouldn't be an issue here because this is a query transformation - it's never actually evaluating t1.Table2.col2 in the CLR. If there's a difference between Linq to SQL (which LinqPad uses), then I suspect it's because EF doesn't know how to transform the expression the way it's written, but of course we'd have to know what actually happened when he tried his queries in order to be sure.

Craig Stuntz Over a year ago

Aaron is correct: Lazy loading (or not) won't be a factor here.

Collectives™ on Stack Overflow

Entity Framework sub query

2 Answers 2

SQL Version:

Linq Version:

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

SQL Version:

Linq Version:

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related