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
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
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() };
I believe you can use a GroupBy
followed by a Where
clause and it will translate it as a Having
. Not entirely sure though.
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,
}
);