title: LINQ with GROUP BY and HAVING COUNT
body: <p>I'd like to understand what I am doing wrong with my <code>GROUP BY</code> query in Linq. I've tried many examples (i.e. <a href="https://stackoverflow.com/questions/2078736/linq-with-group-by-having-count">Linq with group by having count</a>), but I still get more results (as is the <code>WHERE</code> is skipped). My code is like this:</p>
  
  <pre><code>var test = session.Query&lt;SomeClass&gt;()
                    .GroupBy(c =&gt; new { c.Var1, c.Var2, c.Var3 })
                    .Where(g =&gt; g.Count() &gt; 1)
                    .Select(g =&gt; g.Key.Var3)
                    .ToList();
  </code></pre>
  
  <p>This gives 229 results (all records). The query that I'd like to build in Linq is:</p>
  
  <pre><code>SELECT Var3
  FROM myTable
  GROUP BY Var1, Var2, Var3
  HAVING COUNT(*) &gt; 1
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>.Select(g =&gt; new { Item = g.Key, Count = g.Count() })
  </code></pre>
  
  <p>But I dont want a list with items (and counts) and having to go through that list, I'd like to have the <code>HAVING</code> part work in the Linq expression...</p>
  
  <p>Edit 2: If you take a look at <a href="https://stackoverflow.com/questions/1869001/linq-group-by-multiple-fields-syntax-help">LINQ Group By Multiple fields -Syntax help</a>, 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.</p>
  
  <p>Anyone who can point me in the right direction?</p>
  
  <p>Edit 1: As I said in my introduction, the question <a href="https://stackoverflow.com/questions/2078736/linq-with-group-by-having-count">Linq with group by having count</a> 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).</p>
  
  <p>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.</p>
  
  <pre><code>var querygroup = session.Query&lt;SomeClass&gt;()
                          .GroupBy(e =&gt; new { e.Var1, e.Var2, e.Var3 })
                          .Select(s =&gt; new { s.Key.Var1, s.Key.Var2, s.Key.Var3, Count = s.Count() })
                          .ToList();
  
  var duplicates = querygroup.Where(g =&gt; g.Count &gt; 1)
                             .Select(g =&gt; new SomeClass() { Var1 = g.Var1, Var2 = g.Var2, Var3 = g.Var3})
                             .ToList();
  </code></pre>
  
  <p>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.</p>
  
  <p>Edit 4: I can ofcourse take the <code>.Where..</code> part in the <code>duplicates</code> variable and add it to the <code>querygroup</code> statement, thus making the whole one statement. Success? Seems overkill but at least it works.</p>
  
  <p>If anyone can find out why I need 2 statements, please elaborate :)</p>