EF update语句与select查询和where原因组合

Question

UPDATE [Revision] 
SET [Value] = [Value] + 1
WHERE [Type] = 0 
AND [Number] IN (SELECT [Number] FROM [User] 
WHERE Id IN (5111, 5211, 5311))

如何在EF Core中编写这个查询?

I tried with:

var n = await db.Users
.AsNoTracking()
.Where(x => id.Contains(x.Id))
.Select(y => y.Number)
.ToListAsync(s.Token)
.ConfigureAwait(false);
await db.Revisions
.AsNoTracking() 
.Where(y => y.Type == Consts.TABLE_USER && n.Contains(y.Number))
.ExecuteUpdateAsync(z => 
z.SetProperty(o => o.Value, o => o.Value + 1),
s.Token
).ConfigureAwait(false);

但是作为两个往返，性能不是很好。

为了高性能，我想只写EF Core一次往返。

Answer 1

如何在EF Core中编写此查询?

不要去取用户。只需使用批量更新和批量删除将其组合到更新查询中，例如:

//n should be a subquery expression, not a collection
var n = db.Users
.Where(x => id.Contains(x.Id))
.Select(y => y.Number);
await db.Revisions
.Where(y => y.Type == Consts.TABLE_USER && n.Contains(y.Number))
.ExecuteUpdateAsync(z => 
z.SetProperty(o => o.Value, o => o.Value + 1),
s.Token
);