[SOLVED] Atomically increment a field using SubSonic 3 ActiveRecord

Atomically increment a field using SubSonic 3 ActiveRecord

I'm tring to increment a field in a MySQL database using SubSonic 3 ActiveRecord. In SQL, this is what I'm after:

UPDATE people SET messages_received=messages_received+1 WHERE people_id=@id_to;

I tried the following, but it didn't seem to work (messages_received always seemed to be 1):

_db.Update<person>()
    .Set("messages_received").EqualTo(x => x.messages_received == x.messages_received + 1)
    .Where(x => x.people_id == idTo)
    .Execute();

This did work:

string sql="UPDATE people SET messages_received=messages_received+1 WHERE people_id=@id_to";
var q=new SubSonic.Query.QueryCommand(sql, _db.Provider);
q.AddParameter("id_to", idTo);
q.Provider.ExecuteQuery(q);

So I have a solution, but I'm just wondering if it's possible to do this without resorting to plain SQL?

Answer. For reference, based on Rob's suggestion below::

_db.Update<person>()
    .SetExpression("messages_received").EqualTo("messages_received+1")
    .Where<person>(x=>x.people_id==idTo)
    .Execute();

Solution

You can use the old query tool for this and use "SetExpression":

db.Update("MyTable")
   .SetExpression("messages_received +1")
   .Where("people_id")
   .IsEqualTo(1)
   .Execute();

That's freehanded - but hopefully you get the idea :)