LINQ to SQL Query for Many-to-Many relationship

Question

I am moving an old ASP.net (C#) application from plain SQL queries to LINQ to SQL and am having some trouble with some of the more complex queries. In this case, I am trying to get a list of employees who have a certain set of skills. The user picks the skills to search for and the id's are supplied to the method that does the work. In the old SQL version, I just appended WHERE clauses to the string for each skill. Here's an example query:

SELECT DISTINCT e.firstname, e.lastname, e.username
FROM employees AS e
WHERE e.id IN (SELECT es.emp_id 
FROM emp_skl AS es 
WHERE es.skl_id = 6 OR es.skl_id = 11 
GROUP BY es.emp_id
HAVING COUNT(es.emp_id) >= 2)

The key is the HAVING COUNT clause since that ensures the employees returned have ALL the skills and not just one. Anyway, can someone help me turn this into a solid LINQ query?

Answer 1

First of all, it's better if your tables don't end with an "S".

Now the code, asuming you have already a function yo get a list of skills:

IQueryable<skills> listSkills = getSkills();
IQueryable<employees> listEmployees = db.employees;

foreach(var skill in listSkills)
{
    listEmployees=listEmployees
        .Where(p=>p.emp_skls.Any(q=>q.skl_id==skill.id));
}

Edit:

for instance:

public IQueyable<skills> getSkills()
{
    return db.skills.Where(p=>p.id==6 || p.id==1);
}

Answer 2

Here's the LINQ translation of your SQL query:

from e in Employees
where e.Emp_Skls.Count (es => es.Skl_id == 6 || es.skl_id == 11) >= 2
select new
{
  e.FirstName, e.LastName, e.UserName
}

However, if your desired query is "give me employees who have both skills 6 and 11", your SQL query will fail if skill 6 or 11 appears twice for an employee (I take it this is possible because you have >= 2 rather than =2 in your having clause). In which case, the following query is a better fit:

from e in Employees
where e.Emp_Skls.Any (es => es.Skl_id == 6) &&
      e.Emp_Skls.Any (es => es.Skl_id == 11)
select new
{
  e.FirstName, e.LastName, e.UserName
}