5

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?

Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
r.vengadesh
  • 1,721
  • 3
  • 20
  • 36
  • You're looking for group by.... http://stackoverflow.com/questions/11564311/sql-to-entity-framework-count-group-by – Mick Jun 11 '15 at 06:33

1 Answers1

2

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()
});
Mick
  • 6,527
  • 4
  • 52
  • 67