C# 使用实体框架加载所有子实体

Loading all the children entities with entity framework

提问by Arno 2501

I have a data model like this

我有一个这样的数据模型

I would like to load all the related entities from a Reconciliation into a Reconciliation object.

我想将 Reconciliation 中的所有相关实体加载到 Reconciliation 对象中。

For now the only way I could find to load all the related entites to a single Recon is in multiple Lists. But I want to load every related entities in a Reconciliationobject.If possible in an elegant way.

目前，我能找到将所有相关实体加载到单个 Recon 的唯一方法是在多个列表中。但我想加载Reconciliation对象中的每个相关实体。如果可能，以优雅的方式。

Reconciliation recon = db.Reconciliations
  .Where(r => r.ReconNum == 382485).First();

List<ReconciliationDetail> reconDetails = recon.ReconciliationDetails.ToList();
List<JrnlEntryDetail> jrnlDetails = reconDetails.Select(r => r.JrnlEntryDetail).ToList();
List<JrnlEntry> jrnl = jrnlDetails.Select(j => j.JrnlEntry).ToList();

List<ARInvoice> invoices = jrnl.SelectMany(j => j.ARInvoices).ToList();
List<ARInvoiceDetail> invoicesDetail = invoices
  .SelectMany(i => i.ARInvoiceDetails).ToList();

List<ARCredMemo> credmemos = jrnl.SelectMany(j => j.ARCredMemoes).ToList();
List<ARCredMemoDetail> credmemosDetail = credmemos
  .SelectMany(c => c.ARCredMemoDetails).ToList();

List<IncomingPay> incomingPays = jrnl.SelectMany(j => j.IncomingPays).ToList();
List<IncomingPayDetail> incomingPaysDetail = incomingPays
  .SelectMany(i => i.IncomingPayDetails).ToList();

// ... and so on for outgoing pays, AP Invoices AP Cred Memo ...etc

I have also tried to load it with Includeand Selectbut I get this exception :

我也试图与加载它Include和Select，但我得到这个异常：

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.

Include 路径表达式必须引用类型上定义的导航属性。对参考导航属性使用虚线路径，对集合导航属性使用 Select 运算符。

And I don't get how I could load every childs of JrnlEntry using Includeand Select

而且我不明白如何使用Include和加载 JrnlEntry 的每个孩子Select

Reconciliation recon = db.Reconciliations
  .Where(r => r.ReconNum == 382485)
  .Include(r => r.ReconciliationDetails
    .Select(d => d.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry)
    .SelectMany(j => j.ARInvoices).SelectMany(i => i.ARInvoiceDetails))

Edit

编辑

Managed to do it this way too but it's not very beautiful :

也设法这样做，但它不是很漂亮：

Reconciliation recon = db.Reconciliations
.Where(r => r.ReconNum == 382485)
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.ARInvoices.Select(i => i.ARInvoiceDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.ARCredMemoes.Select(c => c.ARCredMemoDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.IncomingPays.Select(i => i.IncomingPayDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.OutgoingPays.Select(o => o.OutgoingPayDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.APInvoices.Select(o => o.APInvoiceDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.APCredMemoes.Select(o => o.APCredMemoDetails)))
  .Include(r => r.ReconciliationDetails.Select(rd => rd.JrnlEntryDetail)
    .Select(jd => jd.JrnlEntry).Select(j => j.JrnlEntryDetails))

回答by Olly

Try with just .Include(r => r.ReconciliationDetails)initially. Then add the .Select()statements one-by-one. At what point does the exception reappear? The .SelectMany()call looks a bit suspicious to me!

刚.Include(r => r.ReconciliationDetails)开始尝试。然后将.Select()语句一一添加。异常在什么时候再次出现？这个.SelectMany()电话在我看来有点可疑！

A second question that might help identify the problem... After you run the code that contains all the ToList()calls, is your reconentity complete? i.e. are all its navigation properties populated? This shouldbe the case because of the automatic 'fixup' behavior of Entity Framework.

可能有助于确定问题的第二个问题...在您运行包含所有ToList()调用的代码后，您的recon实体是否完整？即是否填充了所有导航属性？这应该是因为实体框架的自动“修正”的行为的情况。

With EF, sometimes it is more efficient to load a complex object graph with several calls rather than chained Include()calls. Check the generated SQL and see what is most efficient in your case.

使用 EF，有时通过多次调用而不是链接Include()调用来加载复杂的对象图会更有效。检查生成的 SQL，看看在您的情况下什么是最有效的。

回答by Ryszard D?egan

There are two ways to perform Eager Loadingin Entity Framework:

在 Entity Framework 中执行Eager Loading有两种方法：

• ObjectQuery.Include Method
• Explicit loading using either DbEntityEntry.Collection Methodor DbEntityEntry.Reference Methodalong with their respective Load methods

• ObjectQuery.Include 方法
• 使用DbEntityEntry.Collection 方法或DbEntityEntry.Reference 方法及其各自的 Load 方法进行显式加载

There are also manners to write Raw SQL Queriesagainst database:

还有一些方法可以针对数据库编写原始 SQL 查询：

• DbSet.SqlQuery Methoddeals with entities
• Database.SqlQuery Methoddeals with arbitrary types
• Database.ExecuteSqlCommand Methodfor arbitrary DDL/DML

• DbSet.SqlQuery 方法处理实体
• Database.SqlQuery 方法处理任意类型
• 任意 DDL/DML 的Database.ExecuteSqlCommand 方法

For the case, when you're attempting to load nearly entire database, it would be good idea to execute dedicated store procedure against it.

对于这种情况，当您尝试加载几乎整个数据库时，最好针对它执行专用存储过程。

回答by JQII

Not sure if it's to late but could you benefit from structuring your code such as

不确定是否为时已晚，但您是否可以从构建代码中受益，例如

var acctName = "someName";

var detailList =  _repository.Include(e => e.JrnlEntryDetail).Filter(c => c.JrnlEntryDetail.Any(e => e.AcctName == acctName)).Get().ToList();