How to Bulk Update column of a table with Entity Framework Core or Dapper?

I need to know what's the best approach to update single column from all rows using EF Core or Dapper. I suppose this is called Bulk Update.

I tried EF Plus but I uninstalled after I read about the license trial and I'm looking for a free approach in either Dapper or Entity Framework.

I suppose although I thought it mattered if I had to write raw SQL or make a stored procedure.

Solution

With pure Dapper, it is simple -

string sql = "UPDATE Table SET Field1 = newvalue ....";
con.Execute(sql,.....);

Now, I am not sure why you involved EF here. EF is full ORM. I never used it; but I used its elder brother - NHibernate a lot.

Full ORMs do not support bulk (UPDATE/DELETE) operations under UoW. To make UoW work correctly, the entity must be loaded in memory. Then you can modify the entity the way you want. ORM tracks the changes. On flushing, changes are reflected to database.

So, you load all necessary entities using EF in memory. You loop through them to change the property and then flush the context.

Problem here is that, this is NOT a bulk update per say; this is called batch update instead. You are updating single record at a time. This badly hits the performance.

NHibernate provides alternative ways (HQL, raw SQL) to perform bulk operations. But, those APIs do not honor UoW.

With EF, you may use ExecuteStoreCommand

Executes an arbitrary command directly against the data source using the existing connection.

The store command is executed in the context of the current transaction, if a current transaction exists.

dbContext.ExecuteStoreCommand(sql, ....);

Just keep in mind that though this runs under the current transaction it runs out of UoW. That said, it does not track changes. The entities already loaded will be in inconsistent state then after if those were part of UPDATE command.