如何优化返回关联数据的Entity Framework LINQ查询？

你这个查询慢的核心原因是N+1查询问题，加上在LINQ查询中直接调用内存中的Factory方法，导致EF无法生成高效的SQL——每条Client都会触发单独的地址、联系人查询，10万条数据就会产生十万次额外请求，时间自然被拉长。下面是几个实用的优化方案：

方案一：预加载+内存映射（兼容多数EF版本） #

先通过Include一次性预加载所有需要的关联数据，避免N+1查询，再在内存中转换为Employee对象：

// 第一步：预加载关联数据，从数据库一次性查询核心实体
var rawData = await (from account in _databaseContext.Accounts
                     join client in _databaseContext.Clients
                         .Include(c => c.ClientAddresses)
                         .Include(c => c.ClientContacts)
                     on account.AddressClientID equals client.ClientId
                     where client.EffectiveTo == null
                         && client.ClientType.Equals("PERSON")
                         && account.EffectiveTo == null
                         && account.Status.Equals("INFORCE")
                     select new { account, client })
                     .ToListAsync(cancellationToken);

// 第二步：在内存中映射为Employee对象
return rawData.Select(item => new Employee
{
    Id = item.account.AccountId,
    ClientId = item.client.ClientId,
    FirstName = item.client.GivenNames,
    LastName = item.client.Surname,
    Gender = item.client.Gender,
    Addresses = item.client.ClientAddresses
        .Where(a => a.EffectiveTo == null)
        .Select(AddressFactory.Create)
        .ToList(),
    Contacts = item.client.ClientContacts
        .Where(c => c.EffectiveTo == null && (c.ContactType == "EMAIL" || c.ContactType == "MOBILE"))
        .OrderBy(c => c.EffectiveFrom)
        .Select(c => ContactFactory.Create<Employee>(item.client, c))
        .ToList(),
    DateOfBirth = item.client.DateOfBirth
}).ToList();

方案二：EF Core过滤Include（EF Core 5.0+适用） #

如果你的项目用的是EF Core 5及以上版本，可以直接在Include中添加过滤和排序条件，让数据库层面完成数据筛选，减少内存处理压力：

return await (from account in _databaseContext.Accounts
              join client in _databaseContext.Clients
                  .Include(c => c.ClientAddresses.Where(a => a.EffectiveTo == null))
                  .Include(c => c.ClientContacts
                      .Where(c => c.EffectiveTo == null && (c.ContactType == "EMAIL" || c.ContactType == "MOBILE"))
                      .OrderBy(c => c.EffectiveFrom))
              on account.AddressClientID equals client.ClientId
              where client.EffectiveTo == null
                  && client.ClientType.Equals("PERSON")
                  && account.EffectiveTo == null
                  && account.Status.Equals("INFORCE")
              select new Employee
              {
                  Id = account.AccountId,
                  ClientId = client.ClientId,
                  FirstName = client.GivenNames,
                  LastName = client.Surname,
                  Gender = client.Gender,
                  Addresses = client.ClientAddresses.Select(AddressFactory.Create).ToList(),
                  Contacts = client.ClientContacts.Select(c => ContactFactory.Create<Employee>(client, c)).ToList(),
                  DateOfBirth = client.DateOfBirth
              }).ToListAsync(cancellationToken);

额外优化建议 #

• 添加数据库索引：给Clients.ClientId、Clients.EffectiveTo、Clients.ClientType、Accounts.EffectiveTo、Accounts.Status、ClientAddresses.EffectiveTo、ClientContacts.EffectiveTo、ClientContacts.ContactType这些字段创建索引，能大幅提升数据库过滤和关联的速度。
• 简化对象转换：如果Factory方法逻辑不复杂，建议直接在Select中构造对象，减少内存转换的开销；如果必须用Factory，优先采用方案一的“先查数据再转换”模式。

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