CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6
ydou
ydou

Reputation: 307

Entity framework update one column by increasing the current value by one without select

What I want to achieve is the simple sql query: UPDATE TABLE SET COLUMN = COLUMN + 1

Is there a way to make it happen without loading all records (thousands) to memory first and loop through each record to increment the column and then save it back?

EDIT

I tried raw sql and it worked. I have to decide the sql provider from the connection string and the database schema name from the connection context. After that, I will use the corresponding sql query to update the table.

For SQL, it looks like UPDATE schemaname.TABLE SET COLUMN = COLUMN + 1. for POSTGRESQL, I have to double quote schema name, table name and column name: UPDATE "schemaname"."TABLE" SET "COLUMN" = "COLUMN" + 1.

Upvotes: 12

Views: 7367

Answers (3)

Jervis
Jervis

Reputation: 124

You can use EFCore.BulkExtensions in EFCore, solution like this answer https://stackoverflow.com/a/42368027/8163839

context.Table.Where(x => x.Field1 > 0).BatchUpdate(y => new Table { Field2 = y.Field2 + 1 });

There is one small problem, the method parameters are Expression<Func<Table, Table>>, so you can only assign values to properties like above.

It is not DDD friendly.

Upvotes: 0

Varan Sinayee
Varan Sinayee

Reputation: 1153

Here is the solution. You can use the following code:

context.Table.Where(x => x.Field1 > 0).Update(y => new Table { Field2 = y.Field2 + 1 });

hope that it helps.

Upvotes: 4

sstan
sstan

Reputation: 36513

With pure EF, you are right: you have to load the entities one by one, set the property, and save. Very inefficient.

The 2 alternatives that I know of are:

  1. Provide raw SQL to execute the UPDATE statement as you want it.
  2. Use the EntityFramework.Extended library (https://github.com/loresoft/EntityFramework.Extended), which supports both bulk UPDATEs and DELETEs with a natural EF feel.

Quote from their main page:

Batch Update and Delete

A current limitations of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Now in most scenarios this is just fine. There are however some senerios where performance would suffer. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. Batch update and delete eliminates the need to retrieve and load an entity before modifying it.

Deleting

//delete all users where FirstName matches
context.Users.Where(u => u.FirstName == "firstname").Delete();

Update

//update all tasks with status of 1 to status of 2
context.Tasks.Update(
    t => t.StatusId == 1, 
    t2 => new Task {StatusId = 2});

//example of using an IQueryable as the filter for the update
var users = context.Users.Where(u => u.FirstName == "firstname");
context.Users.Update(users, u => new User {FirstName = "newfirstname"});

Upvotes: 2

Related Questions