Sub query in Entity Framework

.net entity-framework subquery

Question

How should these subquestions be written 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 made an attempt at them

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 function properly. Linq to Sql or LinqPad

1
3
1/2/2010 5:40:07 PM

Popular Answer

This kind of subquery can be converted to a join, so that's how I'd do it here:

Version in SQL:

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

Version of Linq:

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 immediately joins. Alternatively, if you are single:

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 may modify them in accordance withNOT IN even while I wouldn't advise using itNOT IN If you can prevent it, performance will suffer, and it nearly always indicates a design fault.

I advise you to review the solutions in this inquiry, If you have to do things the "IN" way, do it..

6
5/23/2017 12:01:27 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow