Loading all the children entities with entity framework

Question

I have a data model like this

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

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 Reconciliation object. If possible in an elegant way.

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 Include and Select but I get this exception :

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.

And I don't get how I could load every childs of JrnlEntry using Include and 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))

Answer 1

There are two ways to perform Eager Loading in Entity Framework:

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

There are also manners to write Raw SQL Queries against database:

• DbSet.SqlQuery Method deals with entities
• Database.SqlQuery Method deals with arbitrary types
• Database.ExecuteSqlCommand Method for arbitrary DDL/DML

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