Linq query with multiple counters

Question

I am trying to query this table using LINQ:

Here is what I want to do:

Here is my LINQ query:

var query = from a in table where a.Country.Equals("USA") group a by a.Product_brand into grp select new { Product_brand = grp.key.Product_brand, Country = grp.Key.Country, Black = grp.Count(a => a.Black=="Yes"), White = grp.Count(a => a.White=="Yes"), Red = grp.Count(a=> a.Red=="Yes"), Green = grp.Count(a=> a.Green=="Yes") } 

I do not know what is wrong with my request, I continue to receive this message:

Alternative solution:

Sql request:

 SELECT [Product brand], Country, sum(case when [Black] = 'Yes' then 1 else 0 end) as Black, sum(case when [White] = 'Yes' then 1 else 0 end) as White, sum(case when [Red] = 'Yes' then 1 else 0 end) as Red, sum(case when [Green] = 'Yes' then 1 else 0 end) as Green, FROM dbo.Table group by [Product brand], Country 

Answer 1

You need to group by two fields if you want them to work something like:

 var query = from a in table where a.Country.Equals("USA") group a by new {a.Product_brand, a.Country} into grp select new { Product_brand = grp.key.Product_brand, Country = grp.Key.Country, Black = grp.Count(a => a.Black=="Yes"), White = grp.Count(a => a.White=="Yes"), Red = grp.Count(a=> a.Red=="Yes"), Green = grp.Count(a=> a.Green=="Yes") } 

Answer 2

In VB.Net code, this is similar to

 Dim query=(from a in table where a.Country = "USA" group a by a.Product_brand,a.country into grp = group _ select new with { .Product_brand=Product_brand, .country=country, .Black = grp.Count(Function(a) a.Black="Yes"), .White = grp.Count(Function(a) a.White="Yes"), .Red = grp.Count(Function(a) a.Red="Yes"), .Green = grp.Count(Function(a) a.Green="Yes") })