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

Question

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 }

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jul 29 '22 at 17:32
In `Table1`, how it possible to group by `t1.id` - are there multiple t1 rows with the same `id`? — NetMage, Jul 29 '22 at 17:37

score 0 · Answer 1 · answered Jul 29 '22 at 04:29

0

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

answered Jul 29 '22 at 04:29

Gauravsa

6,330
2
21
30

t2.col2 and t1.id are non-numeric, I think Sum will not work – Daniel Jul 29 '22 at 05:33

score 0 · Accepted Answer · answered Jul 29 '22 at 09:03

Looks like you do not need HAVING here. Just check that right record do not exists:

var query = 
    from t1 in context.Table1
    join t2 in context.Table2 on t1.id equals t2.t1_id into tgroup
    from t2 in tgroup.DefaultIfEmpty()
    where t2 == null
    group t1 by new { t1.id, t1.col2 } into g
    select new { g.Key.id, g.Key.col2 };

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

2 Answers2