将SQL转换为LINQ JOIN/GROUP-BY/COUNT/DISTINCT

Question

我得到了一个相当困难的查询，要从SQL转换为LINQ到Entities。这是我的SQL代码：

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)

我似乎对林克的独特性有意见。有什么建议吗？

干杯

Answer 1

这是怎么回事：

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

或者

var result = db.c
    .Where(c => 
        c.i.GroupBy(i => i.attributeX).Count() >
        c.l.GroupBy(l => l.attributeY).Count()
    ) 
    .Select(c => c.id);