How to conditionally SetProperty with Entity Framework Core ExecuteUpdate?

Question

When querying a database with EF Core, it's easy to conditionally add .Where clauses to a query before executing the query, e.g.:

[HttpGet]
public async Task<List<Entity>> GetEntitiesAsync(string? property1, string? property2)
{
    var query = _context.Entities.AsNoTracking();
    if (property1 != null)
    {
        query = query.Where(e => e.Property1.Contains(property1));
    }
    if (property2 != null)
    {
        query = query.Where(e => e.Property2.Contains(property2));
    }
    return await query.ToListAsync();
}

However, when using .ExecuteUpdate, I can't see how you would conditionally chain .SetProperty clauses:

[HttpPatch("{id}")]
public async Task<IActionResult> UpdateEntityAsync(int id, Entity entity)
{
    var entitiesUpdated = await _context.Entities
        .Where(e => e.Id == id)
        .ExecuteUpdateAsync(s => s
            // How to conditionally chain SetProperty based on
            // if entity.Property1 and entity.Property2 are null?
            .SetProperty(e => e.Property1, entity.Property1)
            .SetProperty(e => e.Property2, entity.Property2)
    );

    return entitiesUpdated == 1 ? NoContent() : NotFound();
}

You can't use if statements inside the lambda. It needs to be a single expression that evaluates to a SetPropertyCalls<T>. Maybe you could manually create an expression tree, but wouldn't you need to build it on top of the parameter passed into the lambda? Is there an easy way I'm not seeing?

I created an issue for this on GitHub: github.com/dotnet/efcore/issues/32018 — Arad
– Arad, Commented Oct 11, 2023 at 5:21

Guru Stron · Accepted Answer · 2025-11-21 08:13:17Z

20

UPD

EF Core 10 (with .net-10) introduces a breaking change:

ExecuteUpdateAsync now accepts a regular, non-expression lambda

Which allows to dynamically construct the column setters (based on conditions) out of the box (though should break previous approach):

await context.Blogs.ExecuteUpdateAsync(s =>
{
    s.SetProperty(b => b.Views, 8);
    if (nameChanged)
    {
        s.SetProperty(b => b.Name, "foo");
    }
});

Pre EF Core 10

SetProperty allows passing expression to calculate the value. To dynamically combine SetProperty calls based on condition you can use ternary conditional operator :

var entitiesUpdated = await _context.Entities
    .Where(e => e.Id == id)
    .ExecuteUpdateAsync(s => s
         .SetProperty(e => e.Property1, e => entity.Property1 != null
              ? entity.Property1
              : e.Property1)
         .SetProperty(e => e.Property2, , e => entity.Property2 != null
              ? entity.Property2
              : e.Property2));

Though this will generate SQL like "PropertyX" = "e"."PropertyX" for cases when the source is null.

A bit harder but more "correct" approach to perform "conditional" batch update is to perform some expression trees manipulation:

// helper method to combine set expressions
static Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> AppendSetProperty<TEntity>(
    Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> left,
    Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> right)
{
    var replace = new ReplacingExpressionVisitor(right.Parameters, new []{left.Body});
    var combined = replace.Visit(right.Body);
    return Expression.Lambda<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>>(combined, left.Parameters);
}

// empty set expression
Expression<Func<SetPropertyCalls<Author>, SetPropertyCalls<Author>>> set = 
     calls => calls;

// conditionally append set for Property1 
if (entity.Property1 is not null)
{
    set = AppendSetProperty(set,
          s => s.SetProperty(e => e.Property1, entity.Property1));
}

// conditionally append set for Property2
if (entity.Property2 is not null)
{
    set = AppendSetProperty(set,
          s => s.SetProperty(e => e.Property2, entity.Property2));
}

edited yesterday

answered Apr 3, 2023 at 22:18

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Hanyou Hottie Over a year ago

This technically works, but it ends up generating SQL like this: UPDATE "Entities" AS "e" SET "Property1" = @__Property1 "Property2" = "e"."Property2" WHERE "e"."Id" = @__id Every column is either set to the new value or set to itself. Is there a way so only the columns to be changed are set?

Guru Stron Over a year ago

@HanyouHottie Sorry. Misunderstood the question, it is other way around, it's not database fields you need to check. Yes, it is, but will require expression tree manipulation. Will update the question in an hour or so.

Guru Stron Over a year ago

@Niksr yes it does) I have tested it myself and it clearly worked for OP. Can you please share a minimal reproducible example showing the problem (for example at github)

Niksr Over a year ago

You right, it works. I moved your AppendSetProperty into static method and must be made a mistake. Sorry and thank you for a great solution.

Guru Stron Over a year ago

@Adnan it should sufficient enough. Basically you build the set expression (similar to how you can build predicate for Where) and then call .ExecuteUpdateAsync(set). If you have question I can try to create a full blown minimal reproducible example later but not sure when I will have time.

|

Arad · Accepted Answer · 2024-06-14 13:46:05Z

Here's a simple helper I wrote that composes the expression tree "transparently" and enables these kinds of scenarios — while allowing you to use the same API shape, unlike Guru Stron's answer:

