如何将字符串linq与子查询与另一个带有子查询的linq进行比较

I Have 4 tables:

我有四个表:

Position Table:

职位表:

|     Position  |  PositionId  | 
|     driver    |     1        | 
|     clerk     |     2        |

position Skill table:

职位技能表:

|  SkillId  |    skill     |     PositionId  |  
|     1     |   driving    |        1        |
|     2     |   drifting   |        1        |

Worker table:

工作表:

|     Name     |    WorkerId | 
|     John     |     1       |
|     alex     |     2       |

Worker skill table:

员工技能表:

|     skillId |    skill     |     WorkerId   |
|     1       |   driving    |        1       |
|     2       |   drifting   |        1       |

I join the position table with position Skill table and worker table with worker skill

我以岗位技能表和工人技能表加入岗位技能表

What I'm having trouble with is how can I compare the two joined tables to have a result of

我遇到的问题是，我如何比较这两个已连接的表的结果

for example:

例如:

I need to know who's worker have all the specific skills that the position have

我需要知道谁是工人，拥有这个职位所具备的所有技能

Like:

如:

I Select position with positionId of 1 and have the skillname of driving and drifting I need to get the Worker with the same skills with driving and drifting also

我选择1号位置，并有驾驶和漂流的技能名，我需要得到具有同样驾驶和漂流技能的工人

so far i got this:

到目前为止，我得到了这个:

var PositionsWithSkills = (from a in db.Client_Customer_Position
                           where a.ID == position
                           select new
                           {
                               PositionID = a.ID,
                               RequiredSkills = (from b in db.Client_Customer_Position_Skills
                                                 where b.ClientCusPosId == a.ID
                                                 select b.SkillName)
                           }).ToList();

var WorkersWithSkills = (from x in db.Workers
                         select new
                         {
                             workerId = x.ID,
                             Skills = (from y in db.Worker_Skills
                                       where y.Worker_ID == x.ID
                                       select y.SkillName)
                         }).ToList();

var PositionWithSkilledWorkers = (from pos in PositionsWithSkills
                                  select new
                                  {
                                      PositionId = pos.PositionID,
                                      Workers = (from worker in WorkersWithSkills
                                                 where pos.RequiredSkills.All(skill => worker.Skills.Any(workerSkill => workerSkill == skill))
                                                 select worker.workerId)
                                  }).ToList();

the two query works well.. but the last query where i must compare the two query =.. i cant get the worker id and can i turn this to a stored proc?

这两个查询工作得很好。但是最后一个查询必须比较两个查询=..我找不到工人id，我能把它转换成存储的proc吗?

5 个解决方案

#1

Sorry if am wrong. What I got to know from your question is you want the workers list satisfying all the skills of the position you pass. If this is what you want you may try this:

如果我错了,对不起。我从你的问题中了解到你希望员工名单能满足你所通过的职位的所有技能。如果这是你想要的，你可以试试:

 var workerWithallSkill = (from u in db.workerList join x in db.workerSkillList on    u.WorkerId equals x.WorkerId 
 where ((from y in db.workerSkillList where y.WorkerId == u.WorkerId select y).Count() == (from p in db.positionSkillList where p.PositionId == 1("pass your positionId here") select p).Count()) 
select u).ToList().Distinct();

or if you want to use lambda expression you can use this

或者如果你想用lambda表达式，你可以用这个

var workerWithallSkill = (from u in workerList join x in workerSkillList on u.WorkerId equals x.WorkerId where (workerSkillList.Where(y=> y.WorkerId == u.WorkerId).Count() ==  positionSkillList.Where(p=>p.PositionId == 1).Count()) select u).ToList().Distinct();

For more understanding you can try the below code:

