EF Core 6 master-detail how to retrieve only matching values

Question

Hello there,

I have Orders and OrdersDetail entities.

SET IDENTITY_INSERT [dbo].[Orders] ON  
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (12, N'2022-08-25 12:09:33', N'Continues', N'Cenk', 5)  
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (13, N'2022-08-28 17:44:46', N'Completed', N'Sedat', 6)  
SET IDENTITY_INSERT [dbo].[Orders] OFF  
  
SET IDENTITY_INSERT [dbo].[OrdersDetail] ON  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) VALUES (18, N'123456', N'Modem', 10, 12, 0, 0, 0, N'ttt', N'Getting ready', N'1224242', N'ffgff', 12, 1, N'TL', 3.5, 4.2)  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) VALUES (19, N'444', N'Fridge', 50, 18, 0, 0, 0, N'y', N'At customs', N'65yu77', N'yyyuuuu', 12, 2, N'Dolar', 1.4, 2.2)  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) VALUES (20, N'3333', N'home', 12, 13, 0, 0, 0, N'ship', N'Completed', N'63737jkkkk', N'dikkat', 13, 1, N'Euro', 5, 6.2)  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) VALUES (21, N'uyuyeh778', N'test', 40, 12, 0, 0, 0, N'fddfd', N'Completed', N'434343', N'fgdgdfd', 13, 1, N'TL', 8, 11)  
SET IDENTITY_INSERT [dbo].[OrdersDetail] OFF  

SET IDENTITY_INSERT [dbo].[Vendors] ON  
INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (1, N'Test Vendor1', N'Test Address1', N'testemail@mail.com', N'123456789', N'responsible1', N'responsible2')  
INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (2, N'Test vendor2', N'Address2', N'vendor@vendor.com', N'000000000011122', N'Main Resp1', N'Assitant Resp1')  
INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (3, N'Add Names', N'duadadajdajda', N'mail@mail.comd', N'8329392842823', N'kdakdla daşd', N'dşakdşadad')  
SET IDENTITY_INSERT [dbo].[Vendors] OFF  

I want to get values based on vendors. Let's say I want to query for Vendor 1 and the query suppose to bring 3 values. Order Id = 12 the first detail, Order Id = 13 both details. But my query below brings just the Order Id = 13. What I am missing?

 public async Task<IEnumerable<Order>> GetOrdersForExport(int vendorId,string status, DateTime? startDateTime, DateTime? endDateTime)  
        {  
              
  
            IQueryable<Order?> result = _db.Orders.Include(c=>c.Customer).Include(d => d.OrderDetails).ThenInclude(v => v.Vendor);  
  
            if (vendorId != null)  
            {  
                result = result.Where(d => d.OrderDetails.All(s => s.VendorId == vendorId));  
            }  
  
            if (status != null)  
            {  
                result = result.Where(dy => dy.Status == status);  
            }  
              
            return await result.ToListAsync();  
        }  

As an example, if I want to query for vendor Id = 1 then I should get;

Get Order Id = 12 and OrderDetail Id = 18

Get Order Id = 13 and OrderDetail Id = 20,21

Any ideas on how to fix this?

Answer 1

You will have to load related records for this you will have to use Eager Loading in EF Core. Kindly refer - https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager