Entity structure updates one column, increasing the current value by one without selection

Question

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

Is there a way to do this without first loading all the records (thousands) into memory and numbering each record to enlarge 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 appropriate sql query to update the table.

For SQL, it looks like an UPDATE schemaname.TABLE SET COLUMN = COLUMN + 1 . for POSTGRESQL I need to specify the schema schema name, table name and column name twice: UPDATE "schemaname". "TABLE" SET "COLUMN" = "COLUMN" + 1 .

Answer 1

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 this helps.

Answer 2

With pure EF, you're right: you need to load objects one by one, set a property, and save. Very inefficient.

The 2 alternatives that I know of are as follows:

• Provide the source SQL to execute the UPDATE statement as you wish.
• Use the EntityFramework.Extended library ( https://github.com/loresoft/EntityFramework.Extended ), which supports both bulk UPDATE and DELETE with a natural EF feel.

Quote from your homepage:

Batch update and deletion

The current limitations of the Entity Framework are to update or delete an object that you must first retrieve into memory. Now in most scenarios this is fine. However, there are some senerons in which performance will suffer. In addition, for single deletes, an object must be retrieved before it is deleted, requiring two calls to the database. Batch updating and deletion eliminates the need to retrieve and load an object before modifying it.

Delete

 //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"});