Linq to Sql,使用一个查询检索嵌套的数据结构 [英] Linq to Sql, retrieving nested data structures with one query
问题描述
我不确定是否已经找到答案,因为我无法确定Linq中的哪种机制适用于这种情况,或者我是否需要手动完成.
I'm not sure if an answer for this already exists, as I can't figure out what mechanism in Linq is meant for this situation, or if I just need to do it manually.
假设我有2张桌子:
------------Employees-------------
EmployeeID Name -other columns-
---------EmployeeSkills-----------
EmployeeID Skill -other columns-
因此,每个员工可以拥有0个或更多技能.
So each employee can have 0 or more skills.
我的目标是使用一个sql查询将这些信息绘制到内存中的数据结构中
My goal is to draw this information into a data structure in memory, using one sql query
class StaticEmployee
{
int EmployeeID;
string Name;
List<string> Skills;
}
List<StaticEmployee> employees = (???).ToList();
由于已经建立了表关系,所以我只能为每个雇员表(从db中的雇员中选择雇员),但是当我访问EmployeeSkills属性时,它将执行一个单独的查询每条员工记录,因为第一个查询未返回该数据.
Since my table relations are setup, I could just foreach the employees table (from employee in db.Employees select employee), however when I access the EmployeeSkills property, it is going to execute a seperate query for every employee record since that data wasn't returned with the first query.
或者,就Sql而言,我想做的是运行带有左联接的查询:
Alternatively, and I what I want done so far as Sql is concerned, is run a query with a left join:
SELECT Employees.EmployeeID, Employees.Name, EmployeeSkills.Skill FROM Employees LEFT JOIN EmployeeSkills ON Employees.EmployeeID=EmployeeSkills.EmployeeID
那将获得我的列表,但是我需要手动将其整理到我的列表中,因为具有2个技能的员工将返回2行. 是否有执行此操作的Linq操作? 下面的假设示例
That will get me my list, but I'll need to manually collate it into my list since an employee with 2 skills will return 2 rows. Is there a Linq operation that does this? Hypothetical example below
from employee in db.Employees
select new
{
EmployeeID = employee.EmployeeID,
Name = employee.Name,
Skills = new List(
from employeeSkill in employee.EmployeeSkills
select employeeSkill.skill
)
}
推荐答案
var options = new DataLoadOptions();
options.LoadWith< Employees>(e => e.EmployeeSkills);
context.LoadOptions = options;
//retrieve employees, they will come loaded with EmployeeSkills
如果要改用linq查询版本,这也会加载一次:
If you were to use the linq query version instead, this will load them once as well:
from employee in db.Employees
select new
{
EmployeeID = employee.EmployeeID,
Name = employee.Name,
Skills = employee.EmployeeSkills
}
这篇关于Linq to Sql,使用一个查询检索嵌套的数据结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!