我有4个表：
Position表：
|职位|PositionId||驱动程序|1||职员|2|
position Skill表：
|SkillId|skill|PositionId||1|驾驶|1||2|漂移|1|
Worker表：
|姓名|工作ID ||约翰|1||亚历克斯|2|
Worker skill表：
|skillId|skill|WorkerId||1|驾驶|1||2|漂移|1|
I join position表与position Skill表和带有worker skill 的worker表
我遇到的问题是如何比较两个joined表以获得的结果
例如：
我需要知道谁的worker拥有该职位拥有的所有特定技能
类似：
I选择positionId为1的位置，并具有driving和drifting的skillname我需要得到具有相同skills的Worker，driving和drifting也是
到目前为止，我得到了这个：
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();
两个CCD_ 20工作良好。。但是我必须比较两个查询的最后一个查询=。。我拿不到worker id我能把它变成stored proc吗？
如果我错了，很抱歉。我从你的问题中了解到的是，你希望员工名单满足你通过的职位的所有技能。如果这是你想要的，你可以试试这个：
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();
或者，如果你想使用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();
为了获得更多的理解，你可以尝试以下代码：
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; }
}
}
如果一个工人有不同职位的技能，上面的代码将不起作用，如果是这种情况，请尝试下面的代码：
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();
这不太可能与Linq to 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请学会使用关联，而不是加入/在哪里。如果您要使用join/where，那么您也可以只使用SQL。
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();
我认为，您的数据库表设计不正确。。。你需要worker skill和position skill之间的关系，我认为你的表必须这样设计：
技能表：技能ID，技能
位置表：PositionID，Position
PositionSkill表：ID、SkillID、PositionID
Worker表：WorkerID，名称
WorkerSkill表：ID，SkillID，WorkerID
但通过这种方式，您设计了表格，如果假设skill字段（技能描述）在worker skill和position skill中相同，我们可以将其用作关系，您的查询可以是这样的：
// 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();
如果不更改数据库的表，可以在position skill和worker skill之间添加一个join table，如
工作岗位技能：岗位技能ID，工作岗位技能ID
这里有一个LinqPad程序，它返回结果｛Worker=John，Position=Driver｝。如果我理解你的要求，你想找到一名满足条件的工人，该工人具备职位=1所需的所有技能，即驾驶和漂移技能。查询返回以下两行[｛worker=John，Position=Driver｝，｛worker=John，Position=Driver｝]。我不得不使用distinct来显示它一次。之所以排成两排，是因为他同时满足驾驶和漂移的工作技能。如果该职位需要工人所具备的4项技能，则会有4行重复。独特的解决了这个问题。希望这能帮助你。
我在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; }
}