EF Core 7 dynamic bulk updates

Question

I'm trying to use the DbSet.ExecuteUpdate method that was introduced in EF Core 7. When doing things the types way, so e.g.

dbContext
    .Notifications
    .Where(n => !n.Snoozed)
    .ExecuteUpdate(s => s.SetProperty(
        n => n.Snoozed,
        n => true));

all is well.

Now I need to be able to update properties dynamically, so I would provide a Dictionary<string, object> as input, and I need to update every property identified by its dictionary key and set it to the corresponding dictionary value.

Dictionary<string, object?> updates = new(); // filling omitted

foreach (var kvp in updates)
{
    // set the property kvp.Key to value kvp.Value
}

How would I go about that? This is what I've come up with so far:

Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> setExpression = _ => _;

foreach (var propertyValue in updates.Values)
{
    var propertyExp = GetPropertyExpression<TEntity>(propertyValue.Key);
    setExpression = c => c.SetProperty(propertyExp, _ => propertyValue.Value);
}

result.Result = await items.ExecuteUpdateAsync(setExpression).ConfigureAwait(false);

private static Func<TEntity, object> GetPropertyExpression<TEntity>(string propertyName)
{
    var parameterExpression = Expression.Parameter(typeof(TEntity), "x");
    var memberExpression = Expression.Property(parameterExpression, propertyName);
    var lambda = Expression.Lambda<Func<TEntity, object>>(memberExpression, parameterExpression);
    return lambda.Compile();
}

While this executes, I get an exception:

Unable to cast object of type 'System.Linq.Expressions.TypedParameterExpression' to type 'System.Linq.Expressions.LambdaExpression'

I have updated [my previous answer](https://stackoverflow.com/a/74477243/10646316) and for now it works with dictionary. — Svyatoslav Danyliv, Aug 15 '23 at 12:35
@SvyatoslavDanyliv: your answer does indeed work. It just can't handle nullable types in the entity yet. — Stephan Steiner, Aug 15 '23 at 16:23
see my comment after your response. Managed to get it working with a small change — Stephan Steiner, Aug 15 '23 at 16:30

score 0 · Answer 1 · answered Aug 14 '23 at 17:50

Ah, okay. The error you're getting is because setProperty is expecting an expression that defines how to set the value, but it looks like you're mixing the creation with the use of a delegate.

Here is an example snippet I wrote that will get you back on track:

public void UpdateProperties(Dictionary<string, object?> updates)
        {
            var updateExpression = GetUpdateExpression(updates);
            var updateMethod = typeof(EntityFrameworkQueryableExtensions)
                .GetMethod("ExecuteSqlInterpolatedAsync", new[] { typeof(IQueryable<TEntity>), typeof(FormattableString) });

            var query = _context.Set<TEntity>().Where(e => true); //adjust if you need to
            var formattableString = FormattableStringFactory.Create(
                $"UPDATE [{{0}}] SET {updateExpression}",
                query.GetTableName());

            updateMethod?.MakeGenericMethod(typeof(int))
                .Invoke(null, new object[] { query, formattableString });
        }




private FormattableString GetUpdateExpression(Dictionary<string, object?> updates)
        {
            var assignments = new List<string>();

            foreach (var kvp in updates)
            {
                var propertyName = kvp.Key;
                var value = kvp.Value;
                var parameter = Expression.Parameter(typeof(TEntity), "e");
                var property = Expression.Property(parameter, propertyName);
                var assignment = Expression.Assign(property, Expression.Constant(value));
                var lambda = Expression.Lambda(assignment, parameter);

                assignments.Add(_context.Model.GetRelationalModel().GetSqlGenerator()
                    .GenerateTopLevelUpdate(property, lambda, _context.Model));
            }

            var updateExpression = string.Join(", ", assignments);
            return FormattableStringFactory.Create(updateExpression);
        }

class Program
    {
        static void Main(string[] args)
        {
            using var dbContext = new DynamicDbContext();
            var updater = new DynamicPropertyUpdater<Notification>(dbContext);

            var updates = new Dictionary<string, object?>
            {
                { "Snoozed", true },
                { "OtherProperty", "NewValue" }
            };

            updater.UpdateProperties(updates);
        }
    }

Here is also a relevant SO answer that might help with dynamically updating properties.

This answer is step back in time. EF Core 7 supports `ExecuteUpdate` over `IQueryable` with custom filter, for example. But you have proposed dynamic SQL generation which is less flexible. — Svyatoslav Danyliv, Aug 14 '23 at 18:14
Maybe I am just too old for all of the cool new stuff EF Core is coming out with xD — Corey Sutton, Aug 14 '23 at 18:28
@CoreySutton: are you sure you're on the right track? You're operating with Expression.Assign, which as far as I understand your code, assigns `Expression.Constant(value)`to `property`. With efcore7, we have `SetProperty` which takes two funcs - the first one selects the property from the Entity, the second one gives the value to assign to that property. So no need to write an Assignment Expression. Your linked SO answer would work for a regular update on the entity, but not `SetProperty` (and thus would require fetching the entiy - which is what I'm avoiding with `SetProperty`) — Stephan Steiner, Aug 15 '23 at 08:03
@StephanSteiner yep, you're right. damn. I do not think my answer works then. I am going to keep looking for an answer, but feel free to downvote the current answer in the meantime because it doesn't work for your question — Corey Sutton, Aug 15 '23 at 14:11

EF Core 7 dynamic bulk updates

1 Answers1