How to group by multiple columns using LINQ

Group by Multiple Columns in LINQ

For example, Use an anonymous type in linq group by multiple fields

// linq groupby multiple columns
group x by new { x.Column1, x.Column2 }

To group the products by both Category and Brand, you can use an anonymous object as the key in the group by clause:

For example, c# linq by group multiple fields

// linq multiple group by
var query = (from t in Transactions
             group t by new { t.MaterialID, t.ProductID } into grp
             select new
             {
                 grp.Key.MaterialID,
                 grp.Key.ProductID,
                 Quantity = grp.Sum(t => t.Quantity)
             }).ToList();

Since C# 7.0 you can also use value tuples with linq group by multiple columns.

A Value Tuple is a lightweight and efficient way to store multiple values without creating a full-fledged class or struct. It can be especially useful when you need to group by multiple columns.

group x by (x.Column1, x.Column2)

or

.GroupBy(x => (x.Column1, x.Column2))

For example, c# groupby multiple fields

// group by linq c# multiple columns
var query = Transactions
    .GroupBy(t => (t.MaterialID, t.ProductID))
    .Select(grp => new
    {
        grp.Key.MaterialID,
        grp.Key.ProductID,
        Quantity = grp.Sum(t => t.Quantity)
    })
    .ToList();

or

For example, group by two columns in linq c#

// c# group by multiple values
var result = 
    from x in Transactions
    group x by (x.MaterialID, x.ProductID) into g
    select (g.Key.MaterialID, g.Key.ProductID, QuantitySum: g.Sum(x => x.Quantity));

Entity framework group by multiple columns

In Entity Framework, you can perform a GroupBy operation on multiple columns by creating an anonymous object or using a composite key.

Suppose you have an Order entity with CustomerId, OrderDate, and Amount properties, and you want to group by CustomerId and OrderDate and calculate the total amount for each group.

// linq to sql group by multiple columns
using (var context = new NorthwindDbContext())
{
    // ef core group by multiple columns
    var result = context.Orders
                        .GroupBy(o => new { o.CustomerId, o.OrderDate })
                        .Select(g => new
                        {
                            CustomerId = g.Key.CustomerId,
                            OrderDate = g.Key.OrderDate,
                            TotalAmount = g.Sum(o => o.Amount)
                        })
                        .ToList();

    foreach (var item in result)
    {
        Console.WriteLine($"CustomerId: {item.CustomerId}, OrderDate: {item.OrderDate}, TotalAmount: {item.TotalAmount}");
    }
}

In this example:

• GroupBy(o => new { o.CustomerId, o.OrderDate }): This groups the orders by both CustomerId and OrderDate.
• Select(g => new { ... }): Projects the grouped results into a new anonymous object with the calculated TotalAmount for each group.
• g.Key.CustomerId: Accesses the CustomerId from the group key.
• g.Key.OrderDate: Accesses the OrderDate from the group key.
• g.Sum(o => o.Amount): Sums the Amount for each group.

You can use any number of columns in the GroupBy clause by adding more properties to the anonymous object in new {}. The GroupBy operation in Entity Framework translates to a SQL GROUP BY statement.