1
Vote

Many-to-many query generation

description

Hi guys,
I have wrote sample code:
    public class Order
    {
        public int Id { get; set; }
        public DateTime OrderDate { get; set; }       
        public ICollection<Product> Products { get; set; }

        public Order()
        {
            Products = new HashSet<Product>();
        }
    }

    public class Product
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public ICollection<Order> Orders { get; set; }

        public Product()
        {
            Orders = new HashSet<Order>();
        }
    }

    var orders = context.Orders
                        .Where(o => o.Id == 1)                                    
                        .Include(o => o.Products)
                        .ToArray();
Generated SQL:
SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[OrderDate] AS [OrderDate], 
    [Project1].[UserId] AS [UserId], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[Title] AS [Title]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[OrderDate] AS [OrderDate], 
        [Extent1].[UserId] AS [UserId], 
        [Join1].[Id] AS [Id1], 
        [Join1].[Title] AS [Title], 
        CASE WHEN ([Join1].[Product_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Orders] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[Product_Id] AS [Product_Id], [Extent2].[Order_Id] AS [Order_Id], [Extent3].[Id] AS [Id], [Extent3].[Title] AS [Title]
            FROM  [dbo].[ProductOrders] AS [Extent2]
            INNER JOIN [dbo].[Products] AS [Extent3] ON [Extent3].[Id] = [Extent2].[Product_Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Order_Id]
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC
There are two problems:
  1. No way to tell EF to use inner join instead of left join.
  2. CASE WHEN ([Join1].[Product_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] expression in SELECT and ORDER BY CLAUSE causing SORT in Query Plan and performance degradation.

comments

RoMiller wrote Jul 2 at 11:04 PM

Hey,

Item 1 is by design. We use a LEFT JOIN because there may not be any related products, in which case an INNER JOIN would mean that no results are returned, even though there is an Order row. We do use an INNER JOIN between the join table and products, because any records in the join table will have a matching product row.

Item 2 Assigning for David to investigate this one.

~Rowan

gandjustas wrote Jul 3 at 7:46 AM

I want to select only orders with associated products using linq:

from o in ctx.Orders
where o.Products.Any()
select new {
    o.Id,
    Products = from p in o.Products
                       select new { p.Id }
}
It produces awful SQL:
SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[C1] AS [C1], 
    [Project2].[Product_Id] AS [Product_Id]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent2].[Product_Id] AS [Product_Id], 
        CASE WHEN ([Extent2].[Product_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Orders] AS [Extent1]
        LEFT OUTER JOIN [dbo].[ProductOrders] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Order_Id]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[ProductOrders] AS [Extent3]
            WHERE [Extent1].[Id] = [Extent3].[Order_Id]
        )
    )  AS [Project2]
    ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC
Can you provide some pattern to do inner join on many-to-many relationships?