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