LINQ with GROUP BY and HAVING COUNT

Question

I'd like to understand what I am doing wrong with my GROUP BY query in Linq. I've tried many examples (i.e. Linq with group by having count), but I still get more results (as is the WHERE is skipped). My code is like this:

var test = session.Query<SomeClass>()
                  .GroupBy(c => new { c.Var1, c.Var2, c.Var3 })
                  .Where(g => g.Count() > 1)
                  .Select(g => g.Key.Var3)
                  .ToList();

This gives 229 results (all records). The query that I'd like to build in Linq is:

SELECT Var3
FROM myTable
GROUP BY Var1, Var2, Var3
HAVING COUNT(*) > 1

Somehow, the query is giving me 27 results, but the Linq expression gives me 229 (all). When I replace the where/select part of the Linq expression to the following, I do get a list with counts that are 2 or higher:

.Select(g => new { Item = g.Key, Count = g.Count() })

But I dont want a list with items (and counts) and having to go through that list, I'd like to have the HAVING part work in the Linq expression...

Edit 2: If you take a look at LINQ Group By Multiple fields -Syntax help, this also works for me. However, I'll get a list of objects with Var1, Var2, Var3 and Count. Of this list, I only want to Var3 of the objects with a Count higher than 1.

Anyone who can point me in the right direction?

Edit 1: As I said in my introduction, the question Linq with group by having count is not answering my problem. If I use this code, I still have a set of 229 results instead of the 27 that are actually "duplicated" (meaning, after the group having a count of more than 1).

Edit 3: I am using the following at this moment. I needs two statements, and that I think is weird, but as stated before, this seems to be the only way to select only the records having count > 1.

var querygroup = session.Query<SomeClass>()
                        .GroupBy(e => new { e.Var1, e.Var2, e.Var3 })
                        .Select(s => new { s.Key.Var1, s.Key.Var2, s.Key.Var3, Count = s.Count() })
                        .ToList();

var duplicates = querygroup.Where(g => g.Count > 1)
                           .Select(g => new SomeClass() { Var1 = g.Var1, Var2 = g.Var2, Var3 = g.Var3})
                           .ToList();

Note that instead of selecting only Var3, I decided to select the values Var1 and Var2 aswell and store them in the SomeClass(). This is just an addition, selecting everything doesn't help with creating 1 statement to get this selection.

Edit 4: I can ofcourse take the .Where.. part in the duplicates variable and add it to the querygroup statement, thus making the whole one statement. Success? Seems overkill but at least it works.

If anyone can find out why I need 2 statements, please elaborate :)

