ExecuteUpdateAsync: Could not be translated

Question

I'm facing an issue trying to perform a bulk update through EFCore 7 ExecuteUpdateAsync() method.

The error message says:

.ExecuteUpdate(calls => calls.SetProperty( propertyExpression: x => x.Meter.StatusId, valueExpression: x => x.NewStatusId))' could not be translated. Additional information: The following 'SetProperty' failed to translate: 'SetProperty(x => x.Meter.StatusId, x => x.NewStatusId)'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)

I have an abstract entity BaseMeter which implements this public method to retrieve another Entity MeterStatusType which is abstract as well and has a relationship with this BaseMeter entity

public MeterStatusType RecalculateStatus() =>
    StartDate.Date <= DateTime.Today &&
    (!EndDate.HasValue ||
     DateTime.Today <= EndDate.Value.Date)
        ? MeterStatusType.Live
        : MeterStatusType.Disused;

And this is my ExecuteUpdateAsync:

await _dbContext.Set<BaseMeter>()
    .Select(m => new
    {
        Meter = m,
        NewStatusId = m.RecalculateStatus().Id
    })
    .ExecuteUpdateAsync(calls => calls.SetProperty(x => x.Meter.StatusId, x => x.NewStatusId),
        cancellationToken);

I have done it following the MSDN and the EFCore version being used is the 7.0.15.

Could anyone throw some light into this please?

Answer 1

EF Core cannot translate a bare C# function. It can only translate an Expression or an IQueryable.

You could probably use Expressions with a Visitor, but the easiest method is probably to just create a new type with IQueryable

public class MeterStatusUpdate
{
    public Meter Meter { get; set; }
    public MeterStatusType NewStatusId { get; set; }
}

public static IQueryable<MeterStatusUpdate> RecalculateStatus(this IQueryable<Meter> source) =>
    source.Select(m => new MeterStatusUpdate {
        Meter = m,
        NewStatusId = m.StartDate.Date <= DateTime.Today
          && (!m.EndDate.HasValue || DateTime.Today <= m.EndDate.Value.Date)
        ? MeterStatusType.Live
        : MeterStatusType.Disused
    });

Then use it like this

await _dbContext
    .Set<BaseMeter>()
    .RecalculateStatus()
    .ExecuteUpdateAsync(calls => calls
        .SetProperty(x => x.Meter.StatusId, x => x.NewStatusId),
        cancellationToken);

Note that you can't re-embed an IQueryable inside another one. Either use a separate variable for that, or switch to Expressions.

---

An alternative, if that doesn't work, is to embed the whole calculation inside ExecuteUpdateAsync

await _dbContext
    .Set<BaseMeter>()
    .ExecuteUpdateAsync(calls => calls
        .SetProperty(x => x.StatusId, x =>
            x.StartDate.Date <= DateTime.Today
            && (!x.EndDate.HasValue || DateTime.Today <= x.EndDate.Value.Date)
            ? MeterStatusType.Live
            : MeterStatusType.Disused,
        cancellationToken);