PreProcessQuery help with SQL “IN” type logic

Question

Smart people help me please!

I am having problems with a PreProcessQuery.

Here are the data entities

Skills entity with WorkerSkill and JobSkill junction records linked to Worker and Job entities.

Worker can have many Skills (Skill1, Skill2, Skill3, Skill4, Skill5)

Job requires many Skills (Skill2, Skill4)

When I am viewing a Job, how can I set up a query to bring back only the Workers that have the skills necessary for the Job?

Regards Cliff

Answer 1

I would create a query over the Workers entity set that takes a JobId parameter (of type Integer). Here's the code that I came up with and tested it:

partial void WorkersQualifiedForJob_PreprocessQuery(int? JobId, ref IQueryable<Worker> query)
{
    // Get the IDs of the skills that the job requires
    IEnumerable<int> jobSkills = this.DataWorkspace.ApplicationData.Skills.Where(skill => skill.JobSkills.Any(js => js.Job.Id == JobId)).Execute().Select(skill => skill.Id);

    // Only allow a worker if the set of skills the worker has completely contains all of the required skills
    query = query.Where(worker => !jobSkills.Except(worker.WorkerSkills.Select(ws => ws.Skill.Id)).Any());
}

---

Justin Anderson, LightSwitch Development Team

Answer 2

Brilliant!

Justin thanks for the guidance that worked like a charm.

I will place this in my tool bag so I will know how to do it next time.

Cliff

Answer 3

Justin

Thanks so much for the example code, it does exactly what I asked for.

I am LINQ challenged could you please show me how to modify code to do the following.

I want to filter the content and only bring back Workers who have the Skill and have a SkillLevel == 3 found in the WorkerSkill entry.  Where in the LINQ statement would I put this additional filter?

Thanks again

Cliff