Entity Framework T-Sql "having" Equivalent

Question

How can I write a linq to entities query that includes a having clause?

For example:

SELECT State.Name, Count(*) FROM State
INNER JOIN StateOwner ON State.StateID = StateOwner.StateID
GROUP BY State.StateID
HAVING Count(*) > 1

score 29 · Accepted Answer · answered Sep 10 '09 at 18:39

29

Any reason not to just use a where clause on the result?

var query = from state in states
            join stateowner in stateowners
              on state.stateid equals stateowner.stateid
            group state.Name by state.stateid into grouped
            where grouped.Count() > 1
            select new { Name = grouped.Key, grouped.Count() };

answered Sep 10 '09 at 18:39

Jon Skeet

1,421,763
867
9,128
9,194

5

grouped.Any() would be used for Count() > 0, not Count() > 1 – Lucas Sep 10 '09 at 18:42
2

@Craig: If it had been `Count() > 0` then there'd be no need for a restriction at all, given that it was an inner join... – Jon Skeet Sep 10 '09 at 18:54

score 3 · Answer 2 · answered Sep 10 '09 at 18:39

3

I believe you can use a GroupBy followed by a Where clause and it will translate it as a Having. Not entirely sure though.

answered Sep 10 '09 at 18:39

Andrew Flanagan

4,267
3
25
37

2

For MySql it will cause inner SELECT with outer WHERE, not HAVING. So it will cause performance issue. – Yuri Jan 18 '13 at 11:28

score 0 · Answer 3 · answered Oct 09 '19 at 20:55

If you want to compare a variable that is not in the group by (Ex: age), then it would be:

var duplicated = (
                  from q1 in db.table1
                  where (q1.age >= 10 )
                  group q1 by new { q1.firstName, q1.lastName } into grp
                  where (grp.Count() > 1 )
                  select new 
                   {
                     firstName= grp.Key.firstName,
                     lastName = grp.Key.lastName,
                   }
                 );

Entity Framework T-Sql "having" Equivalent

3 Answers3

Linked

Related