Linq to Entities many to many selection: How to force the generation of a JOIN instead of a subselect clause?

c# entity-framework join linq-to-entities subquery

Question

enter image description here

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?

1
4
2/17/2015 6:51:05 AM

Accepted Answer

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);
12
2/26/2013 10:14:43 AM

Popular Answer

Your two queries do very different things. Your Any/EXISTS query gets suppliers who do not have product 1 at all. Your JOIN query gets all suppliers who have any products other than 1, regardless of whether or not they also have product 1.

I don't think you can do what you're looking for with just a JOIN and WHERE - you can do it with an IN clause, but I think the EXISTS query is the most correct way of looking for your data.

In any case, one of the wonderful things about Entity Framework is that you don't have to worry about what gets generated - as long as the LINQ statement is ok, then it will find the best way of writing the query, and you should never have to look at it. That's especially true when you do paging and other things like that, where the LINQ is simple, but the generated SQL is horribly ugly.



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