How to use Group by and Having count clause in entity framework

Question

I'm new to entity framework and I'm trying to convert an SQL query to entity framework. But I'm not able to convert the having clause.

SQL query is:

select tblRecordingType_ID
from tblEquipmentReadingTypes
group by tblRecordingType_ID
having count(tblRecordingType_ID) > 0

I have written following EF query:

 var items = from o in context.tblEquipmentReadingTypes                            
                        group o by o.tblRecordingType_ID

Please let me know, how i can use having clause with this.

Thanks

Answer 1

you can use where after group by and using into statment:

var items = from o in context.tblEquipmentReadingTypes
            group o by o.tblRecordingType_ID into g
            where g.Count() > 0
            select g;

Answer 2

This:

select tblRecordingType_ID
from tblEquipmentReadingTypes
group by tblRecordingType_ID
having count(tblRecordingType_ID) > 0

is equivalent to this:

select tblRecordingType_ID
from tblEquipmentReadingTypes
where tblRecordingType_ID is not null
group by tblRecordingType_ID

So you can just write something like this in linq:

var items = from o in context.tblEquipmentReadingTypes 
            where o.tblRecordingType_ID != null                           
            group o by o.tblRecordingType_ID into grouped
            select grouped.Key