要了解更多信息，可以试试下面的代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication8
{
    class Program
    {
        static void Main(string[] args)
        {

            IList<Position> positionList = new List<Position>() { 
                new Position(){ position="Driver", PositionId=1}
                ,new Position(){ position="clerk", PositionId=2}
              };


            IList<PositionSkill> positionSkillList = new List<PositionSkill>() {
                new PositionSkill(){ Skill = "driving",skillid = 1,PositionId = 1}
               ,new PositionSkill(){ Skill = "drifting",skillid = 2,PositionId = 1}
            };

            IList<Worker> workerList = new List<Worker>() {
             new Worker(){ name = "John",WorkerId = 1}
            ,new Worker(){ name = "alex",WorkerId = 2}
            };

            IList<WorkerSkill> workerSkillList = new List<WorkerSkill>(){
                new WorkerSkill(){Skill = "driving",skillid = 1,WorkerId = 2}
                , new WorkerSkill(){Skill = "drifting",skillid = 2,WorkerId = 2}
            };

            var workerWithallSkill = (from u in workerList join x in workerSkillList on u.WorkerId equals x.WorkerId where (workerSkillList.Where(y => y.WorkerId == u.WorkerId).Count() == positionSkillList.Where(p => p.PositionId == 1).Count()) select u).ToList().Distinct();

            foreach (var worker in workerWithallSkill)
            {
                Console.WriteLine(worker.name);
            }

            Console.ReadLine();

        }
    }


    public class Position
    {
        public string position { get; set; }
        public int PositionId { get; set; }
    }

    public class PositionSkill
    {
        public int skillid { get; set; }
        public string Skill { get; set; }
        public int PositionId { get; set; }
    }

    public class Worker
    {
        public string name { get; set; }
        public int WorkerId { get; set; }
    }

    public class WorkerSkill
    {
        public int skillid { get; set; }
        public string Skill { get; set; }
        public int WorkerId { get; set; }
    }

}

if a worker has skills from different positions the above code will not work, if this is the scenario try the below code:

如果一个工人有来自不同岗位的技能，上面的代码将不能工作，如果这是一个场景，请尝试下面的代码:

var WorkerPositionSkill = from p in db.positionSkillList join q in db.workerSkillList on p.skillid equals q.skillid select new { posSkill = p, workerSkill = q };

 var workerWithallSkill = (from u in db.workerList join x in db.workerSkillList on u.WorkerId equals x.WorkerId where (WorkerPositionSkill.Where(y => y.workerSkill.WorkerId == u.WorkerId && y.posSkill.PositionId == 1).Count() == db.positionSkillList.Where(p => p.PositionId == 1).Count()) select u).ToList().Distinct();

#2

This is highly unlikely to work with Linq To SQL because...its a huge steaming pile of #$%&. But this Linq query should given a sufficiently magical IQueryProvider give the right SQL. I've seen some very magical things come from Entity Framework.

这在Linq到SQL中是不太可能的，因为……这是一个巨大的蒸汽堆#$%&。但是这个Linq查询应该给一个足够神奇的IQueryProvider提供正确的SQL。我看到一些非常神奇的东西来自实体框架。

var PositionsWithSkills = from a in db.Client_Customer_Position
                          where a.ID == position
                          select new
                          {
                               PositionID = a.ID,
                               RequiredSkills = (from b in db.Client_Customer_Position_Skills
                                                 where b.ClientCusPosId == a.ID
                                                 select b.SkillName)
                          };

var WorkersWithSkills =  from x in db.Workers
                         select new
                         {
                             workerId = x.ID,
                             Skills = (from y in db.Worker_Skills
                                       where y.Worker_ID == x.ID
                                       select y.SkillName)
                         };

var PositionWithSkilledWorkers = from pos in PositionsWithSkills
                                 from worker in WorkersWithSkills
                                 where pos.RequiredSkill.All(worker.Skills.Contains)
                                 group worker.Name by pos.PositionID;

PS please learn to use associations as opposed to join/where. If you are going to use join/where, you might as well just use SQL.

PS请学会使用联想，而不是加入。如果您打算使用连接/在哪里，您最好只使用SQL。

#3

  var PositionsWithSkills = (from a in Positions select new {
      PositionID = a.PositionId,
      RequiredSkills = (from b in PositionSkills where b.PositionId == a.PositionId select b.skillId).ToList()
  }).ToList();

  var WorkersWithSkills = (from x in Workers select new {
      Name = x.Name,
      Skills = (from y in WorkerSkills where y.WorkerId == x.WorkerID select y.skillId).ToList()
  }).ToList();

  var PositionWithSkilledWorkers = (from pos in PositionsWithSkills select new {
      PositionId = pos.PositionID,
      Workers = (from worker in WorkersWithSkills where pos.RequiredSkills.All(skill => worker.Skills.Any(workerSkill => workerSkill == skill)) select worker.Name).ToList()
  }).ToList();

#4

i think, your database's tables have not been designed correctly... you need a relation between worker skill and position skill, i think your tables must be desinged like this:

我认为，您的数据库表设计得不正确……你需要一种员工技能和职位技能之间的关系，我认为你的表格必须像这样做:

Skill table: SkillID, Skill

技能表:SkillID、技能

Position table: PositionID, Position

位置表:PositionID、位置

PositionSkill table: ID, SkillID, PositionID

位置技能表:ID, SkillID, PositionID

Worker table: WorkerID, Name

工作表:WorkerID,名字

