猿问
下载APP

实体框架linq查询Include()多个子实体

这可能是一个非常重要的问题,但是当编写跨越三个级别(或更多)的查询时,包含多个子实体的好方法是什么?


即我有4个表:Company,Employee,Employee_Car和Employee_Country


公司与员工有1:m的关系。


Employee与Employee_Car和Employee_Country的关系为1:m。


如果我想编写一个返回所有4个表中数据的查询,我目前正在编写:


Company company = context.Companies

                         .Include("Employee.Employee_Car")

                         .Include("Employee.Employee_Country")

                         .FirstOrDefault(c => c.Id == companyID);

必须有一个更优雅的方式!这是漫长的缠绕,并产生可怕的SQL


我在VS 2010中使用EF4


慕哥9229398
浏览 55回答 3
3回答

慕容3067478

使用扩展方法。将NameOfContext替换为对象上下文的名称。public static class Extensions{&nbsp; &nbsp;public static IQueryable<Company> CompleteCompanies(this NameOfContext context){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;return context.Companies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include("Employee.Employee_Car")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include("Employee.Employee_Country") ;&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp;public static Company CompanyById(this NameOfContext context, int companyID){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;return context.Companies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include("Employee.Employee_Car")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include("Employee.Employee_Country")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.FirstOrDefault(c => c.Id == companyID) ;&nbsp; &nbsp; &nbsp; }}然后你的代码变成了&nbsp; &nbsp; &nbsp;Company company =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; context.CompleteCompanies().FirstOrDefault(c => c.Id == companyID);&nbsp; &nbsp; &nbsp;//or if you want even more&nbsp; &nbsp; &nbsp;Company company =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; context.CompanyById(companyID);

慕仰8121524

EF 4.1至EF 6有一个强类型.Include,它允许通过将Select表达式提供到适当的深度来指定所需的预先加载深度:using System.Data.Entity; // NB!var company = context.Companies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include(co => co.Employees.Select(emp => emp.Employee_Car))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include(co => co.Employees.Select(emp => emp.Employee_Country))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.FirstOrDefault(co => co.companyID == companyID);在两个实例中生成的Sql仍然不是直观的,但似乎足够高效。我在这里给GitHub写了一个小例子EF核心EF Core有一个新的扩展方法.ThenInclude(),虽然语法略有不同:var company = context.Companies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include(co => co.Employees)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.ThenInclude(emp => emp.Employee_Car)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ...根据文档,我会保留额外的“缩进” .ThenInclude以保持你的理智。过时的信息(不要这样做):多个孙子加载可以在一个步骤中完成,但这需要在向下移动下一个节点之前备份相当笨拙的反转(NB:这不起作用AsNoTracking()- 您将得到运行时错误):var company = context.Companies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.Include(co =>&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;co.Employees&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(emp => emp.Employee_Car&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(ec => ec.Employee)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(emp2 => emp2.Employee_Country)))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.FirstOrDefault(co => co.companyID == companyID);所以我会继续使用第一个选项(每个实体深度模型包含一个)。

胡子哥哥

您可能会在codeplex.com上找到这篇感兴趣的文章。使用基于图的查询提高实体框架查询性能。本文介绍了一种表达查询的新方法,这些查询以声明图形状的形式跨越多个表。此外,本文还包含对这种新方法与EF查询的全面性能比较。该分析表明GBQ快速优于EF查询。
打开App,查看更多内容
随时随地看视频慕课网APP
我要回答