CODEWITHSUNDEEP
c#entity-framework-core
Romulus Urakagi Ts'ai
Romulus Urakagi Ts'ai

Reputation: 3910

How to do "UPDATE table SET COL = COL + 1"

When I do this in EF Core 6:

int diff = 2;
var row = await db.Table.FirstOrDefaultAsync();  // foo = 3
row.foo += diff;
await db.SaveChangesAsync();

It translates to SQL UPDATE Table SET foo=5. If the database changes during my operation, it will set a wrong value.

I have heard that EF Core has collision prevention and will throw an exception in such a case, but if I use the SQL UPDATE Table SET foo=foo+2 can I even avoid the collision? If so, how to write this is EF Core 6?

Upvotes: 1

Views: 74

Answers (1)

JHBonarius
JHBonarius

Reputation: 11281

If you want to make raw SQL queries, i.e. something you can execute, EF Core is maybe not the best, as EF Core is an entity tracking framework. But it is supported.

You can also try bulk updates, but they have the same issue when an entity is updated during writing back:

foreach (var item in context.Table)
{
    item.foo++;
}

context.SaveChanges();

Note the text at the bottom:

Unfortunately, EF doesn't currently provide APIs for performing bulk updates. Until these are introduced, you can use raw SQL to perform the operation where performance is sensitive: context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");

You could also make an old-fashioned ADO call

string queryString = "UPDATE [table] SET [foo]=[foo]+2";
SqlCommand command = new SqlCommand(queryString, connection);
command.ExecuteNonQuery();

edit: note both Execute[...] calls return an int representing the number of lines affected.

Upvotes: 2

Related Questions