Raysefo

Raysefo

  • 1.9k
  • 264
  • 127.6k

EF Core 6 master-detail retrieve matching values

Aug 26 2022 7:39 AM

Hello,

I have Orders and OrdersDetail entities. 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?

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 dasd', N'dsakdsadad')
SET IDENTITY_INSERT [dbo].[Vendors] OFF
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

Thanks in advance.


Answers (1)