In this article, we will learn to use LINQ to group by multiple properties and sum using C#. We have already learned How to use group by in SQL query.
I’m assuming that you are familiar with SQL queries and group by clause If not then please go through the following articles:
Select multiple fields group by and sum
I have a list of Customer objects, Now I want to group the list based on the City and ProductType, and return an object containing the City, ProductType, and the TotalOrderAmount.
If you want to do a query with LINQ in the list of customers and you really don’t know how to do LINQ to group by multiple properties and sum, then you have come to the right place. In this article, we will discuss how we can use the Linq group by multiple columns and sum them together in an object list.
Example 1:
Customers Collection
public class Customer
{
public List<Customer> Getcustomers()
{
List<Customer> customers = new List<Customer>();
customers.Add(new Customer { Name = "John", City = "London", OrderAmount = 2000, ProductType = "Clothing" });
customers.Add(new Customer { Name = "Mark", City = "Paris", OrderAmount = 1800, ProductType = "Jewelry" });
customers.Add(new Customer { Name = "Josh", City = "Sydney", OrderAmount = 200, ProductType = "Household" });
customers.Add(new Customer { Name = "Kath", City = "Paris", OrderAmount = 3500, ProductType = "Health" });
customers.Add(new Customer { Name = "Karlin", City = "Sydney", OrderAmount = 2200, ProductType = "Household" });
customers.Add(new Customer { Name = "Kovii", City = "London", OrderAmount = 3600, ProductType = "Jewelry" });
customers.Add(new Customer { Name = "Jack", City = "Tokyo", OrderAmount = 1400, ProductType = "Clothing" });
customers.Add(new Customer { Name = "Smith", City = "Paris", OrderAmount = 800, ProductType = "Health" });
return customers;
}
public string Name { get; set; }
public string City { get; set; }
public double OrderAmount { get; set; }
public string ProductType { get; set; }
}
Linq Query
List<Customer> customers = new Customer().Getcustomers();
var results = from cust in customers
group cust by new
{
cust.City,
cust.ProductType
} into gp
select new
{
ProductType = gp.Key.ProductType,
City = gp.Key.City,
TotalOrderAmount = gp.Sum(item => item.OrderAmount)
};
foreach (var r in results)
{
Debug.Print(($@"ProductType:""{r.ProductType}"",City:""{r.City}"",TotalOrderAmount:""{r.TotalOrderAmount}"""));
}
Output:
ProductType:"Clothing",City:"London",TotalOrderAmount:"2000"
ProductType:"Jewelry",City:"Paris",TotalOrderAmount:"1800"
ProductType:"Household",City:"Sydney",TotalOrderAmount:"2400"
ProductType:"Health",City:"Paris",TotalOrderAmount:"4300"
ProductType:"Jewelry",City:"London",TotalOrderAmount:"3600"
ProductType:"Clothing",City:"Tokyo",TotalOrderAmount:"1400"
Example 2:
Let’s take one more example of LINQ to group by multiple properties and sum, we have a list of doctor objects, Now I want to group the doctor list based on the Gender and SpecializationId, and return an object containing the Gender, SpecializationId, and the TotalSalary.
Table doctor
ID | NAME | GENDER | SALARY | SpecializationId |
---|---|---|---|---|
1 | Carter | Male | 20000 | 1 |
3 | Gianna | Female | 10000 | 1 |
4 | Brandon | Male | 15000 | 4 |
5 | Julia | Female | 23000 | 7 |
6 | Julian | Male | 45000 | 6 |
7 | Kayla | Female | 25000 | 5 |
9 | Autumn | Female | 13000 | 1 |
10 | Sebastian | Male | 35000 | 2 |
11 | Blake | Male | 39000 | 3 |
12 | Dr. Jacob | Male | 41000 | 4 |
13 | Dr. Henry | Male | 28000 | 5 |
14 | Dr. Tanmay | Male | 36000 | 1 |
Output
SpecializationId | Gender | TotalSalary |
---|---|---|
1 | Female | 23000 |
1 | Male | 56000 |
2 | Male | 35000 |
3 | Male | 39000 |
4 | Male | 56000 |
5 | Female | 25000 |
5 | Male | 28000 |
6 | Male | 45000 |
7 | Female | 23000 |
Linq Query
var results = from doc in db.TblDoctors
group doc by new
{
doc.Gender,
doc.SpecializationId
} into gp
select new
{
Gender = gp.Key.Gender,
SpecializationId = gp.Key.SpecializationId,
TotalSalary = gp.Sum(item => item.Salary)
};
foreach (var r in results)
{
Debug.Print(($@"SpecializationId:""{r.SpecializationId}"",Gender:""{r.Gender}"",TotalSalary:""{r.TotalSalary}"""));
}
Example 3:
I have a product sales table that looks like the one below, Now I want to group the product list based on the Month and Year of datesold, and return an object containing the SoldDate, Product,Qty, CostPrice, and the ShipingCost.
Product sales table
ProductName | Qty | CostPrice | ShipingCost | SoldDate |
---|---|---|---|---|
Computers | 5 | 800 | 20 | 01/15/2022 0:00 |
Sports | 3 | 500 | 20 | 01/15/2022 0:00 |
Cameras | 2 | 400 | 20 | 01/15/2022 0:00 |
furniture | 4 | 250 | 20 | 02/24/2022 0:00 |
Sports | 6 | 500 | 20 | 03/24/2022 0:00 |
furniture | 6 | 250 | 20 | 03/26/2022 0:00 |
Cameras | 8 | 400 | 20 | 04/26/2022 0:00 |
Computers | 9 | 800 | 20 | 04/27/2022 0:00 |
we need to make the SUM of Qty,CostPrice and ShipingCost each month so our output table would look like this:
Output
SoldDate | ProductName | Qty | CostPrice | ShipingCost |
---|---|---|---|---|
Jan-22 | Computers | 5 | 800 | 20 |
Jan-22 | Sports | 3 | 500 | 20 |
Jan-22 | Cameras | 2 | 400 | 20 |
Feb-22 | furniture | 4 | 250 | 20 |
Mar-22 | Sports | 6 | 500 | 20 |
Mar-22 | furniture | 6 | 250 | 20 |
Apr-22 | Cameras | 8 | 400 | 20 |
Apr-22 | Computers | 9 | 800 | 20 |
public class Product
{
public string Name { get; set; }
public int Qty { get; set; }
public int CostPrice { get; set; }
public int ShipingCost { get; set; }
public DateTime SoldDate { get; set; }
public List<Product> GetProducts()
{
var objList = new List<Product>();
objList.Add(new Product() { Name = "Computers", Qty = 5, CostPrice = 800, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
objList.Add(new Product() { Name = "Sports", Qty = 3, CostPrice = 500, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
objList.Add(new Product() { Name = "Cameras", Qty = 2, CostPrice = 400, ShipingCost = 20, SoldDate = new DateTime(2022, 01, 15) });
objList.Add(new Product() { Name = "furniture", Qty = 4, CostPrice = 250, ShipingCost = 20, SoldDate = new DateTime(2022, 02, 24) });
objList.Add(new Product() { Name = "Sports", Qty = 6, CostPrice = 500, ShipingCost = 20, SoldDate = new DateTime(2022, 03, 24) });
objList.Add(new Product() { Name = "furniture", Qty = 6, CostPrice = 250, ShipingCost = 20, SoldDate = new DateTime(2022, 03, 26) });
objList.Add(new Product() { Name = "Cameras", Qty = 8, CostPrice = 400, ShipingCost = 20, SoldDate = new DateTime(2022, 04, 26) });
objList.Add(new Product() { Name = "Computers", Qty = 9, CostPrice = 800, ShipingCost = 20, SoldDate = new DateTime(2022, 04, 27) });
return objList;
}
}
Linq Query
var results = from p in products
let k = new
{
MonthYear = p.SoldDate.ToString("MMM-yy"),
Product = p.Name
}
group p by k into t
select new
{
MonthYear = t.Key.MonthYear,
Product = t.Key.Product,
Qty = t.Sum(p => p.Qty),
CostPrice = t.Sum(p => p.CostPrice),
ShipingCost = t.Sum(p => p.ShipingCost)
};
foreach (var r in results)
{
Debug.Print(($@"SoldDate:""{r.MonthYear}"",Product:""{r.Product}"",Qty:""{r.Qty}"",CostPrice:""{r.CostPrice}"",ShipingCost:""{r.ShipingCost}"""));
}
*If you have any query ,please comment,Thank you
The post [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax appeared first on Software Development | Programming Tutorials.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- Xamarin SQLite.net group columns by data and sum numbers
- Linq query for getting group by values in columns
- How to get multiple columns from one SQL query with SQLite and C#
- Dynamic LINQ or multiple columns
- postgresql query to get sum and columns from two tables using joins
- C# LINQ select from table and join multiple table and join a SQL query view in SQL server
- The sum for every column and raw in a 2D array with N columns
- aggregate three same lists based on two columns and sum the third column
- Order by and group by and sum using SQL
- Bot Framework Emulator v4.0: Simulate group and multiple users
- Getting Records From Multiple Tables SQL Query With Sum And Abstract Function
- C# DataTable group and sum into New DataTable
- I want to sum up my IncomingProduct Table's amount column if materialId and depotId columns are same. Finally i want to subtract these. MVC C#
- Linq group by multiple columns, one of which is selectable
- Sum of single column in Joined tables using Linq query and Get One Item from Modal in View
- How to group by tables in datatable and sum them using Entity Framework
- LINQ to SQL and adding multiple records to foreign table for a single primary key
- Using GroupBy for Multiple Columns and Returning List<TSource>
- Select SUM a column and group by
- Combining multiple fixed width text files and separating data into pipe delimited columns
- LINQ to SQL - multiple result and access to data
- LINQ to SQL - multiple result and access to data
- How to SUM two columns and display the result in the third column by subtracting their aggregates SQL & C#?
- Using stored procedure to get data from multiple columns and display it in a View
- Using stored procedure to get data from multiple columns and display it in a View
- SQL: Getting Data from Multiple Columns using Where and Or
- ASP .NET table sum of rows and columns with textbox
- Binding a ComboBox using a DataContext and LINQ to SQL
- How to read certain text containing line and then append this text to those of multiple files in which this text line is absent?
- C# - DataSets and Data Binding in multiple Forms
- Calling git clone from C# app doesn't return output data
- Finding out is user fully completed download or not in C#
- Secure way of creating SQL Database based on user input in C# app
- Trying to Run Angular 9 Web Project in Visual Studio
- How do I allow a machine to connect to local SQL file
- WCF service with impersonate cannot connect to SQL Server, but SSMS on the same machine will
- Get all objects from a list of objects where one of the object properties is null or empty in c#
- C# - Oledb - Not updating
- HttpRequest Handling
- Upload file on Dropbox using REST api
- How to retain the TextBox control text value across pages in ASP.NET?
- C# Read .html or .rtf and use it as Mail.Body
- Saving the score, then using it in highscore
- Using dataSet to create XML
- How to configure NServiceBus endpoints when exposing more than one method in WCF service contract
- Problems with Selecting a Gallery Image in OnActivityResult method
- C# communication with nodejs
- xamarin forms Image does not show up
- how use Multi Checkbox with binding - wpf
- Azure Function with ServiceBusTrigger not firing when scheduled