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.