如何正确显式加载Entity Framework导航属性列表？

请注意：使用AsSplitQuery()无法解决此问题，下文会说明原因

我定义了三个实体类：

public class Organization
{
   // 主键
   public int OrganizationId { get; set; }

   // 导航属性
   public virtual List<Department> Departments { get; set; }

   // ... 其他大量属性
}

public class Department 
{
   // 主键
   public int OrganizationId { get; set; }
   public int DepartmentId { get; set; }

   // 导航属性
   public virtual List<Employee> Employees { get; set; }

   // ... 其他大量属性
}

public class Employee
{
   // 主键
   public int OrganizationId { get; set; }
   public int DepartmentId { get; set; }
   public int EmployeeId { get; set; }

   // ... 其他大量属性
}

我已经将这个类层次结构映射到Entity Framework。加载Organization时，我知道可以用预加载导航属性的方式：

public Organization GetOrganization(int organizationId)
{
   return context.Organizations.Where(p => p.OrganizationId == organizationId)
      .Include(n => n.Departments).ThenInclude(n => n.Employees);
}

但我不想这么做，因为这会生成如下SQL语句：

SELECT Organizations.*
       Departments.*
       Employees.*
FROM Organizations
     JOIN Departments
        ON  Departments.OrganizationId = Organizations.OrganizationId 
     JOIN Employees
        ON  Employees.OrganizationId = Departments.OrganizationId 
        AND Employees.DepartmentId = Departments.DepartmentId 
WHERE Organizations.OrganizationId = [Organization ID]

问题在于每个表的行数据量很大，查询结果里每一行都包含Organization和Department的全部列——这些都是重复数据，所以加载数据的耗时比执行以下三个独立查询要长：

SELECT * FROM Organizations WHERE OrganizationId = [Organization ID]
SELECT * FROM Departments WHERE OrganizationId = [Organization ID]
SELECT * FROM Employees WHERE OrganizationId = [Organization ID]

我不想用延迟加载，因为EF会逐行发起查询，性能更差。

使用AsSplitQuery()也没法彻底解决问题：虽然返回的数据量减少了，但EF会在每个子查询里把子表和所有父表关联，导致查询速度不如预期，生成的SQL如下：

-- 这个没问题
SELECT Organizations.*
FROM Organizations
WHERE Organizations.OrganizationId = [Organization ID]

-- 我不想关联Organizations和Departments，只想查WHERE OrganizationId = [Organization ID]的Departments
SELECT Organizations.OrganizationId
       Departments.*
FROM Organizations
     JOIN Departments
        ON  Departments.OrganizationId = Organizations.OrganizationId 
     JOIN Employees
        ON  Employees.OrganizationId = Departments.OrganizationId 
        AND Employees.DepartmentId = Departments.DepartmentId 
WHERE Organizations.OrganizationId = [Organization ID]

-- 我不想关联Organizations、Departments和Employees，只想查WHERE OrganizationId = [Organization ID]的Employees
SELECT Organizations.OrganizationId,
       Departments.OrganizationId,
       Departments.DepartmentId,
       Employees.*
FROM Organizations
     JOIN Departments
        ON  Departments.OrganizationId = Organizations.OrganizationId 
     JOIN Employees
        ON  Employees.OrganizationId = Departments.OrganizationId 
        AND Employees.DepartmentId = Departments.DepartmentId 
WHERE Organizations.OrganizationId = [Organization ID]

所以我希望手动查询并附加子导航属性，现在的实现代码如下，想问问这样做是否正确，会不会留下后续隐患？如果不正确，该怎么修改？

public Organization GetOrganization(int organizationId)
{
   // 加载指定组织的所有数据
   Organization organization = context.Organizations.FirstOrDefault(p => p.OrganizationId == organizationId);
   var departments = context.Departments.Where(p => p.OrganizationId == organizationId).ToList();
   var employees = context.Employees.Where(p => p.OrganizationId == organizationId).ToList();

   // 将部门附加到组织
   organization.Departments = departments;
   departments.ForEach(dept => context.Departments.Attach(dept));

   // 将员工附加到对应部门；我知道可以用字典优化员工分组
   foreach (Department department in organization.Departments)
   {
      List<Employee> departmentEmployees = employees.Where(p => p.DepartmentId == department.DepartmentId).ToList();

      department.Employees = departmentEmployees;
      departmentEmployees.ForEach(emp => context.Employees.Attach(emp));

   }

   return organization;
}

你的实现基本可以正常工作，但存在几个可以优化的点，以及需要注意的隐患：

一、现有实现的隐患 #

1. 重复Attach操作：
   • 当你用context.Departments.Where(...).ToList()查询部门时，这些实体已经被EF上下文跟踪了（因为是从上下文查询出来的），后续再调用context.Departments.Attach(dept)属于多余操作，甚至可能引发InvalidOperationException（如果实体已经被跟踪）。员工的Attach操作同理。
2. 内存分组效率低：
   • 你用employees.Where(p => p.DepartmentId == department.DepartmentId)在内存中逐个过滤员工，当员工数量较多时，这种方式的时间复杂度是O(n*m)（n是部门数，m是员工数），性能会很差。
3. 导航属性的状态同步：
   • 手动赋值导航属性后，EF上下文虽然会跟踪这些实体，但如果后续对这些实体进行修改、删除操作，需要确保导航属性的双向关联（比如如果实体有反向导航属性，比如Employee.Department）也正确设置，否则可能出现状态不一致的问题。

二、优化后的实现 #

public Organization GetOrganization(int organizationId)
{
    // 加载组织，此时Departments导航属性未加载
    var organization = context.Organizations.FirstOrDefault(p => p.OrganizationId == organizationId);
    if (organization == null)
        return null;

    // 加载部门，此时实体已被上下文跟踪
    var departments = context.Departments
        .Where(p => p.OrganizationId == organizationId)
        .ToList();

    // 加载员工并按DepartmentId分组，提升内存分组效率
    var employeesByDeptId = context.Employees
        .Where(p => p.OrganizationId == organizationId)
        .GroupBy(p => p.DepartmentId)
        .ToDictionary(g => g.Key, g => g.ToList());

    // 关联组织和部门：直接赋值即可，上下文已跟踪部门实体
    organization.Departments = departments;

    // 关联部门和员工
    foreach (var department in departments)
    {
        // 尝试获取当前部门的员工，没有则返回空列表
        employeesByDeptId.TryGetValue(department.DepartmentId, out var departmentEmployees);
        department.Employees = departmentEmployees ?? new List<Employee>();
    }

    return organization;
}

三、额外注意事项 #

• 上下文生命周期：确保这个方法使用的上下文在后续操作中（比如修改实体）是同一个实例，否则手动关联的导航属性在新上下文里不会被自动识别为关联关系。
• 反向导航属性：如果你的实体定义了反向导航（比如Employee.Department、Department.Organization），建议在关联时也设置这些属性，比如在给department.Employees赋值时，遍历员工设置emp.Department = department，这样EF的状态跟踪会更准确，避免后续操作出现异常。
• 性能测试：如果数据量极大，可以考虑用AsNoTracking()来取消实体跟踪（如果后续不需要修改这些实体），进一步提升查询性能，但此时手动关联的导航属性不会被EF自动维护。

内容的提问来源于stack exchange，提问作者Ben Rubin