Usage:

int affectedCount = await dbContext.Books
    .Where(b => b.Id == someBookId)
    .ExecutePatchUpdateAsync(b =>
    {
        if (someCondition)
            b.SetProperty(b => b.Foo, "Foo");
        else
            b.SetProperty(b => b.Bar, "Bar");
    });

The implementation:

public static class QueryableExecutePatchUpdateExtensions
{
    public static Task<int> ExecutePatchUpdateAsync<TSource>(
        this IQueryable<TSource> source,
        Action<SetPropertyBuilder<TSource>> setPropertyBuilder,
        CancellationToken ct = default
    )
    {
        var builder = new SetPropertyBuilder<TSource>();
        setPropertyBuilder.Invoke(builder);
        return source.ExecuteUpdateAsync(builder.SetPropertyCalls, ct);
    }

    public static int ExecutePatchUpdate<TSource>(
        this IQueryable<TSource> source,
        Action<SetPropertyBuilder<TSource>> setPropertyBuilder
    )
    {
        var builder = new SetPropertyBuilder<TSource>();
        setPropertyBuilder.Invoke(builder);
        return source.ExecuteUpdate(builder.SetPropertyCalls);
    }
}

public class SetPropertyBuilder<TSource>
{
    public Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>> SetPropertyCalls { get; private set; } = b => b;

    public SetPropertyBuilder<TSource> SetProperty<TProperty>(
        Expression<Func<TSource, TProperty>> propertyExpression,
        TProperty value
    ) => SetProperty(propertyExpression, _ => value);

    public SetPropertyBuilder<TSource> SetProperty<TProperty>(
        Expression<Func<TSource, TProperty>> propertyExpression,
        Expression<Func<TSource, TProperty>> valueExpression
    )
    {
        SetPropertyCalls = SetPropertyCalls.Update(
            body: Expression.Call(
                instance: SetPropertyCalls.Body,
                methodName: nameof(SetPropertyCalls<TSource>.SetProperty),
                typeArguments: new[] { typeof(TProperty) },
                arguments: new Expression[] {
                    propertyExpression,
                    valueExpression
                }
            ),
            parameters: SetPropertyCalls.Parameters
        );

        return this;
    }
}

Does not compile, syntax error in line Action<SetPropertyBuilder<TSource>> setPropertyBuilder

robert · Accepted Answer · 2024-06-11 08:35:40Z

0

You can use code generator scriban:

var template = Template.Parse(@"
{
    await _context.Entities
        .Where(e => e.Id == id)
        .ExecuteUpdateAsync(s =>    
}");

if (entity.Property1 != null)
{
    template += "s.SetProperty(e => e.Property1, entity.Property1)";
}
if (entity.Property2 != null)
{
    template += "s.SetProperty(e => e.Property2, entity.Property2)";
}
var entitiesUpdated = = template.Render();

[HttpPatch("{id}")]
public async Task<IActionResult> UpdateEntityAsync(int id, Entity entity)
{
return entitiesUpdated == 1 ? NoContent() : NotFound();
}

This is only a draft how it could be done.

edited Jun 11, 2024 at 8:35

answered Jun 10, 2024 at 14:44

robert

493 bronze badges

Comments

SternK · Accepted Answer · 2023-10-21 21:46:57Z

-1

This method works for me in my MAUI .NET 7 proyect: On my TryLogin() method I use the line await Constants.UpdateEntityAsync(_context, data); for dynamic update

    var TokenResult = await _loginService.LoginBusiness(UserValue,PassValue, AliasValue);

    if (TokenResult != null && TokenResult.token != null)
    {

        UtilDataModel data = await _context.UtilData.FirstAsync();

        data.token = TokenResult.token;
        data.expiration = TokenResult.expiration;
        data.businessId = TokenResult.businessId;
        data.userId = TokenResult.userId;
        data.depositId = TokenResult.depositId;
        data.apiVersion = TokenResult.apiVersion;
        data.empresa = TokenResult.empresa;
        data.nombreUsuario = UserValue;
        data.basedatos = TokenResult.basedatos;

        await Constants.UpdateEntityAsync(_context, data);
        return true;
    }
    else
    {
        return false;
    }
}
else
{
    await Shell.Current.DisplayAlert("Error", "Debe escribir sus datos para ingresar!!", "ok");
    return false;
}

}

And here in a static class I put the generic method to do dynamic update:

Generic method:

Then with this code now I can use the generic method to update any object

edited Oct 21, 2023 at 21:46

SternK

13.3k34 gold badges40 silver badges55 bronze badges

answered Oct 17, 2023 at 23:18

Santi Llanes

1

1 Comment

Niksr Over a year ago

Your answer does nothing with the question. ExecueUpdate is a "new" performant way to update values in tables without retrieving entitles in advance. It's rough equivalent of raw SQL requests. In your answer you use traditional (in terms of EF) way to first retrieve and then update entity. The difference is performance: ExecueUpdate is 10x faster.

Collectives™ on Stack Overflow

How to conditionally SetProperty with Entity Framework Core ExecuteUpdate?

4 Answers 4

7 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related