8,497
社区成员
发帖
与我相关
我的任务
分享
var query= from e in Employee
select new
{
Name=e.Name,
Age=e.Age,
EmpHobby=string.Join(",",e.EmpHobbies.Select(s=>s.HobbyName)),
Skill=string.Join(",",e.EmpSkills.Select(s=>s.SkillName))
};
INSERT INTO Employee SELECT 'Tim',30,'China','10086' UNION ALL
SELECT 'Jim',22,'China','10086' UNION ALL
SELECT 'Tom',25,'China','10086' UNION ALL
SELECT 'Jack',38,'China','10086'
INSERT INTO Skill SELECT 'C#' UNION ALL
SELECT 'java' UNION ALL
SELECT 'JS' UNION ALL
SELECT 'CSS' UNION ALL
SELECT 'LINQ'
INSERT INTO EmpSkill SELECT 1,1 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,5 UNION ALL
SELECT 2,2
INSERT INTO Hobby SELECT '篮球' UNION ALL
SELECT 'dotnet' UNION ALL
SELECT 'photoshop' UNION ALL
SELECT '3d'
INSERT INTO EmpHobby SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 4,1 UNION ALL
SELECT 4,2
void Main()
{
var query=from e in Employee
select new
{
Name=e.Name,
Age=e.Age,
EmpHobby=string.Join(",", (from emp in Employee
join emh in EmpHobbies on emp.EmployeeID equals emh.EmployeeID into temp
from emh in temp.DefaultIfEmpty()
join h in Hobbies on emh.HobbyID equals h.HobbyID
where emp.EmployeeID==e.EmployeeID
select h.HobbyName).ToArray()),
Skill=string.Join(",", (from emp in Employee
join ems in EmpSkills on emp.EmployeeID equals ems.EmployeeID into temp
from ems in temp.DefaultIfEmpty()
join s in Skills on ems.SkillID equals s.SkillID
where emp.EmployeeID==e.EmployeeID
select s.SkillName).ToArray())
};
query.ToList().ForEach(q=>Console.WriteLine("{0}\t{1}\t{2}\t{3}\t",q.Name,q.Age,q.EmpHobby,q.Skill));
/*
Tim 30 篮球,dotnet C#,JS,LINQ
Jim 22 photoshop,3d java
Tom 25
Jack 38 篮球,dotnet
*/
}
var query = from e in ctx.员工
let skills = (from s in ctx.员工技能 select new { s.employeeID, s.技能分类.skillName })
let hobbies = (from h in ctx.员工业余爱好 select new { h.employeeID, h.业余爱好分类.hobbyName })
select new
{
e.employeeID,
e.name,
ss = skills.Where(x => x.employeeID == e.employeeID).Select(x => x.skillName),
hs = hobbies.Where(x => x.employeeID == e.employeeID).Select(x => x.hobbyName),
};
foreach (var q in query)
{
Console.WriteLine("{0}\t{1}\t{2}", q.employeeID, string.Join(",", q.ss), string.Join(",", q.hs));
}
var query=from e in Employee
join ek in EmpSkills on e.EmployeeID equals ek.EmployeeID into EmpSkill
from es in EmpSkills.DefaultIfEmpty()
group es by es.EmployeeID into g
let emp=Employee.SingleOrDefault(emp=>emp.EmployeeID==g.Key)
let skillIDs=g.Select(m=>m.SkillID)
select new
{
Name=emp.Name,
Age=emp.Age,
Skill=string.Join(",",Skills.Where(sk=>skillIDs.Contains(sk.SkillID)).Select(sk=>sk.SkillName).ToArray()),
};