0

Is it possible to increment an integer in an Update query using Simple.Data?

My database is SQL Server, but I know Simple.Data is designed to be database agnostic so perhaps this query is not available in other providers.

The SQl query I am trying to generate would look like this:

UPDATE MyTable SET MyInt = MyInt + 1 WHERE Id = 123

Currently I have to do a SELECT and an UPDATE in Simple.Data to achieve the same thing:

var result = db.MyTable.Find(db.MyTable.Id == 123);
result.MyInt++;
db.MyTable.UpdateById(Id: result.Id, MyInt: result.MyInt );

I am wondering if there is a way to replicate the single SQL statement above.

Dave S
  • 1,403
  • 1
  • 15
  • 23

2 Answers2

1

You can do this:

db.MyTable.UpdateById(Id: 123, MyInt: db.MyTable.MyInt + 1);

Works on SQL Server, should work in all the SQL-based providers.

Mark Rendle
  • 9,274
  • 1
  • 32
  • 58
  • Thanks Mark. Should this work with InMemoryAdapter too? It seems to reset the value of MyInt to 0. – Dave S Sep 26 '13 at 12:31
  • 1
    It doesn't work with InMemoryAdapter right now. That feature is up for a total rewrite in 2.0 and should support almost everything. – Mark Rendle Sep 27 '13 at 15:22
0

I have found this does not work if the int is nullable and the current value is null. Nothing happens. But if I set the value to 0 then it works. So my int col now has a default of 0.

Norbert Norbertson
  • 2,102
  • 1
  • 16
  • 28