Nested fields in Entity Framework Core: can I fetch everything in one query?-CodePudding

I'm trying to efficiently manipulate complex records with multiple levels of nested fields in C# / ASP.NET Core / Entity Framework Core.

I created a small test app with EF models "Departments > Courses > CourseAuditors".

Here's my query:

    public void OnGet()
    {
        Departments = ctx.Departments.ToList();
        foreach (var department in Departments)
        {
            department.Courses = ctx.Courses
                .Where(c => c.DepartmentID == department.ID)
                .ToList();
            foreach (var course in department.Courses)
            {
                course.CourseAuditors = ctx.CourseAuditors
                    .Where(c => c.CourseID == course.ID)
                    .ToList();
            }
        }
    }

Q: Is there any way I can get rid of the loops, and read everything in one query?

Here are the models:

Department.cs

public class Department
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string DepartmentHead { get; set; }

    public virtual ICollection<Course> Courses { get; set; }
}

Course.cs

public class Course
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Instructor { get; set; }
    public string Location { get; set; }
    public int DepartmentID { get; set; }
    public virtual ICollection<CourseAuditor> CourseAuditors { get; set; }
}

CourseAuditor.cs

public class CourseAuditor
{
    public int ID { get; set; }
    public string StudentName { get; set; }
    public int CourseID { get; set; }
}

Our current platform is

TargetFramework=.net5.0;
EntityFrameworkCore=5.0.6 (we'd like to migrate to .NET 6.x soon).

My primary concern is SQL-level efficiency (the fewer SQL-level queries/round trips the better!).

Any advice/suggestions would be welcome!

CodePudding user response：

First of all you should extend your models with proper relations:

public class CourseAuditor
{
    public int ID { get; set; }
    public string StudentName { get; set; }
    public int CourseID { get; set; }
    public Course Course { get; set; } // this property is missing
}

public class Course
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Instructor { get; set; }
    public string Location { get; set; }
    public int DepartmentID { get; set; }
    public Department Department { get; set; } // this property is missing
    public virtual ICollection<CourseAuditor> CourseAuditors { get; set; }
}

Then in your DbContext method OnModelCreating(ModelBuilder modelBuilder) you should create relations like so:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Course>()
        .HasMany(c => c.CourseAuditors)
        .WithOne(ca => ca.Course)
        .HasForeignKey(ca => ca.CourseID);
    modelBuilder.Entity<Department>()
        .HasMany(d => d.Courses)
        .WithOne(c => c.Department)
        .HasForeignKey(d => d.DepartmentID);
}

After this setup you can now use SQL Join feature - .Include(...) in Entity Framework.

Changed method OnGet():

public void OnGet()
{
    Departments = ctx.Departments
        .Include(d => d.Courses)
        .ThenInclude(c => c.CourseAuditors)
        .ToList();
}

Lastly I would suggest changing naming of properties.

DepartmentID -> DepartmentId
ID -> Id
etc.

There's the convention in C# to not capitalize full texts.