EF update statement with select query and where cause combined-CodePudding

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

How to write this query in 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);

but it is not good performance as two round-trip.

I want to write EF Core only one round-trip for high performance.

CodePudding user response：

How to write this query in EF Core?

Don't fetch the users. Just compose that into the update query using Bulk Update and Bulk Delete eg:

//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
                 );