实体框架子查询 [英] Entity Framework sub query

问题描述

如何在EF中写入这样的子查询？

  select * from table1 where col1 in（select col1 from table2 where col2 ='xyz'）
  

或

< p $ p> select * from table1 where col1 not in（select col1 from table2 where col2 ='xyz'）


我尝试过这样的东西

  from table1 
 where（from t2在table2中col2 ='xyz'选择t2.col1）.Contains（t1.col1）
选择t1 
  

和

  from t1 in table1 
 where！（from t2 in table2 where col2 ='xyz 'select t2.col1）.Contains（t1.col1）
 select t1 
  

这些查询正在工作的LinqPad或Linq到Sql

解决方案

这种类型的子查询可以被平坦化为一个join，这是我的方式选择在这里写：

SQL版本：

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

Linq版本：

  var query = 
 from t1 in context.Table1 
其中t1.AssociationToTable2.Col2 ==xyz
选择新的{t1.Col1，t1.Col2，...}; 
  

其中 AssociationToTable2 是关系属性 - 它自动进行连接。或者，如果您没有关系：

  var query = 
 from t1 in context.Table1 
加入t2在context.Table2 
在t1.Col1等于t2.Col1 
其中t2.Col2 ==xyz
选择新的{t1.Col1，t1.Col2，.. 。}; 
  

您可以根据 NOT IN ，尽管我建议不要使用 NOT IN ，如果你可以避免它 - 性能会下沉，几乎总是意味着设计错误。

如果您绝对必须以IN的方式执行，我建议您在这个问题。

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

解决方案

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.

这篇关于实体框架子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！