UpdateFromQueryAsync can't update a field with GETUTCDATE() and substract another column

[jlchavez]

When trying to reset all active records for statistics of how long someting has been running, when the servers starts or shut down, I have a model like this: (minimal code)

    public record CircuitData
    {
        [Required]
        public DateTime? Created { get; set; }
        
        public DateTime? LastUpdated { get; set; }

        public TimeSpan? Duration { get; set; }
        
    }

the DbContext has

public DbSet<CircuitData> Circuits { get; set; }

the service has:

        private async Task ClearCircuitsAsync()
        {
            using var context = GetAppScopedContext();
            await context.Db.Circuits.
                .UpdateFromQueryAsync(c => new CircuitData { LastUpdated = DateTime.UtcNow, Duration = DateTime.UtcNow - c.Created });
        }

the query views in the profiler:

exec sp_executesql N'
UPDATE A 
SET A.[LastUpdated] = @zzz_BatchUpdate_1,
A.[Duration] = GETUTCDATE(), B.[Created]
FROM [Security].[Circuits] AS A
INNER JOIN ( SELECT [c].[Id], [c].[CircuitId], [c].[Created], [c].[Duration], [c].[LastUpdated], [c].[ServerId], [c].[Status], [c].[UserID], [c].[Username]
FROM [Security].[Circuits] AS [c]
           ) AS B ON A.[Id] = B.[Id]
',
N'@ServerId uniqueidentifier,@zzz_BatchUpdate_1 datetime2(7)',
@ServerId='00000000-0000-0000-0000-000000000000',
@zzz_BatchUpdate_1='2026-04-11 16:51:19.0867270'

The code Duration = DateTime.UtcNow - c.Created was translated to A.[Duration] = GETUTCDATE(), B.[Created] instead of A.[Duration] = GETUTCDATE() - B.[Created], but the types might not be the same, Duration would be a time and Created a datetime2 it would need to be used DATEDIFF to seconds and then casted to a time to be stored it as Time for the .net TimeSpan type.

A.[Duration] = CAST(DATEADD(SECOND, DATEDIFF(SECOND, B.[Created], GETUTCDATE()), 0) AS time)

Tried to use a specific value in the query in C#, but the query translation still hits the query visitor mistake.

var utcTime = DateTime.UtcNow;
await context.Db.Circuits.
    .UpdateFromQueryAsync(c => new CircuitData { LastUpdated = utcTime, Duration = utcTime - c.Created });

and we have this in the translated query, and the DATEDIFF and castings still would be required to make it work.

A.[Duration] = ''2026-04-11T17:20:08.0100774Z'', B.[Created]

Thanks for the great product, first time finding something. If you've got a workaroung that would be great. Thanks.

[JonathanMagnan]

Hello @jlchavez ,

Thank you for reporting, we will look if we can do something.

Best Regards,

Jon

[JonathanMagnan]

Hello @jlchavez,

After investigating this issue, it will not be fixed.

The reason is that it requires a client-side evaluation: https://learn.microsoft.com/en-us/ef/core/querying/client-eval#client-evaluation-in-the-top-level-projection

When looking at the query generated by EF Core, we can see that it first returns the values:

SELECT GETUTCDATE(), [e].[Created]
FROM [EntitySimples] AS [e]

Before evaluating DateTime.UtcNow - c.Created on the client side.

The way our library works is by using the query generated by EF Core, which currently makes this scenario impossible (using the current technique) to support.

We also tried ExecuteUpdate for comparison:
https://www.learnentityframeworkcore.com/saving/execute-update

Unfortunately, this query is not supported either for the same reason:

System.InvalidOperationException : 'The LINQ expression 'DbSet<EntitySimple>()
    .ExecuteUpdate(new Tuple<Delegate, object>[]
    { 
        new Tuple<Delegate, object>(
            e => e.LastUpdated, 
            (object)@p
        ), 
        new Tuple<Delegate, object>(
            e => e.Duration, 
            e => @dateUtcNow - e.Created
        ) 
    })' could not be translated. Additional information: The following lambda argument to 'SetProperty' does not represent a valid value: 'e => @dateUtcNow - e.Created'.
The following lambda argument to 'SetProperty' does not represent a valid value: 'e => @dateUtcNow - e.Created'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Let me know if that makes sense.

Best Regards,

Jon