How to query the connection table

Question

Using Entity Framework / LINQ, I need help with the following.

There is a table of people in the database with the PersonId identification column. There is also a Skills table with a SkillId identity column. The two are connected through the third PeopleSkills table, which has its own IdentSkillsId column, an external column referencing PersonId, and a foreign column referencing SkillId.

The method I'm trying to write is passed in a parameter of type List, which contains any number of skills that we are looking for. The method should return a list that is associated with all the skills in the list of input parameters. How to create a list that excludes someone without all the skills in the skills list?

The problem I am facing is that I have very little SQL experience. I have many other programming experiences, but SQL is always a little rude to me. I was thinking about using Join, but that will not work. that is, if my person has skills A and B, and the search list has items for B and C, the connection will match them on B and return the person. I need this person to be expelled because he has neither B nor C.

I also thought about iterating through the skill list and creating a filter, but that seems ugly. This is similar to the problem that LINQ was created to handle, using List to query another list, and there should be an elegant solution.

Answer 1

This might work:

public List<Person> GetPeopleWithAllSkills(List<Skill> skills) { var skillIds = skills.Select(s => s.SkillId); return context.People.Where( p => skillIds.All(id => p.PeopleSkills.Any(ps => ps.SkillId == id))) .ToList(); } 

Give me people who satisfy the condition that all the skills provided are ( Any ) in the list of skills of these people. (They may have more given skills, but no less.)

Answer 2

I used LinqPad, which uses Linq-to-SQL instead of Linq for Entities, but the concepts should be the same.

Firstly, the data that I used for testing.

 create table People (PersonID int, Name varchar(100)) create table Skills (SkillID int, Skill varchar(100)) create table PeopleSkills (PeopleSkillsID int, PersonID int, SkillID int) insert People values (1,'Bert'),(2,'Bob'),(3,'Phil'),(4,'Janet') insert Skills values (1,'C#'),(2,'Linq'),(3,'SQL') insert PeopleSkills values (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,3),(6,3,2),(7,3,3),(8,4,1),(9,4,2),(10,4,3) 

And the solution.

 //I don't know how you are specifying your list of skills; for explanatory purposes //I just generate a list. So, our test skill set is C#, Linq, and SQL. //int? is used because of LinqToSQL complains about converting int? to int var skills = new List<int?>(){1,2,3}; //This initial query is also a small bow to LinqToSQL; Really I just wanted a plain //List so that the Except and Any clauses can be used in the final query. //LinqToSQL can apparently only use Contains; that may or may not be an issue with //LinqToEntities. Also, its not a bad idea to filter the people we need to look at //in case there are a large number anyway. var peopleOfInterest = PeopleSkills.Where( p => skills.Contains(p.SkillID)).ToList(); //Final query is relatively simple, using the !x.Except(y).Any() method to //determine if one list is a subset of another or not. var peopleWithAllSkills = //Get a distinct list of people from person in peopleOfInterest.Select( p=>p.PersonID).Distinct() //determine what skills they have let personSkills = peopleOfInterest.Where(x=>x.PersonID == person).Select(x=>x.SkillID) //check to see if any of the skills we are looking for are not skills they have where !skills.Except(personSkills).Any() select person;