Linq to Entities many to many selection：如何强制生成一个JOIN而不是一个subselect子句？ [英] Linq to Entities many to many selection: How to force the generation of a JOIN instead of a subselect clause?

问题描述

使用EF DB首先我有两个实体（供应商，产品）有多对多的关系。实体框架不会为关联表创建一个实体（SupplierProduct），因为关联表仅包含强实体的主键。

我已经得到所有供应商不要提供给定的产品以下查询：

  var q1 = context.Suppliers.Where（s =>！s .Products.Any（p => p.Id == 1））; 
  

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

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

可以使用Linq to Entities方法语法，以产生使用关联表中的连接的查询？

ie：

  SELECT DISTINCT s。* 
 FROM SupplierProduct sp 
 JOIN供应商的ON s.Id = sp.SupplierId; 
 WHERE sp.ProductId！= 1 
  

更新 p>

正如JoeEnos所指出的，我上面的查询不会做同样的事情。 NOT EXISTS子查询可能是最好的方法。如果我试图让所有供应商提供产品？我会将我的linq更改为实体查询：

  var q1 = context.Suppliers.Where（s => s。 Products.Any（p => p.Id == 1））; 
  

生成的SQL将是：

  SELECT * 
 FROM Suppliers s 
 WHERE EXISTS 
（SELECT 1 
 FROM SupplierProduct sp WHERE sp.SupplierId = s.Id&& ; sp.ProductId = 1）
  

哪个好，我得到我想要的结果。但是，如果我在这种情况下编写SQL，我通常会这样做：

  SELECT s。* 
 FROM SupplierProduct sp 
 JOIN供应商的ON s.Id = sp.SupplierId; 
 WHERE sp.ProductId = 1 
  

我的linq对实体查询是否可以更改，以上SQL？

解决方案

根据其m：n关联选择实体时，生成使用连接代替EXISTS的SQL可以使用另一个实体 SelectMany（）。例如：

  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）; 
  

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));

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)

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

ie:

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

Update

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));

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)

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

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

解决方案

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));

Can be rewritten to:

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

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