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#
- Linq query with inner join, multiple group by and min max
- LINQ - join with Group By and get average
- WPF DataGrid with multiple source data and grouped columns
- Drag and Drop Multiple Columns in DataGridView
- aggregate two same lists based on two columns and sum the third column
- LINQ to SQL grouping multiple columns with a distinct row
- Create multiple rows and columns xaml
- Insert multiple items into List or Array (no Linq and not quite CodeGolf)
- Return Multiple Columns in Linq to Sql?
- Generate LINQ or Lambda Expression for Multiple columns based on the user selection of checkboxes
- WPF - Ability to group multiple controls together and show one of several 'pages'
- LINQ : Error using multiple from and Join
- How to write the following group by and stripping time Linq query?
- Multiple group join lambda to expression tree
- MVC5 + EF + UOW + Services Layer where to call SaveChanges() and avoid multiple trips to DB?
- Data Access Layer with Multiple Backends and Different Database Designs
- NotSupportedException with linq query and groupBy
- How to predict multiple columns with ML.NET
- What's Different Between LINQ and Entity Framework?
- How to create multiple columns ListView in Xamarin.Forms ?
- Selecting Many Fields From a Table using Linq and Lambda Expressions
- Multiple generic methods with identical names and arguments, but different results and constraints
- Upload multiple files using HttpFileCollectionBase issue with C# and MVC3
- How to group multiple if condition in C#?
- How to run multiple threads and await for complete all of them
- C# Mysql and multiple threads?
- Linq expressions and extension methods to get property name
- System.Reflection.MethodInfo.Invoke and multiple threads
- Dependency injection for DelegatingHandler with multiple constructor parameters and options
- Using DDD and AutoMapper, how do you do work on the same aggregate root in multiple services within a single unit of work?
- ReportingService2005 via WCF - works with impersonation, but not with username/password
- How do I sort the filenames read with Directoryinfo
- Is my C# Entity Frameworks project haunted?
- c# Rounding rules
- System.Security.Claims missing
- Discord.net 1.0 - How to get Messages to delete?
- MongoDB + .NET Core 1.0?
- How to find out what IWin32Window has been created when SetCompatibleTextRenderingDefault throws an InvalidOperationException?
- ASP.NET FormsAuthentication - Length of the data to decrypt is invalid
- Write data in cmd to memory instead of file, catch it with C#
- Selecting dynamically created listboxs items in C#
- Puzzled by the lifetime of the httpclient in the new ihttpfactory
- Two decimal places lost with subtraction
- Smooth polyline with minimal deformation
- Get Repeater's Items
- Sqlite-net extensions relations always null
- Silverlight 5 SDK SLSvcUtil errors after install VS 2015.1 / .NET 4.6.1
- .NET autogenerated web-service client: How do I avoid requesting schemas from w3.org?
- What is this "tool" and how to disable it?
- Dynamically fill mvvm model from indexed array