SQLTeam.com | Weblogs | Forums

Linq to SQL query question

Hi there;
I have 4 tables (Orders, OrdersDetail, Vendors and Customers) here are sample data;

SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (16, N'2022-08-29 16:58:17', N'Cancelled', N'kizildagcenk@gmail.com', 5)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (18, N'2022-09-04 12:07:17', N'Completed', N'user@test.com', 6)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (19, N'2022-09-09 14:12:51', N'Completed', N'kizildagcenk@gmail.com', 7)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (24, N'2022-09-18 18:44:22', N'Continues', N'kizildagcenk@gmail.com', 5)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (35, N'2022-09-19 10:11:38', N'Continues', N'kizildagcenk@gmail.com', 5)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (36, N'2022-09-19 10:11:38', N'Continues', N'kizildagcenk@gmail.com', 6)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (37, N'2022-09-20 23:48:34', N'Continues', N'kizildagcenk@gmail.com', 5)
INSERT INTO [dbo].[Orders] ([Id], [OrderDateTime], [Status], [DoneBy], [CustomerId]) VALUES (38, N'2022-09-21 09:36:57', N'Continues', N'kizildagcenk@gmail.com', 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], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (24, N'000001', N'Shirtt', 100, 14, 12.654, 1110, 1300, N'shipment1', N'Shipped', N'tracking1', N'description1', 16, 1, N'TL', 11.1, 13, N'fff', N'dff', 0, 1265.4)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (25, N'000002', N'Gol', 50, 12, 22.512, 1005.0000000000001, 1250, N'shipment2', N'Being supplied', N'tracking2', N'description2', 16, 1, N'TL', 20.1, 25, N'h', N'g', 0, 1125.6)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (26, N'000003', N'Excipal', 35, 6, 62.54, 2065, 2310, N'shipment3', N'Getting ready', N'tracking3', N'description3', 16, 2, N'TL', 59, 66, N'j', N'h', 0, 2188.9)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (27, N'00004', N'Ball', 45, 12, 207.2, 8325, 9495, N'shipment4', N'Getting ready', N'tracking4', N'description4', 16, 3, N'TL', 185, 211, N'order00012', N'stok0001', 0, 9324)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (28, N'00011', N'Atkı', 40, 11.5, 28.544, 1024, 12440, N'shipment 01', N'Completed', N'tracking 01', N'description 01', 18, 3, N'Euro', 25.6, 311, N'order 011', N'stock 001', 1, 1141.76)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (29, N'0022', N'Şapka', 15, 12, 33.6, 450, 600, N'shipment 02', N'Completed', N'tracking 02', N'desc 02', 18, 1, N'Dolar', 30, 40, N'order 222', N'stock 022', 1, 504)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (30, N'05', N'Gözlük', 10, 20, 1020, 8500, 11000, N'ship05e', N'Cancelled', N'track0', N'desc0', 16, 1, N'TL', 850, 1100, N'order07', N'stock05', 0, 10200)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (31, N'06', N'Çanta', 20, 11.8, 385.4864, 6896, 8020, N'ship06', N'Shipped', N'track06', N'desc06', 16, 1, N'TL', 344.8, 401, N'order06', N'stock06', 0, 7709.728)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (32, N'07', N'Atkı', 1, 10, 24.2, 22, 25, N'ship07r', N'In warehouse', N'track07', N'desc07', 16, 1, N'TL', 22, 25, N'order07', N'stock07', 1, 24.2)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (33, N'08', N'Kalem', 200, 6.6, 21.32, 4000, 4580, N'ship08t', N'At customs', N'track08', N'desc08', 16, 1, N'TL', 20, 22.9, N'order08', N'stock08', 1, 4264)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (34, N'333', N'Lamp', 50, 11, 4.4289000000000005, 199.5, 256, N'abc shipment', N'Completed', N'1234 tracking', N'description', 19, 4, N'Dolar', 3.99, 5.12, N'12345 order', N'customer stokck', 1, 221.44500000000002)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (35, N'4444', N'Handset', 15, 17.5, 131.6, 1680, 2220, N'def shipment', N'Completed', N'569 tracking', N'description', 19, 3, N'Dolar', 112, 148, N'order customer 23242', N'stok 34opı', 1, 0)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (47, N'00-1111', N'', 50, 10, 0, 0, NULL, NULL, N'Getting Ready', NULL, N'description1', 35, 1, NULL, 14, 285, N'11', N'cs01', 1, NULL)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (48, N'000-22', N'', 100, 15, 0, 0, NULL, NULL, N'Getting Ready', NULL, N'description2', 35, 1, NULL, 22.5, 33, N'12', N'cs02', 1, NULL)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (49, N'98937-ABC', N'', 85, 11, 0, 0, NULL, NULL, N'Getting Ready', NULL, N'description3', 35, 3, NULL, 30, 97, N'13', N'cs03', 1, NULL)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (50, N'11111-000A', N'', 100, 8, 0, 0, NULL, NULL, N'Getting Ready', NULL, N'description1', 36, 2, NULL, 11.3, 75, N'10', N'cs01', 1, NULL)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (52, N'222', N'test', 22, 10, 35.2, 704, 88, N'rt', N'Being supplied', N't', N'terr', 16, 4, N'TL', 32, 4, N'e', N'tr', 1, 774.40000000000009)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (53, N'33333', N'test', 34, 43, 334.62, 7956, 544, N'ere', N'At customs', N'ee', N'ere', 18, 4, N'Dolar', 234, 16, N'erere', N'ere', 1, 11377.08)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (54, N'5454', N'e', 5, 3, 44.29, 215, 30, N'd', N'At customs', N'f', N'g', 18, 4, N'TL', 43, 6, N'tt', N'tr', 1, 221.45)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (55, N'4444', N'Cenk', 22, 4, 23.92, 506, 352, N's', N'At customs', N't', N'd', 16, 3, N'Euro', 23, 16, N'c', N'c', 1, 526.24)
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice], [CustomerOrderNumber], [CustomerStockCode], [IsActive], [TotalUnitCost]) VALUES (56, N'44', N'f', 3, 3, 4.12, 12, 21, N's', N'At customs', N't', N'g', 16, 1, N'TL', 4, 7, N'c', N'c', 1, 12.36)
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'vendor 3', N'duadadajdajda', N'mail@mail.comd', N'8329392842823', N'kdakdla daşd', N'dşakdşadad')
INSERT INTO [dbo].[Vendors] ([Id], [Name], [Address], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (4, N'Test Vendor 4', N'adress test adress', N'testvendor@test.com', N'12345678', N'main responsible name', N'Assistant responsible name')
SET IDENTITY_INSERT [dbo].[Vendors] OFF

SET IDENTITY_INSERT [dbo].[Customers] ON
INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (5, 1234567888, N'Adıyaman Vergi Dairesi USA ', N'Customer 1', N'addresss', N'deliivery hero', N'testemail@test.com', N'(332) 2324242', N'Mikail', N'Şaban')
INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (6, 99009988, N'Tax Dairesi', N'Customer 2', N'tedaıjdajda saldkşlsakdşlsakdsa', N'dadasa safasfsadsadsdadsa sadsad sa', N'test@test.com', N'(666) 6666666', N'Main', N'asssddsfddfd')
INSERT INTO [dbo].[Customers] ([Id], [TaxNumber], [TaxAdministration], [Name], [Address], [DeliveryAddress], [Email], [PhoneNumber], [MainResponsibleName], [AssistantResponsibleName]) VALUES (7, 13124214221421, N'Vali Konağı test', N'Test Customer 3', N'Customer Address Test', N'Delivery Address Test 04', N'testcustomermail@mail.com', N'(213) 2323424', N'Responsible Name 04', N'Assistant Responsible 4')
SET IDENTITY_INSERT [dbo].[Customers] OFF

In my application, I am using this LINQ query. I am getting only the active records of the current user.

Orders
   .Where(u => u.DoneBy == user.Identity.Name)
   .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
   .ThenInclude(v => v.Vendor)
   .Include(c => c.Customer)
   .AsNoTracking()
   .ToListAsync();

I want to convert it to SQL, is there any online tools that I can convert?

Something like that? Could there be improvement points?

select *
from Orders o
INNER JOIN OrdersDetail od ON o.Id = od.OrderId
INNER JOIN Vendors v ON od.VendorId = v.Id 
INNER JOIN Customers c ON o.CustomerId = c.Id
where od.IsActive = 1 and o.DoneBy = 'user@test.com'