locked
PreProcessQuery help with SQL “IN” type logic RRS feed

  • 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

    Monday, August 27, 2012 1:03 AM

Answers

  • 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

    Monday, August 27, 2012 5:10 AM
    Moderator

All replies

  • 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

    Monday, August 27, 2012 5:10 AM
    Moderator
  • 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

    Tuesday, August 28, 2012 2:12 AM
  • 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

    Thursday, August 30, 2012 12:09 AM