How to do a +1 on a counter field with only one query to the db?

Question

I have a field where i'm counting the total number of subscriptions to an user. Right now i update this field like this:

using (var context = new AppDbContext())
{
    var foundEntry = context.Users.Find(id);
    if (foundEntry != null)
    {
        foundEntry.TotalSubscriptions = foundEntry.TotalSubscriptions + 1;
        context.Entry(foundEntry).CurrentValues.SetValues(foundEntry);

        var result = context.SaveChanges();
    }
}

But this way i have to make 2 queries to the db. One to get the current total count and one to update the value.

Is there a way to do this with only one query to the db with entity framework?

I wanted to try something like this:

var user = new User() { Id = userId, TotalSubscriptions = currentS + 1 };
db.Users.Attach(user);
db.Entry(user).Property(x => x.TotalSubscriptions).IsModified = true;

but the problem i'm facing is that i would have to get the current count first but don't know how to do in in a single query. I think that 1 query for this is possible with a raw SQL statement/query.

I'm trying to archive something like this: https://stackoverflow.com/a/2762856/1286942 but with entity framework.

UPDATE

I also tried something like this:

var user = new User() { Id = userId };
db.Users.Attach(user);
user.TotalSubscriptions = context.Entry(user)
.Property(u => u.TotalSubscriptions).OriginalValue + 1;
db.Entry(user).Property(x => x.TotalSubscriptions).IsModified = true;

But the problem is that the .OriginalValue and the .OriginalValue always return 0. So the TotalSubscriptions field is always updated to -1 or 1.

UPDATE #2

Right now i see only this as an option:

var numberOfUpdatedRows = context.Database
.ExecuteSqlCommand("UPDATE dbo.Users 
SET TotalSubscriptions = TotalSubscriptions + 1 
WHERE id = " + id + "");

Answer 1

The basic approach for this type of code is to use a stored procedure for your SQL code, and pass in a variable value to the procedure. Here is a possible solution for your problem.

CREATE PROCEDURE AdjustSubscriptions 
    @userid int,
    @adjustment int
AS
BEGIN
    DECLARE @subs int
    SET NOCOUNT ON

    UPDATE UserInfo
    SET @subs = currentS + @adjustment, currentS = @subs
    WHERE userID = @userid

    SELECT @subs AS SubscriptionCount
END

This will SELECT the currentS value into a variable, adjust it as specified by the value in @adjustment, (Adding three subscriptions? Pass a three. Removing three subscriptions? Pass a negative three) then UPDATE the value to the saved row. Finally, a row containing only the adjusted subscriptionCount value is returned.

The stored procedure could be rewritten to return the value in an OUTPUT variable (if implemented correctly, you will simply get the value back into a variable instead of having to deal with a recordset), or even use the statement RETURN @subs at the end of the procedure to return the adjusted value as a return value. I highly recommend that you do NOT use the RETURN @subs method, since it breaks on a return of a string or a value outside of smallint.

I leave it to you to implement the entity framework code to handle the stored procedure. I don't believe that you can do the same direct manipulation of the value using one call within the entity framework, but I'm perfectly willing to be shown otherwise.

Happy Coding.