c# - 使用 Entity Framework 加载所有子实体

Question

我有这样一个数据模型

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

目前我能找到的将所有相关实体加载到单个 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

我也尝试用 Include 和 Select 加载它，但我得到了这个异常:

包含路径表达式必须引用在类型上定义的导航属性。将点路径用于引用导航属性，将 Select 运算符用于集合导航属性。

我不知道如何使用 Include 和 Select

加载 JrnlEntry 的每个子项

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))

编辑

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

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))

Answer 1

在Entity Framework中有两种方式来执行Eager Loading:

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

也有针对数据库写Raw SQL Queries的方式:

• DbSet.SqlQuery Method 处理实体
• Database.SqlQuery Method 处理任意类型
• Database.ExecuteSqlCommand Method 用于任意 DDL/DML

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