Linq Group By Question

Cenk 956 Reputation points
2022-08-29T18:44:32.437+00:00

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;
235847-groupby.png

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.

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
697 questions
Blazor
Blazor
A free and open-source web framework that enables developers to create web apps using C# and HTML being developed by Microsoft.
1,382 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Cenk 956 Reputation points
    2022-08-30T11:44:00.267+00:00

    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();  
    
    0 comments No comments

  2. Ruikai Feng - MSFT 2,526 Reputation points Microsoft Vendor
    2022-08-31T08:32:45.753+00:00

    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:
    236429-qa8312.png

    ----------

    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

    0 comments No comments