join table to table related to the parent table-CodePudding

Still new to LINQ so be nice.

This query:

var query = _ODSContext.AllFacilities
.Where(f => f.AllFacilityContacts.Any(c => ProviderContactIds.Contains(c.ContactID) && 
(c.ContactTypeName == "Primary Rep")))
.Where(f => f.TermDate > DateTime.Now)
.Include(a => a.Address)
.Include(b => b.AllFacilityContacts)
.Include(c => c.AllPractitionerLocations)
.Include(e => e.AllFacilityServices)
.OrderBy(f => f.FacilityName);

works fine.

However, I want to add a table that joins to AllpractitionerLocations:

Here's what I tried:

var query = _ODSContext.AllFacilities
.Where(f => f.AllFacilityContacts.Any(c => ProviderContactIds.Contains(c.ContactID) && 
(c.ContactTypeName == "Primary Rep")))
.Where(f => f.TermDate > DateTime.Now)
.Include(a => a.Address)
.Include(b => b.AllFacilityContacts)
.Include(c => c.AllPractitionerLocations)
.Include(d => d.AllPractitionerNetworkSpecialty)
.Include(e => e.AllFacilityServices)
.OrderBy(f => f.FacilityName);

But I get:

'AllFacility' does not contain a definition for 
'AllPractitionerNetworkSpecialties' and no accessible extension method 
'AllPractitionerNetworkSpecialties' accepting a first argument of type 
'AllFacility' could be found (are you missing a using directive or an assembly reference?

Which is technically accurate. AllPractitionerNetworkSpecialties is related to AllPractitionerLocations.

How do I do the join between those two tables in LINQ?

Thanks,

CodePudding user response：

You use ThenInclude

Simplistically, Include starts off from the root entity, ThenInclude carries on from the entity type that you call it on. If you conceive your graph as a hub-spoke type affair with AllFacilities as the hub, your Include starts another spoke from the hub, whereas ThenInclude continues an existing spoke. If a customer has orders and orders have products, you'd context.Customer.Include(... Orders).ThenInclude(... Products). If you wanted to "go back to the hub" and get the Customer>Address>Country>TaxYearCodings you'd

context.Customer
  .Include(... Orders)
    .ThenInclude(... Products)
  .Include(... Address)
    .ThenInclude(... Country)
      .ThenInclude(... TaxYearCodings)

We (at work) tend to indent another level when we ThenInclude to signify "continuing a spoke", and indent all the Includes the same to signify "going back to the hub".

Possibly also worth pointing out that you can chain your Include and ThenInclude by accessing the chain "in a one-er" but you can't navigate into collections, only single props - you start another inclusion when you hit a collection

customer.Include(c => c.Address.Country.TaxYearCodings).ThenInclude(tyc => ...)

Be careful; the amount of data you load when you start (then)including a lot can be enormous