ExecuteUpdate/Delete (AKA bulk update, without loading data into memory)

[akirayamamoto]

EF does not provide a batch update mechanism. A proposal is below.
Context.Customers.Update().Where.( c => c.CustType ==“New”).Set( x => x.CreditLimit=0)

Will you consider this feature?
More details here: https://entityframework.codeplex.com/workitem/52

---

Design summary: #795 (comment)

[rowanmiller]

This is something we do want to do in EF, but not for the initial RTM of EF Core. Moving to backlog.

[djechelon]

Bulk is currently available in EntityFramework.Extended by loresoft (source https://github.com/loresoft/EntityFramework.Extended, Nuget https://www.nuget.org/packages/EntityFramework.Extended/).

Maybe if license allows, you could think about grabbing code from that project

[abatishchev]

Unfortunately EF.Extended isn't testable at all and author isn't going to address that.

[djechelon]

I mostly agree. EF.Extended is indeed difficult to test, but not actually "not testable at all", as after several attempts I made my PR run on AppVeyor. I did my own fork and implemented my own methods for MySQL (only because the original project didn't work with it) because the author is not maintaining the project any more.

What about porting that code, and unit-testing it, in the original EF package?
Really, this and the duplicate #3303 are really "must-have" features, we can't have an ORM with the bottleneck of massive operations.

😭 😭

[ilmax]

Hi, I'm starting to investigate how complex would be to add support for set-based operation, and I'm actually only investigating Delete operation, I really would like to start a discussion with the team and the community members to start a discussion on how this feature can be designed.

Hoping that this will be achieved by EF Core, I'm posting my initial thoughts/questions on this subjects.

• Is this the right place or it's better to open a new issue?
• What set-based method should we expose? Delete, Update, and maybe Merge?
• What should be done with entities affected by the query that's actually being change tracked (i.e. already loaded from db by the same context instance)? Should this scenario be supported?
• To my understanding the update pipeline is generating SQL starting from a collection of ColumnModification instances, set-based operations should probably have a more powerful query generation capabilities, so we may need to mix somehow the query pipeline SQL generation capabilities with the update pipeline (or am I missing something?)
• What api should be exposed?

My initial proposal is the following:

Delete

void Delete<TEntity>(this DbSet<TEntity> query, Expression<Func<TEntity, bool>> condition)

usage: context.Customers.Delete(c => c.HasPendingPayments)

or

void Delete<TEntity>(this IQueryable<TEntity> query)

usage: context.Customers.Where(c => c.HasPendingPayments).Delete()

Update

void Update<TEntity>(this IQueryable<TEntity> query, Expression<Action<TEntity>>)

usage: context.Customers.Where(c => c.HasPendingPayments).Update(c => c.Status = CustomerStatus.Disabled)

or (as suggested above)

void Set<TEntity>(this IQueryable<TEntity> query, Expression<Action<TEntity>>)

usage: context.Customers.Where(c => c.HasPendingPayments).Set(c => c.Status = CustomerStatus.Disabled)

Merge

Any suggestion?

This is just a quick list of initial thoughts, I will update this one with new findings (if any 😄 ) during this initial investigation, but would like to get the discussion started.

[anpete]

To my understanding the update pipeline is generating SQL starting from a collection of ColumnModification instances, set-based operations should probably have a more powerful query generation capabilities, so we may need to mix somehow the query pipeline SQL generation capabilities with the update pipeline (or am I missing something?)

This makes sense. We likely need to introduce UpdateExpression etc. and then port the existing impl. to use it, too.

[abatishchev]

Looks good to me.
However, I'd stick with CRUD naming, i.e. Update(...).
Delete can have no overload so would be used on top of Where(...). Delete(predicate) would be a nice addition though.

[djechelon]

Replying to @ilmax

What should be done with entities affected by the query that's actually being change tracked (i.e. already loaded from db by the same context instance)? Should this scenario be supported?

I think the most difficult part is to guess whether some modifications propagate to entities already within the session. This is not currently addressed by EntityFramework.Extended in my knowledge, and is supposed to cause troubles.

Example:

using(DbContext context = new MyDbContext()) {

    Cat molly = context.Cats.Single(c => c.name == "Molly");

    molly.Weight += 1.5; // feed Molly

    // Now feed all Eleanor's cats
    context.Cats.Update(
        c => c.Owner.FirstName == "Eleanor" && c.Owner.LastName == "Abernathy", //Where clause Func<Cat,bool>
        c => c.Weight += 1.5 //mapping function Action<Cat>
    );

    AssertFalse(molly.IsOverweight);
}

The above differs whether Molly is Eleanor's cat or not. If we can assure (only under proper transaction synchronization constraints) that molly is always in sync with the database, one IMO feasible solution could be running the Update both to the SQL datasource and into the Entity cache. The second is terribly easy because no runtime-generated SQL is involved but a sane IList operation on the tracked entities that are stored in-memory.

---

The above was a simple scenario because EF knows we are working on the cats, so affected entities are of Cat. What if I try to cheat and run a trivial 1-affected-record statement such as

context.Cats.Update(
    c => c.Id = 657,
    c => c.Owner.Sick = true
);

The update is supposed to run on the Cats, but, in practice, modifications apply to Person class, as the generated SQL would be (UPDATE PERSONS..., see also here).

This scenario is really over-complicated, even though easy to run in-memory. I am speaking of it only to illustrate possible misuses of the bulk statement.

---

Final point of discussion for the update, I want to ask if multi-column updates are supported and how.

I mean

context.Customers.Update(c => c.HasPendingPayments, c => { c.Status = CustomerStatus.Disabled; c.Overdraft = 0; });

UPDATE CUSTOMERS SET STATUS = 'DISABLED', OVERDRAFT = 0
WHERE PENDING_PAYMENTS = 1;

Cheers,
ΕΨΗΕΛΩΝ

[djechelon]

Another point of discussion is that I like is the overload I have used in my examples: int Update(this IQueryable<TEntity> dataSet, Expression<Func<TEntity,bool>> whereCondition, Action<TEntity> setClause);

so

context.Customers.Update(c => c.HasPendingPayments, c => c.Status = CustomerStatus.Disabled);

Normally databases return the number of affected rows, and it is helpful to the application.

So here are 2 separate proposals:

1. Update (and thus Delete) should return int/long
2. Update may accept both Where and Set, even though it would become a direct redirect to context.Entities.Where(xx).Set(yy) in the real code

[ilmax]

@djechelon Good points, IMO at first we can reject set-based query if the context is already tracking object of the same type, unless it's proven to be very simple to propagate changes to tracked objects.

About the second example context.Cats.Update( c => c.Id = 657, c => c.Owner.Sick = true );
I propose to not support that case initially and evaluate based on community feedbacks if this is worth supporting or not.

Multi-column update IMO should be supported, I don't think that support it should be much different than what's required for single column ones.

@anpete

We likely need to introduce UpdateExpression etc. and then port the existing impl. to use it, too.

Is this change on your radar? Would you like me to open an issue to track this request?