Possible duplicate of [Linq with group by having count](http://stackoverflow.com/questions/2078736/linq-with-group-by-having-count) — Pieter Geerkens, Apr 04 '16 at 08:52
Added an 'edit' to my question. As I said in my introduction, I tried it but it doesn't give me the 'correct' result. — Tjab, Apr 04 '16 at 08:58
In order to identify if the problem is with the translated SQL query, try `var testA = session.Query().GroupBy(c => new { c.Var1, c.Var2, c.Var3 }).ToList(); var test = testA.Where(g => g.Count() > 1).Select(g => g.Key.Var3).ToList();` and see what you'll get. — Ivan Stoev, Apr 04 '16 at 09:29
@IvanStoev, here I get a query error on the group, it needs a select since the "id" column isn't part of the GROUP statement. — Tjab, Apr 04 '16 at 10:36
@IvanStoev, if I add a select statement to the `var testA` part (`.Select(b => new { b.Key.Var1, b.Key.Var2, b.Key.Var3, nr = b.Count()})` and select var1, var2, var3 and count, and then check `var test`, I get the desired result. Again though, I think this should be doable in one query/statement? — Tjab, Apr 04 '16 at 10:43
It seems to be a SQL query translation problem then. The LINQ query is ok, but you need to check the queryable provider. From what I see, it's LINQ to SQL, can you check the generated SQL for the non working query? — Ivan Stoev, Apr 04 '16 at 10:49
What is "session.Query()"... have you tried doing a direct grouping instead of going through an intermediary? I suspect case sensitivity is causing the difference. — Amy B, Apr 04 '16 at 12:21
@DavidB, `SomeClass` is an `AuditableEntity` (is an `Entity` is an `EntityWithTypedId` is an `IEntityWithTypedId`/`IPersistentEntity`). Please note that I have no full knowledge about this, it's a project I've taken over from someone and I do not know what was made by this person or taken from other projects. — Tjab, Apr 05 '16 at 05:38
@Tjab Take your original query from the post. Replace `ToList()` with `ToString()`. Do you see something that looks like a SQL? — Ivan Stoev, Apr 05 '16 at 07:51
@IvanStoev I get `NHibernate.Linq.NhQueryable'1[System.Int32]` :) — Tjab, Apr 05 '16 at 09:17
Ah, so it's neither LINQ to SQL or EF, but NHibernate. Such things are important, you should tag the question properly, because every ORM mapper has specifics (and bugs). There is nothing more we can do here, good luck. — Ivan Stoev, Apr 05 '16 at 09:44

Viru · Answer 1 · 2016-04-04T11:36:53.320

1

Try this

var test = session.Query<SomeClass>()
                  .GroupBy(c => new { c.Var1, c.Var2, c.Var3 })
                  .Select(d=> new { var1 = d.Key.var1,var2 = d.Key.var2,var3=d.Key.var3,records = d.ToList()})
                  .Where(e => e.records.Count() > 1)
                  .Select(g => g.Var3)
                  .ToList();

edited Apr 04 '16 at 11:36

answered Apr 04 '16 at 09:29

Viru

2,228
2
17
28

I was having high hopes, because it seems about right, but still too many records (very sure it skips the count part and selects all records). Your example has the same result as `SELECT Var3 FROM table GROUP BY Var1, Var2, Var3 HAVING COUNT(*) > 1` when I remove the `HAVING` part. – Tjab Apr 04 '16 at 10:22
Even better, in your example, if I change the select part to `.Select(g => g.records.Count())`, I get a list of 229 (all) records, something like `1,1,1,1,1,1,4,1,1,1,1,1,1,1,1,5,1,1,1,1,1` etc (27 times NOT 1). I'm very confused by this, since I'd like to select only the NOT 1 records.... – Tjab Apr 04 '16 at 10:30
1

@Tjab I have updated answer..give that also try....I have materialized records by doing ToList...I feel there is some problem happening due to deffered execution..This is more of hunch give it a try..By looks of it your query in question should work. – Viru Apr 04 '16 at 11:38
Doesn't seem to work (problems with the ToList at the moment). I'll update the question and provide an answer (workaround) for now, having two queries that work to give the answer I need. – Tjab Apr 04 '16 at 12:03

Wicher Visser · Answer 2 · 2016-04-04T09:25:55.753

You can try this in Linqpad. I changed the table and column name of your query to match against my own DB:

Levels
.GroupBy(c => new { c.CourseGuid, c.ModuleName })
.Where(g => g.Count() > 10)
.Select(g => g.Key.CourseGuid)

This was the SQL query it produced:

-- Region Parameters
DECLARE @p0 Int = 10
-- EndRegion
SELECT [t1].[CourseGuid]
FROM (
    SELECT COUNT(*) AS [value], [t0].[CourseGuid]
    FROM [xrt].[Levels] AS [t0]
    GROUP BY [t0].[CourseGuid], [t0].[ModuleName]
    ) AS [t1]
WHERE [t1].[value] > @p0

Not one with HAVING COUNT but with WHERE instead, and functionally identical. Playing with the @p0 parameters shows that the output gets filtered properly.

I recommend you try with Linqpad to see what your query does against your data.

But that's the same as `HAVING` inside the subquery. – Ivan Stoev Apr 04 '16 at 09:10 — Ivan Stoev, Apr 04 '16 at 09:10

score 0 · Accepted Answer · answered Apr 04 '16 at 12:13

So, the answer to my question:

var duplicates = session.Query<SomeClass>()
                        .GroupBy(e => new { e.Var1, e.Var2, e.Var3 })
                        .Select(s => new { s.Key.Var1, s.Key.Var2, s.Key.Var3, Count = s.Count() })
                        .ToList()
                        .Where(g => g.Count > 1)
                        .Select(g => new SomeClass() { Var1 = g.Var1, Var2 = g.Var2, Var3 = g.Var3})
                        .ToList();

Seems a bit overkill, I feel like it could be smaller, so if anyone likes to comment on that...

LINQ with GROUP BY and HAVING COUNT

3 Answers3