SQL to C# LINQ query with multiple group by inner queries and having clauses

Question

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 LINQ query in c#

This is my list:

List<int> list1 = new List<int>()
        {
          130011,
          130010
        };

This is my table

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

Now i wanted to find all the Employee's who are already assigned to the exact departments list1 has along with the same count. So with the values in my list1 the output shall be EmpID 5 as it has the same values and same count of departments. EmpID 4 & 13 shouldn't be in my output because even though there are matching departments for the employee, the count is different.

This is the working SQL query i came up with:

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

I managed to get the inner query with LINQ like this but couldn't convert it fully.

var innerQuery = (from red in RelEmployeeDepartments 
                           group red by red.EmpID into red1
                           where red1.Count().Equals(list1.length)
                           select red1.Key);

I am wondering how can i convert this to a LINQ query or if there is even a better solution than above one?

score 2 · Answer 1 · answered Jun 14 '18 at 01:37

2

you could use a combination of Contain, GroupBy, and Where clauses:

var result = RelEmployeeDepartments
                 .Where(e => list1.Contains(e.DeptID))
                 .GroupBy(g => g.EmpId)
                 .Where(grp => grp.Count() == list1.Count())

above will give you IGrouping, you can then Select and SelectMany to get IEnumerable<int> of EmpID

result.Select(grp => grp.Select(v => v.EmpId)).SelectMany(x => x).Distinct()

answered Jun 14 '18 at 01:37

farzaaaan

410
3
9

This is giving me 4,5 & 13 too where as the output needs to be only 5 – hakuna Jun 14 '18 at 02:06
do you know whether it's returning all the three number due the `.Where(...Contains...` failing or `.Where(...Count...` failing? – farzaaaan Jun 14 '18 at 13:40
Its the issue with the count form whatever i see. This is the sql query i get from your LINQ query using LINQPad: DECLARE p0 Int = 130010 DECLARE p1 Int = 130011 DECLARE p2 Int = 2 -- EndRegion SELECT DISTINCT [t2].[EmpID] FROM ( SELECT COUNT(*) AS [value], [t0].[EmpID] FROM [RelEmployeeDepartments] AS [t0] WHERE [t0].[DeptID] IN (p0, p1) GROUP BY [t0].[EmpID] ) AS [t1] CROSS JOIN [RelEmployeeDepartments] AS [t2] WHERE ([t1].[value] = p2) AND ([t1].[EmpID] = [t2].[EmpID]) AND ([t2].[DeptID] IN (p0, p1)); removed @ for variables as stackoverflow is not allowing. – hakuna Jun 14 '18 at 16:03
that's odd, here is [another answer](https://stackoverflow.com/a/1406978/2788979) suggesting the same solution. maybe try that notation ? – farzaaaan Jun 14 '18 at 16:42
I got half part right this way , just needed to put a contains query some where. Can you take a look please ? from red in RelEmployeeDepartments group red by red.EmpID into red1 where red1.Count().Equals(list1.length) select red1.Key – hakuna Jun 14 '18 at 17:21
contains goes behind `group red by..` so something like `from red in RelEmployeeDepartments where list1.Contains(red.DeptID) group red ...` – farzaaaan Jun 14 '18 at 17:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/173165/discussion-between-srini-and-farzan-mirheydari). – hakuna Jun 14 '18 at 17:47

hakuna · Accepted Answer · 2018-06-15T00:17:30.547

Finally after lot of struggle,help with @farzan-mirheydari answer and Using linq to group a table that contains substrings this is my working solution.It's an exact conversion of my SQL query in the question

from red1 in RelEmployeeDepartments
where
 (from red in RelEmployeeDepartments group red by new {
   red.EmpID
  }
  into g where g.Count() == list1.Count() select new {
   g.Key.EmpID
  }).Contains(new {
  EmpID = red1.EmpID
 }) &&
 (list1).Contains(red1.DeptID)
group red1 by new {
 red1.EmpID
}
into g
where g.Count() == list1.Count()
select new {
 g.Key.EmpID
};

Lambda Code:

    RelEmployeeDepartments
   .Where(
    red1 =>
    (RelEmployeeDepartments
     .GroupBy(
      red =>
      new {
       EmpID = red.EmpID
      }
     )
     .Where(g => (g.Count() == list1.Count()))
     .Select(
      g =>
      new {
       EmpID = g.Key.EmpID
      }
     )
     .Contains(
      new {
       EmpID = red1.EmpID
      }
     ) &&
     list1.Contains(red1.DeptID)
    )
   )
   .GroupBy(
    red1 =>
    new {
     EmpID = red1.EmpID
    }
   )
   .Where(g => (g.Count() == list1.Count()))
   .Select(
    g =>
    new {
     EmpID = g.Key.EmpID
    }
   );

I know it looks complex. If there is a better or easier way, please post it.

SQL to C# LINQ query with multiple group by inner queries and having clauses

2 Answers2