How to do multiple Group By's in linq to sql?

Question

Hi

how can you do multiple "group by's" in linq to sql?

Can you please show me in both linq query syntax and linq method syntax.

Thanks

Edit.

I am talking about multiple parameters say grouping by "sex" and "age".

Also I forgot to mention how would I say add up all the ages before I group them.

If i had this example how would I do this

Table Product

ProductId ProductName ProductQty ProductPrice

Now imagine for whatever reason I had tons of rows each with the same ProductName, different ProductQty and ProductPrice.

How would I groupt hem up by Product Name and add together ProductQty and ProductPrice?

I know in this example it probably makes no sense why there would row after row with the same product name but in my database it makes sense(it is not products).

Answer 1

To group by multiple properties, you need to create a new object to group by:

var groupedResult = from person in db.People
                    group by new { person.Sex, person.Age } into personGroup
                    select new 
                    { 
                        personGroup.Key.Sex, 
                        personGroup.Key.Age, 
                        NumberInGroup = personGroup.Count() 
                    }

Apologies, I didn't see your final edit. I may be misunderstanding, but if you sum the age, you can't group by it. You could group by sex, sum or average the age...but you couldn't group by sex and summed age at the same time in a single statement. It might be possible to use a nested LINQ query to get the summed or average age for any given sex...bit more complex though.

EDIT:

To solve your specific problem, it should be pretty simple and straightforward. You are grouping only by name, so the rest is elementary (example updated with service and concrete dto type):

class ProductInventoryInfo
{
    public string Name { get; set; }
    public decimal Total { get; set; }
} 

class ProductService: IProductService
{

    public IList<ProductInventoryInfo> GetProductInventory()
    {
        // ...

        var groupedResult = from product in db.Products
                            group by product.ProductName into productGroup
                            select new ProductInventoryInfo
                            {
                                Name = productGroup.Key,
                                Total = productGroup.Sum(p => p.ProductCost * p.ProductQty)
                            }

        return groupedResult.ToList();
    }
}