Entity Framework Core sub-query (Filter rows based on results from another table)

In the previous post we saw on how to implement different types of row filters using LINQ queries, like Contains, StartsWith, EndsWith etc. In this post we shall see a more complex type of filter where the rows from the Employee table are filtered based on a specific type of Department Name.

We want to get the list of employees who belong to a Department whose name contains the word Account. In SQL server we can do this using a sub-query as follows.

SELECT * FROM Employee WHERE DepartmentId IN

       (SELECT DepartmentId FROM Department WHERE Name LIKE '%Account%')

To do the same in a LINQ query we need to use both the Employee and Department tables in the context object as follows.

            var Employee = _context.Employee.Where(e => _context.Department.Any(

                                                    d => d.Name.Contains("Account") &&

                                                    d.DepartmentId == e.DepartmentId)

                                                  );

We can also do this using 2 separate LINQ queries, the first query will get the ids of all the departments whose name contains “Account” into an int List, the second query will get the list of employees who have one of the Department Ids in the list returned from the first query.

Following will be the LINQ query implementation

            var depIds =  _context.Department

                                    .Where(d => d.Name.Contains("Account"))

                                    .Select(d => d.DepartmentId)

                                    .ToList();

            var Employee = _context.Employee

                                .Where(e => depIds.Contains(e.DepartmentId.Value));