CODEWITHSUNDEEP
c#linq
delete
delete

Reputation:

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

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.

Upvotes: 4

Views: 2717

Answers (3)

kbrimington
kbrimington

Reputation: 25652

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()
}

Upvotes: 1

Jordan Parmer
Jordan Parmer

Reputation: 37174

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.

Upvotes: 0

John Gietzen
John Gietzen

Reputation: 49544

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()
}

Upvotes: 3

Related Questions