EF Core 6迁移中可翻译属性的查询优化方案咨询

问题描述 #

我正在将一个遗留项目的ORM映射器迁移至EF Core 6，遇到了多语言翻译字段的处理难题。项目中有一张通用Text表存储所有实体的多语言翻译数据，该表无外键，文化语言由当前登录Person的配置动态决定。

我需要仅获取公司的本地化Name，而非所有Text条目，且不能使用懒加载以避免影响应用性能。目前通过向Automapper的ProjectTo方法传递参数实现了需求，但生成的SQL存在重复子查询，现寻求可复用公司/地址解析逻辑的优化方案。

相关代码示例 #

查询代码 #

var users = await _context.Person
    .AsNoTracking()
    .IsActive(_dateTimeProvider.UtcNow)
    .BySubjectId(request.SubjectId)
    .ProjectTo<UserDTO>(_mapper.ConfigurationProvider, new { timeStamp = _dateTimeProvider.UtcNow, context = _context })
    .SingleOrDefaultAsync(cancellationToken);

映射代码 #

private class Mapping : Profile
{
        public Mapping()
        {
            DateTime? timeStamp = null;
            IRoomsDbContext context = null;

            CreateMap<Person, User>()
                .ForMember(d => d.City, o => o.MapFrom(p => p.CompanyPerson.SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= timeStamp) && (!fp.Ende.HasValue || fp.Ende.Value > timeStamp)).Company.Adresse.Single(a => a.IstStandard).Ort))
                .ForMember(d => d.CompanyDescription, o => o.MapFrom(p => context.Text.SingleOrDefault(t => t.LanguageId == "de" && t.FieldOid == p.CompanyPerson.SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= timeStamp) && (!fp.Ende.HasValue || fp.Ende.Value > timeStamp)).Company.BezeichnungOid).Value))
                .ForMember(d => d.Zip, o => o.MapFrom(p => p.CompanyPerson.SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= timeStamp) && (!fp.Ende.HasValue || fp.Ende.Value > timeStamp)).Firma.Adresse.Single(a => a.IstStandard).Plz));
        }
}

生成的SQL代码 #

DECLARE @__cutoffDate_0 datetime = '2023-11-03 10:23:14.960'
DECLARE @__timeStamp_1 datetime = '2023-11-03 10:23:14.960'

SELECT
    [p].[Id],
    (SELECT TOP(1) [a].[Ort]
    FROM [Adresse] AS [a]
    WHERE (((
        SELECT TOP(1) [f1].[Id]
        FROM [CompanyPerson] AS [f0]
        INNER JOIN [Company] AS [f1] ON [f0].[CompanyId] = [f1].[Id]
        WHERE ([p].[Id] = [f0].[PersonId]) AND ((([f0].[Beginn] IS NULL) OR ([f0].[Beginn] <= @__timeStamp_1)) AND (([f0].[Ende] IS NULL) OR ([f0].[Ende] > @__timeStamp_1)))) IS NOT NULL) AND ((
        SELECT TOP(1) [f3].[Id]
        FROM [CompanyPerson] AS [f2]
        INNER JOIN [Company] AS [f3] ON [f2].[CompanyId] = [f3].[Id]
        WHERE ([p].[Id] = [f2].[PersonId]) AND ((([f2].[Beginn] IS NULL) OR ([f2].[Beginn] <= @__timeStamp_1)) AND (([f2].[Ende] IS NULL) OR ([f2].[Ende] > @__timeStamp_1)))) = [a].[CompanyId])) AND ([a].[IstStandard] = CAST(1 AS bit))
    ) AS [City],
    (SELECT TOP(1) [a0].[Plz]
        FROM [Adresse] AS [a0]
        WHERE (((
            SELECT TOP(1) [f7].[Id]
            FROM [CompanyPerson] AS [f6]
            INNER JOIN [Company] AS [f7] ON [f6].[CompanyId] = [f7].[Id]
            WHERE ([p].[Id] = [f6].[PersonId]) AND ((([f6].[Beginn] IS NULL) OR ([f6].[Beginn] <= @__timeStamp_1)) AND (([f6].[Ende] IS NULL) OR ([f6].[Ende] > @__timeStamp_1)))) IS NOT NULL) AND ((
            SELECT TOP(1) [f9].[Id]
            FROM [CompanyPerson] AS [f8]
            INNER JOIN [Company] AS [f9] ON [f8].[CompanyId] = [f9].[Id]
            WHERE ([p].[Id] = [f8].[PersonId]) AND ((([f8].[Beginn] IS NULL) OR ([f8].[Beginn] <= @__timeStamp_1)) AND (([f8].[Ende] IS NULL) OR ([f8].[Ende] > @__timeStamp_1)))) = [a0].[CompanyId])) AND ([a0].[IstStandard] = CAST(1 AS bit))
    ) AS [Zip],
    (SELECT TOP(1) [t].[Value]
    FROM [Text] AS [t]
    WHERE ([t].[LanguageId] = 'de') AND ([t].[FieldOid] = (
        SELECT TOP(1) [f5].[BezeichnungOid]
        FROM [CompanyPerson] AS [f4]
        INNER JOIN [Company] AS [f5] ON [f4].[CompanyId] = [f5].[Id]
        WHERE ([p].[Id] = [f4].[PersonId]) AND ((([f4].[Beginn] IS NULL) OR ([f4].[Beginn] <= @__timeStamp_1)) AND (([f4].[Ende] IS NULL) OR ([f4].[Ende] > @__timeStamp_1)))))
    ) AS [CompanyName]
