How to increment a value using one command using entity framework

Question

How can i transform this sql query to an EF linq command

"update dbo.table set col1= col1 + 1 where Id = 27"

i want to execute this query using one command to avoid concurrency problems in case of another client modify the record in the same time

i'm looking for doing that using EF but in one command

i tried this but i'm looking for a better solution :

context.table1.FromSqlInterpolated($"update dbo.table  set col1= col1+ 1 where Id=27").FirstOrDefaultAsync();

I can propose only third party extension which can do that. – Svyatoslav Danyliv Jan 27 '22 at 15:33 — Svyatoslav Danyliv, Jan 27 '22 at 15:33
@SvyatoslavDanyliv i will take it – CSharp-n Jan 27 '22 at 15:35 — CSharp-n, Jan 27 '22 at 15:35

score 2 · Answer 1 · answered Jan 27 '22 at 15:46

2

I would propose to use linq2db.EntityFrameworkCore (note that I'm one of the creators)

Then you can do that with ease:

await context.table1.Where(x => x.Id == 27)
   .Set(x => x.Col1, prev => prev.Col1 + 1)
   .UpdateAsync();

answered Jan 27 '22 at 15:46

Svyatoslav Danyliv

13,476
1
9
23

Interesting. Does that actually translate into a single `UPDATE`, or is it still a `SELECT` then `UPDATE`? – Gabriel Luci Jan 27 '22 at 18:16
1

It translates exactly in one `UPDATE` statement. – Svyatoslav Danyliv Jan 27 '22 at 18:18

score 1 · Accepted Answer · answered Jan 27 '22 at 15:32

1

There are ways to update a column without first querying, but the problem you have is that the update is based on the existing value.

Entity Framework can't help you there. You can only do what you want with a direct SQL statement.

answered Jan 27 '22 at 15:32

Gabriel Luci

33,807
4
44
75

score 0 · Answer 3 · answered Jan 27 '22 at 21:15

0

Even the original SQL statement should be executed within a transaction if you want to be sure no other changes can occur between reading and updating the value. It's one SQL statement, but the db still has to read the value, increment and store.

answered Jan 27 '22 at 21:15

pjs

228
1
6

i'm using sql server and that query is done atomically, without using any transactions or locking – CSharp-n Jan 28 '22 at 07:37

How to increment a value using one command using entity framework

3 Answers3