Linq to Entities 多对多选择:如何强制生成 JOIN 而不是子选择子句? [英] Linq to Entities many to many selection: How to force the generation of a JOIN instead of a subselect clause?

问题描述

首先使用 EF DB 我有两个具有多对多关系的实体(供应商、产品).Entity Framework 不会为关联表 (SupplierProduct) 创建实体，因为关联表仅包含强实体的主键.

Using EF DB first I have two entities (Supplier, Product) that have a many-to-many relationship. Entity Framework does not create an entity for the associated table (SupplierProduct) as the associated table contains only the primary keys of the strong entities.

我通过以下查询获得了所有不提供给定产品的供应商:

I have been getting all Suppliers that do not supply a given product with the following query:

var q1 = context.Suppliers.Where(s=>!s.Products.Any(p=>p.Id == 1));

生成的 SQL 使用与此类似的 EXISTS 相关子查询:

The SQL produced uses an EXISTS dependent subquery similar to this:

SELECT *
FROM Suppliers s
WHERE NOT EXISTS
  (SELECT 1 
   FROM SupplierProduct sp WHERE sp.SupplierId = s.Id && sp.ProductId = 1)

是否有可能使用 Linq to Entities 方法语法来生成使用关联表上的连接的查询?

Is it possible, using Linq to Entities method syntax, to produce a query that uses joins on the associated table instead?

即:

SELECT DISTINCT s.*
FROM SupplierProduct sp
JOIN Supplier s ON s.Id = sp.SupplierId;
WHERE sp.ProductId != 1

更新

正如 JoeEnos 所指出的，我上面的查询并没有做同样的事情.NOT EXISTS 子查询可能是最好的方法.如果我试图让所有确实提供产品的供应商怎么办?我会将我的 linq 更改为实体查询:

As pointed out by JoeEnos my queries above don't do the same thing. The NOT EXISTS subquery is probably the best way to go here. What if I was trying to get all suppliers who did supply a product? I would change my linq to entities query slightly to:

var q1 = context.Suppliers.Where(s => s.Products.Any(p=>p.Id == 1));

生成的 SQL 将是:

And the SQL generated would be:

SELECT *
FROM Suppliers s
WHERE EXISTS
  (SELECT 1 
   FROM SupplierProduct sp WHERE sp.SupplierId = s.Id && sp.ProductId = 1)

这很好，我得到了我想要的结果.但是，如果我在这种情况下编写 SQL，我通常会这样做:

Which is fine, I get the result I want. However if I was writing SQL in this case I would normally do:

SELECT s.*
FROM SupplierProduct sp
JOIN Supplier s ON s.Id = sp.SupplierId;
WHERE sp.ProductId = 1

可以更改我的 linq to entity 查询以生成上述 SQL 吗?

Can my linq to entities query be changed to produce the above SQL?

推荐答案

根据与另一个实体的 m:n 关联选择实体时，生成使用连接而不是 EXISTS 的 SQL SelectMany() 可以使用.例如:

To generate SQL where a join is used instead of EXISTS when selecting an entity based on its m:n association with another entity SelectMany() can be used. Eg:

var q1 = context.Suppliers.Where(s => s.Products.Any(p=>p.Id == 1));

可以改写为:

var q1 = context.Products.Where(p => p.Id == 1).SelectMany(p => p.Suppliers);

这篇关于Linq to Entities 多对多选择:如何强制生成 JOIN 而不是子选择子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！