.Include() .Where() in Linq to Entities query

Question

I'm trying to find all active patients, those with EndOfTreatment == null.

The problem is that the relationship has a complex structure.

I'm not so good with these database diagram things, but I've made the following pictue, I think you will get the point:

My attempt so far:

var ans = ctx
    .PatientMap
    .Include(p => p.Doctor)
    .Include(p => p.Product)
    .Include(p => p.Institution)
    .Include(p => p.Doctor.TerritoryDoctorPanel
        .Where(dp =>
            (dp.Territory.PromotionalLine == p.Product.PromotionalLine) &&  // issuing
            (dp.Territory.Active == true)                                   // lines
        )
    )
    .Where(p =>
        (IDProduct == null || p.IDProduct == IDProduct.Value) &&
        (p.EndOfTreatment == null)
    )
    .ToList()
    .Select(p => new ActivePatientModel
    {
        IDPatient = p.ID,
        Observation = p.Observation,
        TreatmentPeriod = DateTimeSpan.CompareDates(
            (DateTime)p.StartOfTreatment, DateTime.Now
        ).Months,
        NameDoctor = p.Doctor.FullName,
        CodeDoctor = p.Doctor.Code,
        CodeInstitution = p.Institution.Code,
    })
    .ToList();

I searched a lot and the closest I got was this answer from Moho, which adapted would look like:

.SelectMany(p => p.Doctor.TerritoryDoctorPanel)
    .Where(dp => dp.Territory.PromotionalLine == /*p.Product.PromotionalLine*/)
                                                   ^^^^^^^^^^
                                                // How can I reference p.Product here?

---

Resume:

• I need to get Patients treated by Doctors using some Product working in Territories. The relationship exists by Product.PromotionalLine = Territory.PromotionalLine.
• IDProduct is of type int?, thus: (IDProduct == null || p.IDProduct == IDProduct.Value)

I'm really out of ideas how to make it work.

I appreciate any suggestion.

Answer 1

I will try something, I hope I got your idea clearly though I am not sure I did.

so I guess your question is this

I need to get Patients treated by Doctors using some Product working in Territories. The relationship exists

and here is how i would do it.

var ans = ctx
    .PatientMap
    .Include(p => p.Doctor)
    .Include(p => p.Product)
    .Include(p => p.Institution)
    .Include(p => p.Doctor.TerritoryDoctorPanel
    .Where(p => 
        // some doctors, doctorIDs is list of all doctors id you want in case you are using id retrieval 
       doctorIDs.Contains(p.DoctorID) &&
       //working in some territory 
       //similar to this, you can filter any doctor Attribute 
       p.Doctor.TerritoryDoctorPanel.Any(t => /*Add condition for TerritoryDoctorPanel here */) &&
      (p.IDProduct == null || p.IDProduct == IDProduct.Value) &&
      (p.EndOfTreatment == null) && 
       // Product Promotion line conditions
       // also similar to this you can filter any product attribute 
      (p.Product.PromotionalLine.Any(pl => /*Add condition for promotional lines here*/)))             
    .ToList()