0

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();
CSharp-n
  • 291
  • 2
  • 15

4 Answers4

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();
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 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.

Gabriel Luci
  • 38,328
  • 4
  • 55
  • 84
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.

pjs
  • 363
  • 1
  • 7
-1

Although it does not directly answer the question ("how to do it with one command"), I would like to post an EF Core solution here for reference, that tackles the problem.

  • Introduce a concurrency token on your entity
  • Add a retry policy when saving whenever a DbConcurrencyException occurs

This will guarantee that if the entity was modified between your read and write, you get an exception and you can retry the read-write operation, which will likely succeed next time. This will guarantee also, that no increment is lost due to two simultaneous reads.

Maxim Zabolotskikh
  • 3,091
  • 20
  • 21
  • Why the minus? It's a valid solution for the problem if you want to stay on the EF level without resorting to SQL. It's also better than "it doesn't work with EF". – Maxim Zabolotskikh Mar 14 '23 at 12:59
  • They want to *avoid* concurrency problems, not *solve* them. That's a major difference. – Gert Arnold Mar 14 '23 at 14:08
  • Do not agree. "avoid concurrency problems in case of another client modify the record in the same time" means "concurrency problems wouldn't lead to an erroneous state". So the "concurrency problem" is hereby avoided by accounting for and handling of concurrency acceptions. A handled exception is not a problem, it's a way of dealing with a problem. – Maxim Zabolotskikh Mar 14 '23 at 15:20
  • This one little sentence only provides some background. Remove it and the question doesn't change. They want a "better solution" for one-statement updates. It doesn't matter why. Side note, the link is invalid. – Gert Arnold Mar 14 '23 at 15:55
  • Yet the sentence is there and the „why“ always matters. Sometimes people ask „I want to do A using B, how do I do B?“ and the right answer is „Don‘t use B (or B cannot be done), you are better off by using C“. Keeping the answer because it‘s a valid alternative for people who want to implement increments with EF. – Maxim Zabolotskikh Mar 14 '23 at 19:00