Linq query with multiple count

Question

I am trying to query this table using LINQ:

enter image description here

Here's what I want to do:

enter image description here

Here's 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 don't know what's wrong with my query, I keep getting this message:

enter image description here

Alternative Solution:

Sql query:

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

This doesn't seem some problem in linq, maybe it's in your connection. — King King
– King King, Commented Aug 20, 2013 at 14:06
Is your server down or unreachable? Your query looks fine: as long as the size of your table is not in the billions of rows, you should get your results reasonably quickly. — Sergey Kalinichenko
– Sergey Kalinichenko, Commented Aug 20, 2013 at 14:09
Run SQL profiler to see if your db gets hit. If it does, see what SQL is being generated/executed and see if it's a LINQ issue or SQL issue. — Ocelot20
– Ocelot20, Commented Aug 20, 2013 at 15:43
What a shame - running away, instead of understanding and solving the problem :) — BartoszKP
– BartoszKP, Commented Aug 22, 2013 at 12:46

Amir Ofir · Accepted Answer · 2013-08-26 21:22:00Z

6

You have to group by two fields if you want it 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")
        }

answered Aug 26, 2013 at 21:22

Amir Ofir

1364 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajamohan Anguchamy · Accepted Answer · 2013-09-06 07:55:24Z

0

In VB.Net code is like that

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

answered Sep 6, 2013 at 7:55

Rajamohan Anguchamy

1,7461 gold badge17 silver badges35 bronze badges

Collectives™ on Stack Overflow

Linq query with multiple count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related