[C#] How to Retrieve Related Table Data Efficiently with Entity Framework Core

Overview

This implementation shows how to use Entity Framework Core (EF Core) to fetch main data (parent) and its related data (child) in a single query. By using the Include method, you can reduce database round-trips and avoid the “N+1 problem” while loading related information.

Specifications (Input/Output)

• Input: Department name to search (string).
• Output: Displays department details and a list of employees in that department to the console.
• Prerequisites: .NET 6.0 or higher, Microsoft.EntityFrameworkCore.InMemory (for testing).

Basic Usage

Use the Include extension method from the Microsoft.EntityFrameworkCore namespace.

// Fetch the "Sales" department and load related Employees at the same time
var department = await context.Departments
    .Include(d => d.Employees) // Specify the join here
    .FirstOrDefaultAsync(d => d.Name == "Sales");

// Access the Employees property after data access
foreach (var emp in department.Employees)
{
    Console.WriteLine(emp.Name);
}

Full Code Example

This code assumes a one-to-many relationship between “Department” and “Employee”. It uses an in-memory database for testing, so you can copy and run it immediately. You will need the Microsoft.EntityFrameworkCore.InMemory package.

dotnet add package Microsoft.EntityFrameworkCore.InMemory

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

public class Program
{
    public static async Task Main()
    {
        // Database setup (In-memory)
        var options = new DbContextOptionsBuilder<CompanyDbContext>()
            .UseInMemoryDatabase(databaseName: "CompanyDb")
            .Options;

        // Data seeding (Adding initial data)
        using (var context = new CompanyDbContext(options))
        {
            if (!await context.Departments.AnyAsync())
            {
                var dept = new Department { Name = "Development" };
                context.Departments.Add(dept);
                
                context.Employees.AddRange(
                    new Employee { Name = "Alice", Role = "Engineer", Department = dept },
                    new Employee { Name = "Bob", Role = "Designer", Department = dept },
                    new Employee { Name = "Charlie", Role = "Manager", Department = dept }
                );
                await context.SaveChangesAsync();
            }
        }

        // Retrieve and display data
        using (var context = new CompanyDbContext(options))
        {
            var worker = new CompanyDataWorker(context);
            await worker.ShowDepartmentDetailsAsync("Development");
        }
    }
}

// Business logic class
public class CompanyDataWorker
{
    private readonly CompanyDbContext _context;

    public CompanyDataWorker(CompanyDbContext context)
    {
        _context = context;
    }

    public async Task ShowDepartmentDetailsAsync(string deptName)
    {
        // Use Include for Eager Loading of related data
        var department = await _context.Departments
            .Include(d => d.Employees)
            .SingleOrDefaultAsync(d => d.Name == deptName);

        if (department == null)
        {
            Console.WriteLine($"Department '{deptName}' was not found.");
            return;
        }

        Console.WriteLine($"Department: {department.Name}");
        Console.WriteLine("----------------------------");

        foreach (var employee in department.Employees)
        {
            Console.WriteLine($"- {employee.Name} ({employee.Role})");
        }
    }
}

// Entity definitions
public class Department
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    // Navigation property
    public List<Employee> Employees { get; set; } = new();
}

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Role { get; set; } = string.Empty;
    
    public int DepartmentId { get; set; }
    public Department? Department { get; set; }
}

// DbContext definition
public class CompanyDbContext : DbContext
{
    public CompanyDbContext(DbContextOptions<CompanyDbContext> options)
        : base(options) { }

    public DbSet<Department> Departments => Set<Department>();
    public DbSet<Employee> Employees => Set<Employee>();
}

Example Output

Department: Development
----------------------------
- Alice (Engineer)
- Bob (Designer)
- Charlie (Manager)

Customization Points

• Filtered Loading: You can filter related data by using Include(d => d.Employees.Where(e => e.Role == "Engineer")) (supported in EF Core 5.0 and later).
• Loading Nested Data: To load related data of related data (grandchildren), use .Include(...).ThenInclude(...).
• Read-Only Operations: If you only need to display data and not update it, adding .AsNoTracking() improves performance.

Important Notes

• Preventing the N+1 Problem: If you forget to use Include and try to access related data (like department.Employees) inside a foreach loop, it may return empty data or cause an error if Lazy Loading is disabled. Even if Lazy Loading is enabled, it will execute a SQL query for every loop iteration, slowing down performance.
• Cartesian Explosion: Using Include on multiple collections (e.g., Employees, Projects, Assets) can drastically increase the amount of data returned due to joins. Only include necessary related data.
• Null Checks: SingleOrDefaultAsync returns null if no data is found. Always perform a null check before accessing the results.

Advanced Application

Example of retrieving nested data (deeper levels in the hierarchy).

// Example: Department -> Employees -> Employee's assigned PC information
var data = await _context.Departments
    .Include(d => d.Employees)
        .ThenInclude(e => e.PcAssignment) // Get PC info related to Employee
    .FirstOrDefaultAsync(d => d.Id == 1);

Conclusion

Use ThenInclude when the hierarchy becomes deeper. When handling parent-child data, the Include method keeps the number of SQL queries to just one. By explicitly loading only the necessary related data, you can maintain application performance and data integrity.