CODEWITHSUNDEEP
c#linq
Evonet
Evonet

Reputation: 3640

How to group linq query by dynamic items

I have a grouped linq query that groups items by category, and then the location within that category:

var categories = db.Items.GroupBy(p => p.Category).
    Select(group =>
    new
    {
        name = group.Key,
        data = new List<int>()
        {
            group.Count(p => p.City == "Sydney"),
            group.Count(p => p.City == "Melbourne"),
            group.Count(p => p.City == "Brisbane"),
            group.Count(p => p.City == "Perth"),
            group.Count(p => p.City == "Canberra"),
        },
        total = group.Count()
    });

As you can see, I'm hardcoding the location to get the count for each location within a category. I don't want to hardcode this as the locations are stored separately in the database, is there a way I can modify the query so the counts are dynamic? For example, for everything in db.locations?

Upvotes: 1

Views: 91

Answers (2)

M.kazem Akhgary
M.kazem Akhgary

Reputation: 19149

You can use another GroupBy.

var categories = db.Items.GroupBy(p => p.Category).Select(group =>
new
{
    Name = group.Key,
    Data = group.GroupBy(g => g.City),
    Total = group.Count()
});

You can also have another anonymous type inside.

var categories = db.Items.GroupBy(p => p.Category).Select(group =>
new
{
    Name = group.Key,
    Data = group.GroupBy(g => g.City).Select(c => 
           new
           {
               CityName = c.Key, 
               Count = c.Count()
           }),
    Total = group.Count()
});

Here is how to use it. if you are confused.

foreach(var category in categories)
{
    var name = category.Name;
    var data = category.Data;
    var total = category.Total;

    foreach(var location in data)
    {
        var cityname = location.CityName;
        var count = location.Count;
    }
}

Note that if you want to pass this query as parameter to somewhere else, you have to use dynamic keyword instead of var.

var would be fast. but dynamic is slow so in that case I recommend you to write custom class for holding your data instead of using anonymous type.

Upvotes: 2

Surya
Surya

Reputation: 101

var categories = db.Items.GroupBy(p => p.Category).
Select(group =>
new
{
    name = group.Key,
    data = new List<int>()
    {
        group.Count(p => p.City == db.locations.select(loc=>loc.ToString()).ToString()),
    },
    total = group.Count()
});

Upvotes: 1

Related Questions