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