What Linq query can I use to return a count of all products by Category?

Question

Given the following table structure, how can I use a Linq query to return a list of Category names and the total count of products in that category?

Category
---------
ID
Name

Product
---------
ID 
IDCategory
Name

My ideal, return would be:

Clothes 156
Electronics 2149
Utensils 412

Etc.

EDIT: Thanks for the helpful suggestions, I now have this:

class Program
{
    static void Main(string[] args)
    {
        MyDatabaseEntities entities = new MyDatabaseEntities();

        var result = from c in entities.Categories
                        join p in entities.Products on c.ID equals p.IDCategory
                        group p by c.Name into g
                        select new
                        {
                            Name = g.Key,
                            Count = g.Count()
                        };

        Console.WriteLine(result.ToString());
        Console.ReadLine();
    }
}

How exactly can I output what is in the result? During debugging I can view the variables values and it has what I need I just don't know how to show it.

Answer 1

from c in Category
join p in Product on c.ID equals p.IDCategory
group p by c.Name into g
select new
{
    Name = g.Key,
    Count = g.Count()
}

That will work in any flavor of LINQ, and groups by the Name, rather than the ID of category.

If you are using LINQ to Entities, this may be faster, and groups on ID rather than name:

from p in Product
group p by p.Category into g
select new
{
    Name = g.Key.Name,
    Count = g.Count()
}

Answer 2

public void Linq77()
{
    List<Product> products = GetProductList();

    var categoryCounts =
        from p in products
        group p by p.Category into g
        select new { Category = g.Key, ProductCount = g.Count() };

    ObjectDumper.Write(categoryCounts
}

See this link for specific LINQ examples.

Answer 3

This query ensures that categories with duplicate names are not grouped together.

from p in context.Product
group p by p.Category into grp
select new
{
    CategoryName = grp.Key.Name,
    Count = grp.Count()
}