.NET 6 EF中如何为BenefitEntity及其关联分类关联翻译表

我正在使用.NET 6与Entity Framework，场景如下：

• BenefitEntity包含Name、Description属性，与BenefitCategoryEntity关联
• BenefitCategoryEntity同样包含Name、Description属性
• 所有实体的名称和描述都通过TranslationEntity表存储多语言翻译，按EntityId（实体ID）、Language（语言）、Property（属性名）区分

核心实体类定义：

public class BenefitEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Guid BenefitCategoryId { get; set; }
    public virtual BenefitCategoryEntity CategoryEntity { get; set; }
}

public class BenefitCategoryEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

public class TranslationEntity
{
    public Guid EntityId { get; set; }
    public string Language { get; set; }
    public string Property { get; set; }
    public string Value { get; set; }
}

我已经实现了BenefitEntity自身的翻译关联替换，但不知道如何给关联的BenefitCategoryEntity也应用相同的翻译逻辑。当前实现代码如下：

public async Task<ICollection<BenefitEntity>> GetAllBenefitEntitiesAsync()
{
    var benefitsQuery = Entities
        .Include(a => a.CategoryEntity);

    var translationsQuery = DbContext.TranslationsEntities!
        .AsNoTracking()
        .Where(e => e.Language.Equals(LANGUAGE));

    var benefits = await benefitsQuery.ToListAsync();

    var result = benefits
        .GroupJoin(
            translationsQuery,
            benefit => benefit.Id,
            translation => translation.EntityId,
            (benefit, translation) => new { Benefit = benefit, Translation = translation })
        .Select(result => new BenefitEntity
        {
                // Benefit Name and Description from joined translations
                Name = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Name)))?.Value ?? result.Benefit.Name,
                Description = result.Translation.FirstOrDefault(e => e.Property.Equals(nameof(BenefitEntity.Description)))?.Value ?? result.Benefit.Description,
                Id = result.Benefit.Id,
                CategoryEntity = result.Benefit.CategoryEntity,
                BenefitCategoryId = result.Benefit.BenefitCategoryId,
        })
        .AsQueryable();

    return result.ToList();
}

如何基于当前代码，用同一个translationsQuery为关联的CategoryEntity替换对应翻译？

你可以在Select映射BenefitEntity的时候，单独处理CategoryEntity：从translationsQuery中筛选出对应分类ID的翻译项，替换其Name和Description属性。修改后的代码如下：

public async Task<ICollection<BenefitEntity>> GetAllBenefitEntitiesAsync()
{
    var benefitsQuery = Entities
        .Include(a => a.CategoryEntity);

    var translationsQuery = DbContext.TranslationsEntities!
        .AsNoTracking()
        .Where(e => e.Language.Equals(LANGUAGE))
        .ToList(); // 提前加载所有对应语言的翻译，避免多次查询数据库

    var benefits = await benefitsQuery.ToListAsync();

    var result = benefits
        .Select(benefit => 
        {
            // 获取当前Benefit的翻译集合
            var benefitTranslations = translationsQuery.Where(t => t.EntityId == benefit.Id);
            // 获取当前关联Category的翻译集合
            var categoryTranslations = translationsQuery.Where(t => t.EntityId == benefit.CategoryEntity.Id);

            return new BenefitEntity
            {
                Id = benefit.Id,
                BenefitCategoryId = benefit.BenefitCategoryId,
                // 替换Benefit的名称和描述翻译
                Name = benefitTranslations.FirstOrDefault(t => t.Property == nameof(BenefitEntity.Name))?.Value ?? benefit.Name,
                Description = benefitTranslations.FirstOrDefault(t => t.Property == nameof(BenefitEntity.Description))?.Value ?? benefit.Description,
                // 替换关联Category的名称和描述翻译
                CategoryEntity = new BenefitCategoryEntity
                {
                    Id = benefit.CategoryEntity.Id,
                    Name = categoryTranslations.FirstOrDefault(t => t.Property == nameof(BenefitCategoryEntity.Name))?.Value ?? benefit.CategoryEntity.Name,
                    Description = categoryTranslations.FirstOrDefault(t => t.Property == nameof(BenefitCategoryEntity.Description))?.Value ?? benefit.CategoryEntity.Description
                }
            };
        })
        .ToList();

    return result;
}

关键说明： #

1. 提前加载翻译：将translationsQuery转为List，避免在循环筛选时多次触发数据库查询，提升性能
2. 分别筛选翻译集合：通过EntityId匹配对应实体的翻译项，分别处理Benefit和Category的翻译
3. 创建新的Category实例：原CategoryEntity是EF上下文跟踪的对象，直接修改可能影响后续操作，创建新实例赋值更安全；若无需跟踪，也可直接修改原实体属性，但不推荐
4. 空值回退：如果没有找到对应语言的翻译，自动使用实体本身的原始属性值

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