LINQ with GROUP BY and filter by COUNT on specific column/field-CodePudding

How to translate below SQL to LINQ (query syntax)

select t1.id, t1.col2
from Table1 t1 left join Table2 t2 on t1.id=t2.t1_id
group by t1.id, t1.col2
having count(t2.id) = 0

Table2 schema

id - PK
t1_id - FK to Table1

This what I have so far. I cannot figure the "having count(t2.id) = 0" part

from t1 in context.Table1
join t2 in context.Table2 on t1.id equals t2.t1_id into tgroup
from t2t in tgroup.DefaultIfEmpty()
group t2t by new { t1.col1, t1.col2 } into g
where g.Count ==> THE PROBLEM
select new { g.Key.id, g.Key.col2 }

CodePudding user response：

Have you tried linqer http://www.sqltolinq.com and SQL-> LINQ converter..

CodePudding user response：

Can you do this:

from t1 in context.Table1 join t2 in 
context.Table2 on t1.id equals t2.t1_id 
into tgroup 
from t2t in 
tgroup.DefaultIfEmpty() 
group t2t by new { 
t1.col1, t1.col2 } into g     
select new { g.Key.id, g.Key.col2, sum = g.Sum(t2.col2) }