EF Core中MySQL DbSet结合List使用ExecuteUpdate报错解决方案咨询

我有三个独立数据库（Site A、Site B、Site C），使用同款数据库软件但结构略有差异，需从中提取同类数据（部分需合并Site A与B的数据）并同步至独立仪表板。数据每日刷新，需实现新增插入、变更更新、过期/删除数据清理的逻辑。

因原数据库老旧繁琐、无适配NuGet包且已停止官方支持，我将数据抽取至独立MySQL数据库进行处理。现有流程为手动导出CSV、合并后替换仪表板关联CSV，需保留该格式。

由于DbContext要求每张表对应独立实体类型，各表实体均继承自基类，已定义对应的MySQL DbContext及实体类：

DbContext代码 #

public class MySqlDbContext : DbContext
{
    public MySqlDbContext(DbContextOptions<MySqlDbContext> options) : base(options)
    {}

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Ball_A>().HasKey(nameof(Ball_A.Site), nameof(Ball_A.ID));
        builder.Entity<Ball_B>().HasKey(nameof(Ball_B.Site), nameof(Ball_B.ID));
        builder.Entity<Ball_AB>().HasKey(nameof(Ball_AB.Site), nameof(Ball_AB.ID));
        builder.Entity<Ball_C>().HasKey(nameof(Ball_C.Site), nameof(Ball_C.ID));
        builder.Entity<Box_A>().HasKey(nameof(Box_A.Site), nameof(Box_A.ID));
        builder.Entity<Box_B>().HasKey(nameof(Box_B.Site), nameof(Box_B.ID));
        builder.Entity<Box_AB>().HasKey(nameof(Box_AB.Site), nameof(Box_AB.ID));
        builder.Entity<Box_C>().HasKey(nameof(Box_C.Site), nameof(Box_C.ID));
    }

    public DbSet<Ball_A> Ball_A { get; set; }
    public DbSet<Ball_B> Ball_B { get; set; }
    public DbSet<Ball_AB> Ball_AB { get; set; }
    public DbSet<Ball_C> Ball_C { get; set; }
    public DbSet<Box_A> Box_A { get; set; }
    public DbSet<Box_B> Box_B { get; set; }
    public DbSet<Box_AB> Box_AB { get; set; }
    public DbSet<Box_C> Box_C { get; set; }
}

实体类代码 #

public class Ball
{
    public string Site { get; set; };
    public int ID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastBounced { get; set; }
    public int Radius { get; set; }
}

[Table("ball_a")]
public class Ball_A : Ball {}
[Table("ball_b")]
public class Ball_B : Ball {}
[Table("ball_ab")]
public class Ball_AB : Ball {}
[Table("ball_c")]
public class Ball_C : Ball {}

public class Box
{
    public string Site { get; set; };
    public int ID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastOpened { get; set; }
    public int Length { get; set; }
    public int Width { get; set; }
}

[Table("box_a")]
public class Box_A : Box {}
[Table("box_b")]
public class Box_B : Box {}
[Table("box_ab")]
public class Box_AB : Box {}
[Table("box_c")]
public class Box_C : Box {}

我可成功从源库获取数据并存储为List<Ball_A>、List<Box_A>等集合，但在尝试更新MySQL数据库中现有数据时，执行以下LINQ查询触发InvalidOperationException：

List<Ball_A> latestBallA = new(); // 已从Site A填充完成

int ballUpdate = (from existingBallData in _mySqlDbContext.Ball_A
                join latestBallData in latestBallA on new { existingBallData.Site, existingBallData.ID } equals new { latestBallData.Site, latestBallData.ID }
                select new { existingBallData, latestBallData }).ExecuteUpdate(s =>
                s.SetProperty(x => x.existingBallData.LastBounced, x => x.latestBallData.LastBounced)
                .SetProperty(x => x.existingBallData.Radius, x => x.latestBallData.Radius)
                );

报错信息显示LINQ表达式无法转换为SQL，尝试显式客户端评估（如AsEnumerable）则导致ExecuteUpdate无法使用，反转Join顺序也会触发新的异常。

推测问题源于DbSet与内存List的关联操作，咨询是否需创建临时表存储List数据，或有更优解决方案？

解决方案 #