FROM [Person] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [CompanyPerson] AS [f]
    WHERE ([p].[Id] = [f].[PersonId]) AND ((([f].[Beginn] IS NULL) OR ([f].[Beginn] <= @__cutoffDate_0)) AND (([f].[Ende] IS NULL) OR ([f].[Ende] >= @__cutoffDate_0))))

优化方案 #

1. 直接使用EF Core投影替代Automapper复杂映射 #

在查询阶段通过Select直接构造DTO，EF Core会自动优化SQL生成逻辑，避免重复子查询：

var users = await _context.Person
    .AsNoTracking()
    .IsActive(_dateTimeProvider.UtcNow)
    .BySubjectId(request.SubjectId)
    .Select(p => 
    {
        var activeCompanyPerson = p.CompanyPerson
            .SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= _dateTimeProvider.UtcNow) 
                                && (!fp.Ende.HasValue || fp.Ende.Value > _dateTimeProvider.UtcNow));
        var standardAddress = activeCompanyPerson?.Company.Adresse.SingleOrDefault(a => a.IstStandard);
        var companyName = _context.Text
            .SingleOrDefault(t => t.LanguageId == "de" 
                                && t.FieldOid == activeCompanyPerson?.Company.BezeichnungOid)?.Value;
        return new UserDTO
        {
            Id = p.Id,
            City = standardAddress?.Ort,
            Zip = standardAddress?.Plz,
            CompanyDescription = companyName
        };
    })
    .SingleOrDefaultAsync(cancellationToken);

2. 封装复用逻辑到扩展方法 #

把获取有效公司、标准地址、本地化名称的逻辑封装成通用扩展方法，方便在多个查询场景复用：

public static class EntityExtensions
{
    public static CompanyPerson GetActiveCompanyPerson(this Person person, DateTime timeStamp)
    {
        return person.CompanyPerson.SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= timeStamp) 
                                                      && (!fp.Ende.HasValue || fp.Ende.Value > timeStamp));
    }

    public static Adresse GetStandardAddress(this Company company)
    {
        return company.Adresse.SingleOrDefault(a => a.IstStandard);
    }

    public static string GetLocalizedName(this IRoomsDbContext context, string fieldOid, string languageId)
    {
        return context.Text.SingleOrDefault(t => t.LanguageId == languageId && t.FieldOid == fieldOid)?.Value;
    }
}

调用示例：

var users = await _context.Person
    .AsNoTracking()
    .IsActive(_dateTimeProvider.UtcNow)
    .BySubjectId(request.SubjectId)
    .Select(p => 
    {
        var activeCompany = p.GetActiveCompanyPerson(_dateTimeProvider.UtcNow);
        var standardAddress = activeCompany?.Company.GetStandardAddress();
        var companyName = _context.GetLocalizedName(activeCompany?.Company.BezeichnungOid, "de");
        return new UserDTO
        {
            Id = p.Id,
            City = standardAddress?.Ort,
            Zip = standardAddress?.Plz,
            CompanyDescription = companyName
        };
    })
    .SingleOrDefaultAsync(cancellationToken);

3. 使用Automapper自定义值解析器 #

创建自定义值解析器封装重复逻辑，替代映射中的硬编码子查询，让Automapper生成更高效的SQL：

public class CompanyCityResolver : IValueResolver<Person, UserDTO, string>
{
    private readonly DateTime _timeStamp;

    public CompanyCityResolver(DateTime timeStamp)
    {
        _timeStamp = timeStamp;
    }

    public string Resolve(Person source, UserDTO destination, string destMember, ResolutionContext context)
    {
        var activeCompanyPerson = source.CompanyPerson.SingleOrDefault(fp => (!fp.Beginn.HasValue || fp.Beginn.Value <= _timeStamp) 
                                                                       && (!fp.Ende.HasValue || fp.Ende.Value > _timeStamp));
        return activeCompanyPerson?.Company.Adresse.SingleOrDefault(a => a.IstStandard)?.Ort;
    }
}

// 同理创建CompanyZipResolver和CompanyNameResolver

修改映射配置：

private class Mapping : Profile
{
    public Mapping()
    {
        CreateMap<Person, UserDTO>()
            .ForMember(d => d.City, o => o.MapFrom<CompanyCityResolver, DateTime>(_ => _timeStamp))
            .ForMember(d => d.Zip, o => o.MapFrom<CompanyZipResolver, DateTime>(_ => _timeStamp))
            .ForMember(d => d.CompanyDescription, o => o.MapFrom<CompanyNameResolver, object>(_ => new { TimeStamp = _timeStamp, Context = _context }));
    }
}

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