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

问题描述

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

How to write sub queries like these in EF?

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

或

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

和

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

这些查询工作正常LinqPad或LINQ到SQL

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:

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
    where t1.AssociationToTable2.Col2 == "xyz"
    select new { t1.Col1, t1.Col2, ... };

其中， AssociationToTable2 是关系属性 - 它的自动加入。或者，如果你没有关系：

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.

如果你绝对必须做的IN的方式，我建议去了答案中的这个问题。

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

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