EF Core: Load related tables in query vs LoadAsync-CodePudding

In EF Core, suppose my database model contains a Patient entity with related collections like this:

public class Patient
{
    // Patient properties
    public int Id { get; set; }
    public string Name { get; set; }

    // Navigation properties
    public List<Allergy> Allergies { get; set; }
    public List<Medication> Medications { get; set; }
    public List<Symptom> Symptoms { get; set; }
    etc.
}

public class Allergy
{
    public int Id { get; set; }
    <more properties>

    // Navigation properties
    public int PatientId { get; set; }    // FK to the related Patient
    public Patient Patient { get; set; }
}

In my actual code, I have 8 of these types of relations. If I fetch a Patient and its related collections via LINQ, EF will create a monster query with 8 joins:

var qry = from patient in db.Patients
            .Include(p => p.Allergies)
            .Include(p => p.Medications)
            .Include(p => p.Symptoms)
            .Include(<etc.>)
          where patient.Id = <desired Id>
          select patient;
var Patient = await qry.FirstOrDefaultAsync();

My other option is to get the Patient without loading the related collections, then make 8 trips to the database to load the related collections:

var qry = from patient in db.Patients
          where patient.Id = <desired Id>
          select patient;
var Patient = await qry.FirstOrDefaultAsync();

await db.Entry(Patient).Collection(p => p.Allergies).LoadAsync();
await db.Entry(Patient).Collection(p => p.Medications).LoadAsync();
await db.Entry(Patient).Collection(p => p.Symptoms).LoadAsync();
etc.

Which strategy is more efficient (faster to execute)? And is there a "happy middle ground" where I load the basic entity with a simple SQL query, then make only a single trip to the database to load all of the desired related collections?

(I've found SO "answers" like this one, that rely on current EF behaviors such as "This works, because the context will detect that entity you are looking for is loaded and attached already and therefore not create a new one but rather update the old one". My problem with those types of solutions is that they may stop working when the EF implementation details change.)

CodePudding user response：

You can use Split Queries in EF Core like this:

Just call .AsSplitQuery() at the end of the .Include() chain.

EF allows you to specify that a given LINQ query should be split into multiple SQL queries. Instead of JOINs, split queries generate an additional SQL query for each included collection navigation.

var qry =
    from patient in db.Patients
        .Include(p => p.Allergies)
        .Include(p => p.Medications)
        .Include(p => p.Symptoms)
        .Include(/* etc. */)
        .AsSplitQuery() // <-- add this method call
    where patient.Id = <desired Id>
    select patient;