title: SQL to C# LINQ query with multiple group by inner queries and having clauses
body: <p>I have a list with set of values. I wanted to check if the table has the matching values with the matching count using a <code>LINQ</code> query in <code>c#</code></p>
  
  <p>This is my list: </p>
  
  <pre><code>List&lt;int&gt; list1 = new List&lt;int&gt;()
          {
            130011,
            130010
          };
  </code></pre>
  
  <p>This is my table</p>
  
  <pre><code>RelEmployeeDepartments:
  
  ID  EmpID DeptID
  8   4     130011
  9   4     130010
  10  4     2
  18  13    130011
  19  13    130010
  20  13    1
  21  13    2
  23  5     130011
  24  5     130010
  </code></pre>
  
  <p>Now i wanted to find all the Employee's who are already assigned to the exact departments <code>list1</code> has along with the same count. So with the values in my <code>list1</code> the output shall be <code>EmpID</code> 5 as it has the same values and same count of departments. <code>EmpID</code> 4 &amp; 13 shouldn't be in my output because even though there are matching departments for the employee, the count is different.</p>
  
  <p>This is the working SQL query i came up with:</p>
  
  <pre><code>SELECT EmpID FROM 
  RelEmployeeDepartments WHERE EmpID IN 
  (SELECT red.EmpID FROM RelEmployeeDepartments red
  GROUP BY red.EmpID HAVING COUNT(red.DeptID) = 2)
  AND DeptID IN (130010,130011) 
  GROUP BY EmpID HAVING COUNT(DeptID) = 2
  </code></pre>
  
  <p>I managed to get the inner query with <code>LINQ</code> like this but couldn't convert it fully.</p>
  
  <pre><code>var innerQuery = (from red in RelEmployeeDepartments 
                             group red by red.EmpID into red1
                             where red1.Count().Equals(list1.length)
                             select red1.Key);
  </code></pre>
  
  <p>I am wondering how can i convert this to a LINQ query or if there is even a better solution than above one?</p>