Search code examples
c#linqlinq-to-sql

LINQ to SQL Query for Many-to-Many relationship

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?

alt text

Solution

  • 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);
}