Linq Group By Question

Question

Hi,

I am trying to group my entities based on vendor id. I couldn't figure out how to sum values. Here is my query so far.

var result1 = from order in Orders  
                         from orderdetail in OrdersDetails  
                         from vendor in Vendors  
                         where order.Id == orderdetail.OrderId && vendor.Id == orderdetail.VendorId && order.Id == 16  
                         select new  
                         {  

                             order.OrderDateTime,  
                             order.CustomerId,  
                             orderdetail.TotalSellPrice,  
                             vendor.Name,  
                             orderdetail.Id,  
                             orderdetail.ProductCode,  
                             orderdetail.ProductName,  
                             orderdetail.Currency,  
                             orderdetail.Quantity,  
                             orderdetail.BuyUnitPrice,  
                             orderdetail.TotalBuyPrice,  

                         };  


result1.Dump();

Here is the linqpad screenshot;

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'Continues', N'Employee 1', 5)  
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 (24, N'000001', N'Shirt', 100, 12, 0, 0, 0, N'shipment1', N'Shipped', N'tracking1', N'description1', 16, 1, N'TL', 11.1, 13)  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) VALUES (25, N'000002', N'Gol', 50, 12, 22.512, 1005.0000000000001, 1250, N'shipment2', N'At customs', N'tracking2', N'description2', 16, 1, N'TL', 20.1, 25)  
INSERT INTO [dbo].[OrdersDetail] ([Id], [ProductCode], [ProductName], [Quantity], [CostRatio], [UnitCost], [TotalBuyPrice], [TotalSellPrice], [ShippingNumber], [Status], [TrackingNumber], [Description], [OrderId], [VendorId], [Currency], [BuyUnitPrice], [SellUnitPrice]) 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)  
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')  
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')  
SET IDENTITY_INSERT [dbo].[Customers] OFF

I want to group by vendor and sum TotalSellPrice, Quantity, BuyUnitPrice and TotalBuyPrice. I kindly request your help.

Thank you.

Answer

Hi @Ruikai Feng - MSFT , thank you for your reply. Is there a way to select all of the below?

                              order.OrderDateTime,  
                              order.CustomerId,  
                              orderdetail.TotalSellPrice,  
                              vendor.Name,  
                              orderdetail.Id,  
                              orderdetail.ProductCode,  
                              orderdetail.ProductName,  
                              orderdetail.Currency,  
                              orderdetail.Quantity,  
                              orderdetail.BuyUnitPrice,  
                              orderdetail.TotalBuyPrice,

Also I want to filter with OrderId as follows;

var result = OrdersDetail  
.Where(x => x.OrderId == 16)  
.GroupBy(x => x.VendorId )  
.Select(a => new { VendorGroupID = a.Key, TotalBuyPriceSum = a.Sum(s => s.TotalBuyPrice),TotalSellPriceSum= a.Sum(s => s.TotalSellPrice) })  
.ToList();  
  
result.Dump();

Answer

Hi,@Cenk ,
I think you could try with include as below:

var orderdetaillist = _context.OrderDetail.Include(x=>x.Order).Include(x=>x.Vendor).Where(x=>x.OrderId==yourtargetid).ToList();

Result:

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Best regards
RuikaiFeng

Share via

Linq Group By Question

2 answers