WorkerSkill table: ID, SkillID, WorkerID

技能表:ID, SkillID, WorkerID

but by this way you designed your tables, if assume skill field (description of skills) are the same in worker skill and position skill, we can use this as a relation, and your query can be like this:

但是通过这种方式你设计了你的表格，如果假设技能领域(技能描述)在工人技能和职位技能上是相同的，我们可以用它作为一个关系，你的查询可以是这样的:

// skills of specific position
var PositionSkills = Context.PositionSkill.Where(u => u.PositionId == 1);

var WorkersWithSkills = Context.Worker
    .Join(Context.WorkerSkill,
          worker => worker.WorkerId,
          workerSkill => workerSkill.WorkerId,
          (worker, workerSkill) => new { worker, workerSkill })
    .GroupBy(u => u.worker)
    .Select(u => new
    {
       u.Key.WorkerId,
       u.Key.Name,
       Skills = u.Select(t => t.workerSkill.skill)
    });

var SkilledWorkers = WorkersWithSkills
    .Where(u => PositionSkills.All(t => u.Skills.Contains(t.skill)))
    .ToList();

if you wont change your database's tables, you can add a join table between position skill and worker skill like:

如果你不改变你的数据库表，你可以在位置技能和工人技能之间添加一个连接表:

WorkerPositionSkill: PositionSkillID, WorkerSkillID

WorkerPositionSkill:PositionSkillID WorkerSkillID

#5

Here is a LinqPad program that returns the result, { Worker = John, Position = Driver}. If I understand your requirements you want to find a worker who satisfies the conditions where the worker has all the skills required for Position = 1, which is driving and drifting skills. The query returns two rows the following [{worker = John, Position = Driver}, {worker = John, Position = Driver}]. I had to use distinct to display it once. The reason for two rows is he satisfies both driving and drifting job skills. If the position required 4 skills in which the worker met, there would be 4 duplicate rows. The unique fixes that problem. Hope this helps you along.

这里有一个LinqPad程序，它返回结果{Worker = John, Position = Driver}。如果我理解你的要求，你想找一个符合条件的工人，这个工人具备了职位= 1所要求的所有技能，即驾驶和随波逐流技能。查询返回以下两行[{worker = John, Position = Driver}， {worker = John, Position = Driver}]。我必须使用不同的来显示一次。两排的原因是他既能满足驾驶要求，又能胜任随波逐流的工作。如果这个职位需要4项技能，那么需要4行重复。唯一解决了这个问题。希望这能帮到你。

I created this solution in LinqPad, which is great tool with hundred's of very well documented linq query examples.

我在LinqPad中创建了这个解决方案，LinqPad是一个很棒的工具，它包含了上百个文档很好的linq查询示例。

void Main()
{
    // Table Setup
    // ************
    var position = new List<Position>();
    position.Add(new Position { Id = 1, Name = "driver" });
    position.Add(new Position { Id = 2, Name = "clerk" });

    var positionSkill = new List<PositionSkill>();
    positionSkill.Add(new PositionSkill { Id = 1, Skill = "driving", PositionId = 1 });
    positionSkill.Add(new PositionSkill { Id = 2, Skill = "drifting", PositionId = 1 });

    var worker = new List<Worker>();
    worker.Add(new Worker { Id = 1, Name = "John" });
    worker.Add(new Worker { Id = 2, Name = "alex" });

    var workerSkill = new List<WorkerSkill>();
    workerSkill.Add(new WorkerSkill { Id = 1, Skill = "driving", WorkerId = 1 });
    workerSkill.Add(new WorkerSkill { Id = 2, Skill = "drifting", WorkerId = 1 });  

    // The Query
    // *********
    var positionValue = 1;

        var r = from p in position
                join ps in positionSkill on p.Id equals ps.PositionId
                join ws in workerSkill on ps.Skill equals ws.Skill
                join w in worker on ws.WorkerId equals w.Id
                where p.Id == positionValue
                select new {
                    PositionName = p.Name,
                    WorkerName = w.Name
                };

    // Get Distinct Names           
    r.Distinct().Dump();


}

// Define other methods and classes here
    public class Position
    {
        public int Id { get; set; }
        public string Name { get; set; }

    }


    public class PositionSkill
    {
        public int Id { get; set; }
        public string Skill { get; set; }
        public int PositionId { get; set; }

    }



    public class Worker
    {
        public int Id { get; set; }
        public string Name { get; set; }

    }


    public class WorkerSkill
    {
        public int Id { get; set; }
        public string Skill { get; set; }
        public int WorkerId { get; set; }

    }