Unable to create a constant value of type 'xxx'. Only primitive types or enumeration types are supported in this context

Question

I have a two tables are state and namelist.

State

------------
id | state
------------
1  |xxx
2  |yyy

namelist

---------------
id|Name   |stateid
--------------
1|aaa     |1
2|abb     |1
3|ccc     |2
4|dds     |2

I want the the result table as

------------------
state   |count
-------------------
xxx     | 2
yyy     | 2

I want the above result with using linq query

I tried below code but that returns error as (Unable to create a constant value of type 'xxx'. Only primitive types or enumeration types are supported in this context.)

var count= (from n in bc.db.state 
            select new 
                  { 
                    states = n.state, 
                    count = (from c in bc.db.namelist 
                             where c.stateid == n.id 
                             select n).Count() 
                   });

Anyone know how to solve this issue?

Answer 1

You need to use group by...

var list= 
(from n in bc.db.state 
join c in bc.db.namelist on n.id equals c.stateid 
group n by n.state into g
select new 
{
    State = g.Key,
    Count = g.Count()
});