Convert SQL to LINQ JOIN/GROUP BY/COUNT/DISTINCT
I got a rather tough query to convert from SQL to LINQ-to-Entities. Here's my SQL code:
select c_id
from db.c
inner join db.i on c_id = i_c
inner join db.l on c_id = l_c
group by c_id
having count(distinct i_attributeX) > count(distinct l_attributeY)
I seem to have problems with the distinct in linq. Any suggestions?
Cheer开发者_Go百科s
How's this:
var result = db.c
.Where(c =>
c.i.Select(i => i.attributeX).Distinct().Count() >
c.l.Select(l => l.attributeY).Distinct().Count()
)
.Select(c => c.id);
or alternatively
var result = db.c
.Where(c =>
c.i.GroupBy(i => i.attributeX).Count() >
c.l.GroupBy(l => l.attributeY).Count()
)
.Select(c => c.id);
精彩评论