开发者

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);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