EF Core 7如何单次批量更新多实体Position且不预取数据？

问题背景 #

这并非通用批量更新（Bulk Update）场景，每条记录需要设置唯一的Position值。现有一个支持用户自由排序的项列表，包含Id、Name、Position字段，用户提交新排序后，需更新所有项的Position字段。目前仅持有Id与对应新Position的映射列表，要求不提前从数据库获取实体，且不丢失Name等其他字段数据，同时通过单次数据库往返完成所有更新。

已尝试方案及局限 #

• 单条循环更新：每条记录单独发起一次数据库请求，性能低下

ctx.Products
   .Where(q => q.Id == item.Id)
   .ExecuteUpdate(q => q.SetProperty(p => p.Position, _ => item.Position))

• 全量获取实体更新：需要从数据库拉取所有目标实体的完整数据，额外消耗IO资源

var idList = new int[] { 1, 2, 3, 4 };

using (var db = new SomeDatabaseContext())
{
    var friends = db.Friends.Where(f => idList.Contains(f.ID)).ToList();

    friends.ForEach(a => a.msgSentBy = '1234');
    db.SaveChanges();
}

• 修改Change Tracker状态：手动标记属性修改时，由于必填字段Name被设为空字符串，导致数据库中对应字段被更新为空，造成数据丢失

var posList = new (int, int)[]
{
    (1, 2),
    (2, 1),
    (3, 1),
};

var ctx = new TestContext();

foreach (var (id, pos) in posList)
{
    var entity = new DbItem
    {
        Id = id,
        Name = "", // 必填属性
        Position = pos,
    };

    var entry = ctx.Attach(entity);
    entry.State = EntityState.Modified;
    entry.Property(q => q.Position).IsModified = true;
}

await ctx.SaveChangesAsync();

• Attach后修改属性：引发并发异常，EF Core误判数据被修改或删除

// 外围代码与上述类似
var entity = new DbItem
{
    Id = id,
    Name = "", // 必填属性
    Position = -1, // 确保会被修改
};

var entry = ctx.Attach(entity);
entity.Position = pos;

未处理的异常。Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 数据库操作预期影响1行，但实际影响0行；实体加载后数据可能已被修改或删除。

可行解决方案（EF Core 7） #

方案1：使用ExecuteUpdate结合CASE表达式（推荐）

利用EF Core 7中ExecuteUpdate对复杂表达式的支持，通过构建CASE语句实现单次往返批量更新，无需提前获取实体，且不会影响其他字段。

var posList = new (int Id, int NewPosition)[]
{
    (1, 2),
    (2, 1),
    (3, 3),
};

using var ctx = new TestContext();

// 构建CASE表达式，匹配Id并设置对应新Position
var caseExpression = ctx.Products.Select(p => p.Id)
    .Switch()
    .Case(posList.Select(t => (Expression<Func<Product, bool>>)(p => p.Id == t.Id), t => t.NewPosition))
    .Default(p => p.Position);

// 执行批量更新
await ctx.Products
    .Where(p => posList.Select(t => t.Id).Contains(p.Id))
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Position, caseExpression));

该方案会生成一条包含CASE逻辑的UPDATE语句，一次性完成所有符合条件记录的Position更新。

方案2：执行原生SQL语句

如果CASE表达式写法不够直观，可直接构造原生SQL实现相同效果，同样是单次数据库往返：

var posList = new (int Id, int NewPosition)[]
{
    (1, 2),
    (2, 1),
    (3, 3),
};

using var ctx = new TestContext();

// 构建参数化SQL的CASE子句和参数列表
var parameters = new List<SqlParameter>();
var caseClauses = new List<string>();
for (int i = 0; i < posList.Length; i++)
{
    var paramId = $"@Id{i}";
    var paramPos = $"@Pos{i}";
    caseClauses.Add($"WHEN Id = {paramId} THEN {paramPos}");
    parameters.Add(new SqlParameter(paramId, posList[i].Id));
    parameters.Add(new SqlParameter(paramPos, posList[i].NewPosition));
}

// 组装完整SQL语句
var sql = $@"
UPDATE Products
SET Position = CASE
    {string.Join(" ", caseClauses)}
    ELSE Position
END
WHERE Id IN ({string.Join(", ", parameters.Where(p => p.ParameterName.StartsWith("@Id")).Select(p => p.ParameterName))})";

// 执行原生SQL
await ctx.Database.ExecuteSqlRawAsync(sql, parameters.ToArray());

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