How to better write this EF core 6 Group by Query

Question

I am looking at the queries generated by EF core 6 and I am not that happy about it as it generates lo much blocking overhead and in efficient TSQL and I am hoping that I am the problem...

My EF Query:

var query = db.PurchaseOrders
                .Include(i => i.Items)
                .Where(w=>w.Items.Any(a=>a.InventoryItemId==item.Id))
                .GroupBy(g=>g.SupplierId)
                .Select(s => new CurrentInventoryItemSupplier{ SupplierId=s.Key
                                , LastOrder= s.Max(m=>m.OrderDate) 
                                , FirstOrder= s.Min(m=>m.OrderDate) 
                                , Orders= s.Count() 
                }
                ).ToList();   

Generates this:

SELECT [p].[SupplierId], (
          SELECT MAX([p1].[OrderDate])
          FROM [PurchaseOrders] AS [p1]
          WHERE EXISTS (
              SELECT 1
              FROM [PurchasedItem] AS [p2]
              WHERE ([p1].[Id] = [p2].[PurchaseOrderId]) AND ([p2].[InventoryItemId] = @__item_Id_0)) AND ([p].[SupplierId] = [p1].[SupplierId])) AS [LastOrder], (
          SELECT MIN([p4].[OrderDate])
          FROM [PurchaseOrders] AS [p4]
          WHERE EXISTS (
              SELECT 1
              FROM [PurchasedItem] AS [p5]
              WHERE ([p4].[Id] = [p5].[PurchaseOrderId]) AND ([p5].[InventoryItemId] = @__item_Id_0)) AND ([p].[SupplierId] = [p4].[SupplierId])) AS [FirstOrder], (
          SELECT COUNT(*)
          FROM [PurchaseOrders] AS [p7]
          WHERE EXISTS (
              SELECT 1
              FROM [PurchasedItem] AS [p8]
              WHERE ([p7].[Id] = [p8].[PurchaseOrderId]) AND ([p8].[InventoryItemId] = @__item_Id_0)) AND ([p].[SupplierId] = [p7].[SupplierId])) AS [Orders]
      FROM [PurchaseOrders] AS [p]
      WHERE EXISTS (
          SELECT 1
          FROM [PurchasedItem] AS [p0]
          WHERE ([p].[Id] = [p0].[PurchaseOrderId]) AND ([p0].[InventoryItemId] = @__item_Id_0))
      GROUP BY [p].[SupplierId]

(plan: https://www.brentozar.com/pastetheplan/?id=rkOloR7S9)

here ideally I would expect

select 
P.[SupplierId], MAX([p].[OrderDate]) as LastOrder, MIN([p].[OrderDate]) as FirtOrder, COUNT(P.Id) as Orders
FROM [PurchaseOrders] AS [p]
join [PurchasedItem] AS [p2] on [P2].[PurchaseOrderId]=[P].ID
where [p2].InventoryItemId= @__item_Id_0
group by P.[SupplierId]

(https://www.brentozar.com/pastetheplan/?id=rkOloR7S9)

Is there a way to improve the generated TSQL or make a parametrized Function and call the function from EF core?

There is no way this will survive production data volumes

Answer 1

ok, I figured it out, I had to use the Join method and not the Include

This:

var query = db.PurchaseOrders
            .Join(db.PurchaseOrderItems, po => po.Id, pi => pi.PurchaseOrderId
            , (po, pi) => new { SupplierId = po.SupplierId, OrderDate = po.OrderDate, pi.InventoryItemId })
            .Where(w => w.InventoryItemId == item.Id)
            .GroupBy(g => g.SupplierId)
            .Select(s => new CurrentInventoryItemSupplier
            {
                SupplierId = s.Key,
                LastOrder = s.Max(m => m.OrderDate),
                FirstOrder = s.Min(m => m.OrderDate),
                Orders = s.Count(),
            });

Generates

SELECT [p].[SupplierId], MAX([p].[OrderDate]) AS [LastOrder], MIN([p].[OrderDate]) AS [FirstOrder], COUNT(*) AS [Orders]
      FROM [PurchaseOrders] AS [p]
      INNER JOIN [PurchaseOrderItems] AS [p0] ON [p].[Id] = [p0].[PurchaseOrderId]
      WHERE [p0].[InventoryItemId] = @__item_Id_0
      GROUP BY [p].[SupplierId]

when you loop over it, nothing wrong with it :-)