如何在Entity Framework中避免更新关联的Game实体

我有一个Skill实体，它与Game实体存在关联关系：

public class Skill
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public Guid Id { get; set; }
    public virtual Game Game { get; set; }
    public virtual IdentityUser TeamMember { get; set; }
    public int SkillPoints1 { get; set; }
    public int SkillPoints2 { get; set; }
    public int SkillPoints3 { get; set; }
    public int SkillPoints4 { get; set; }
    public int SkillPoints5 { get; set; }
    public bool Spectator { get; set; }
    public Skillset SelectedSkillNumber { get; set; }
}

Game实体的定义如下：

public class Game
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public Guid Id { get; set; }
    public string GameTitle { set; get; }
    public DateTime Created { get; set; }
    public GameState GameState { get; set; }
    public virtual Team GameTeamMembers { get; set; }
    public virtual IdentityUser GameOwner { get; set; }
    public virtual ICollection<Backlog> ProductBacklog { get; set; }
}

为了加载特定Game的所有Skill记录，我用了包含Game实体的查询：

List<Skill> allSkillsets = _context.Skills.Include(x => x.Game).Where(x => x.Game.Id == gameId).Include(x => x.TeamMember).ToList();

但在循环更新Skill的部分属性时，EF会尝试同时更新关联的Game实体：

foreach (Skill player in allSkillsets)
{
    try
    {
        player.SkillPoints1 = player.SelectedSkillNumber == Skillset.Skill1 ? 5 : boost_skillset1_by + 1;
        player.SkillPoints2 = player.SelectedSkillNumber == Skillset.Skill2 ? 5 : boost_skillset2_by + 1;
        player.SkillPoints3 = player.SelectedSkillNumber == Skillset.Skill3 ? 5 : boost_skillset3_by + 1;
        player.SkillPoints4 = player.SelectedSkillNumber == Skillset.Skill4 ? 5 : boost_skillset4_by + 1;
        player.SkillPoints5 = player.SelectedSkillNumber == Skillset.Skill5 ? 5 : boost_skillset5_by + 1;

        //_context.Entry(player.Game).State = EntityState.Detached;
        _context.Entry(player.Game).State = EntityState.Unchanged;

        _context.Skills.Attach(player);
        _context.Entry(player).Property(x => x.SkillPoints1).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints2).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints3).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints4).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints5).IsModified = true;
        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        _logger.LogErrorWithObject("Could not write backlog item to database", "UpdatePlayerSkills", player);
        throw;
    }
}

由于Game实体未加载全部关联实体（比如ProductBacklog），更新操作会清除这些关联记录。我试过把Game设为null，但这会删除外键列中的Id。请问有没有办法仅更新Skill的这5个属性，而不更新关联的Game实体？

这里有几种可行的方法来解决这个问题：

1. 查询时不加载Game实体（推荐） #

根本不需要在查询Skill时包含Game实体，直接通过Skill的外键字段筛选（如果没有显式定义外键，建议先添加）：

首先给Skill实体添加显式的外键属性：

public class Skill
{
    // 其他属性不变
    public Guid GameId { get; set; } // 显式外键
    public virtual Game Game { get; set; }
}

然后修改查询语句，不需要Include(x => x.Game)，直接用GameId筛选：

List<Skill> allSkillsets = _context.Skills
    .Where(x => x.GameId == gameId)
    .Include(x => x.TeamMember)
    .ToList();

这样查询出来的Skill对象的Game属性是未加载的（延迟加载如果开启的话可能会触发，但这里我们不需要操作它），EF不会跟踪Game实体，更新时自然不会影响它。

2. 标记Game实体为Unchanged并简化操作 #

如果你必须加载Game实体，那可以在更新前明确将Game实体的状态设为Unchanged，同时注意不需要重复调用Attach（从上下文查询的实体本身已被跟踪）：

foreach (Skill player in allSkillsets)
{
    try
    {
        // 更新Skill的属性
        player.SkillPoints1 = player.SelectedSkillNumber == Skillset.Skill1 ? 5 : boost_skillset1_by + 1;
        player.SkillPoints2 = player.SelectedSkillNumber == Skillset.Skill2 ? 5 : boost_skillset2_by + 1;
        player.SkillPoints3 = player.SelectedSkillNumber == Skillset.Skill3 ? 5 : boost_skillset3_by + 1;
        player.SkillPoints4 = player.SelectedSkillNumber == Skillset.Skill4 ? 5 : boost_skillset4_by + 1;
        player.SkillPoints5 = player.SelectedSkillNumber == Skillset.Skill5 ? 5 : boost_skillset5_by + 1;

        // 明确标记Game实体为Unchanged，EF不会生成它的更新语句
        _context.Entry(player.Game).State = EntityState.Unchanged;

        // 标记Skill的需要更新的属性
        _context.Entry(player).Property(x => x.SkillPoints1).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints2).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints3).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints4).IsModified = true;
        _context.Entry(player).Property(x => x.SkillPoints5).IsModified = true;

        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        _logger.LogErrorWithObject("Could not write backlog item to database", "UpdatePlayerSkills", player);
        throw;
    }
}

3. 使用无跟踪查询 #

如果查询Skill时不需要跟踪实体状态，可以使用AsNoTracking()，然后在更新时手动附加并标记属性：

List<Skill> allSkillsets = _context.Skills
    .Include(x => x.Game)
    .Where(x => x.Game.Id == gameId)
    .Include(x => x.TeamMember)
    .AsNoTracking() // 不跟踪实体
    .ToList();

然后在更新时：

foreach (Skill player in allSkillsets)
{
    try
    {
        // 更新Skill属性
        player.SkillPoints1 = player.SelectedSkillNumber == Skillset.Skill1 ? 5 : boost_skillset1_by + 1;
        // ...其他Skill属性更新

        // 附加Skill实体
        _context.Skills.Attach(player);
        // 标记需要更新的属性
        _context.Entry(player).Property(x => x.SkillPoints1).IsModified = true;
        // ...其他属性标记

        // 标记Game为Unchanged，避免EF尝试更新它
        _context.Entry(player.Game).State = EntityState.Unchanged;

        await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        _logger.LogErrorWithObject("Could not write backlog item to database", "UpdatePlayerSkills", player);
        throw;
    }
}

4. 使用批量更新（EF Core 7+） #

如果使用的是EF Core 7及以上版本，可以直接用批量更新语句，不需要查询实体，效率更高：

await _context.Skills
    .Where(x => x.GameId == gameId)
    .ExecuteUpdateAsync(s => s
        .SetProperty(x => x.SkillPoints1, x => x.SelectedSkillNumber == Skillset.Skill1 ? 5 : boost_skillset1_by + 1)
        .SetProperty(x => x.SkillPoints2, x => x.SelectedSkillNumber == Skillset.Skill2 ? 5 : boost_skillset2_by + 1)
        .SetProperty(x => x.SkillPoints3, x => x.SelectedSkillNumber == Skillset.Skill3 ? 5 : boost_skillset3_by + 1)
        .SetProperty(x => x.SkillPoints4, x => x.SelectedSkillNumber == Skillset.Skill4 ? 5 : boost_skillset4_by + 1)
        .SetProperty(x => x.SkillPoints5, x => x.SelectedSkillNumber == Skillset.Skill5 ? 5 : boost_skillset5_by + 1)
    );

这种方式直接生成SQL更新语句，不会加载任何实体，自然不会影响Game实体。

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