方案1：临时表关联批量更新（推荐，适合大数据量） #

EF Core的ExecuteUpdate无法直接将内存List与数据库表关联生成SQL，因此可以先把内存数据导入MySQL临时表，再通过临时表关联原表执行批量更新：

1. 创建临时表对应的实体类（无需注册到DbSet）：

[Table("#temp_ball_a")]
public class TempBall_A : Ball_A {}

2. 创建临时表并插入内存数据：

// 创建临时表（MySQL语法）
await _mySqlDbContext.Database.ExecuteSqlRawAsync(@"
CREATE TEMPORARY TABLE #temp_ball_a (
    Site VARCHAR(50) NOT NULL,
    ID INT NOT NULL,
    LastBounced DATETIME NOT NULL,
    Radius INT NOT NULL,
    PRIMARY KEY (Site, ID)
)");

// 批量插入内存数据到临时表
var tempBalls = latestBallA.Select(b => new TempBall_A {
    Site = b.Site,
    ID = b.ID,
    LastBounced = b.LastBounced,
    Radius = b.Radius
}).ToList();

await _mySqlDbContext.Set<TempBall_A>().AddRangeAsync(tempBalls);
await _mySqlDbContext.SaveChangesAsync();

3. 关联临时表执行更新：

int ballUpdate = await _mySqlDbContext.Ball_A
    .Join(_mySqlDbContext.Set<TempBall_A>(),
          existing => new { existing.Site, existing.ID },
          temp => new { temp.Site, temp.ID },
          (existing, temp) => new { existing, temp })
    .ExecuteUpdateAsync(s => s
        .SetProperty(x => x.existing.LastBounced, x => x.temp.LastBounced)
        .SetProperty(x => x.existing.Radius, x => x.temp.Radius)
    );

// 可选：临时表会在会话结束后自动销毁，也可手动删除
await _mySqlDbContext.Database.ExecuteSqlRawAsync("DROP TEMPORARY TABLE #temp_ball_a");

方案2：内存对比更新（适合小数据量） #

如果数据量不大，可以将数据库中需要更新的记录加载到内存，与最新数据对比后更新：

// 提取最新数据的主键集合
var latestKeys = latestBallA.Select(b => new { b.Site, b.ID }).ToList();

// 从数据库加载匹配的现有记录
var existingBalls = await _mySqlDbContext.Ball_A
    .Where(b => latestKeys.Any(k => k.Site == b.Site && k.ID == b.ID))
    .ToListAsync();

// 遍历更新匹配的记录
foreach (var existing in existingBalls)
{
    var latest = latestBallA.First(l => l.Site == existing.Site && l.ID == existing.ID);
    existing.LastBounced = latest.LastBounced;
    existing.Radius = latest.Radius;
}

await _mySqlDbContext.SaveChangesAsync();
int ballUpdate = existingBalls.Count;

此方案无需操作临时表，但数据量大时会占用较多内存，性能不如临时表方案。

方案3：INSERT ON DUPLICATE KEY UPDATE（合并插入+更新） #

如果你的同步逻辑包含新增插入和变更更新，可以直接使用MySQL的原生语法一次性完成两种操作，效率更高：

// 参数化构造SQL，避免注入风险
var parameters = new List<object>();
var valuePlaceholders = new List<string>();
int paramIdx = 0;

foreach (var ball in latestBallA)
{
    valuePlaceholders.Add($"(@p{paramIdx++}, @p{paramIdx++}, @p{paramIdx++}, @p{paramIdx++}, @p{paramIdx++})");
    parameters.Add(ball.Site);
    parameters.Add(ball.ID);
    parameters.Add(ball.Created);
    parameters.Add(ball.LastBounced);
    parameters.Add(ball.Radius);
}

var sql = $@"
INSERT INTO ball_a (Site, ID, Created, LastBounced, Radius)
VALUES {string.Join(", ", valuePlaceholders)}
ON DUPLICATE KEY UPDATE
    LastBounced = VALUES(LastBounced),
    Radius = VALUES(Radius)
";

// 执行SQL，返回受影响的行数（插入+更新的总数）
int affectedRows = await _mySqlDbContext.Database.ExecuteSqlRawAsync(sql, parameters.ToArray());

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