CODEWITHSUNDEEP
c#sql-serverentity-framework
CMS
CMS

Reputation: 3757

UPDATE Balance in Entity Framework using C#

I have a website that has balances & I used to update with a stored procedure:

UPDATE Account 
SET Balance = Balance + @Diff

And it works very well BUT now with C# and Entity Framework, I can only update the value by setting amount (loading value first).

I'm ok with loading value before the update BUT I don't like the generated SQL statement:

UPDATE Account 
SET Balance = 6.99

That sucks because if balance gets updated after read and before update (which can be 300ms in EF from load to save) then the balance my be changed in the process.

And to run within a transaction is not really recommended for EF since it causes dead locks.

So is there any way to do this correct with EF or do I need to update with the stored procedure?

Upvotes: 0

Views: 557

Answers (1)

Slava Utesinov
Slava Utesinov

Reputation: 13488

Try to use ConcurrencyCheckAttribute:

Model:

public class Account
{
    //other properties...
    [ConcurrencyCheck]
    public decimal Balance {get; set;}
}

Balance update logic:

while(true)
{
    balance += diff;
    try
    {       
        context.SaveChanges();
        break;
    }
    catch(DbUpdateConcurrencyException)
    {     
        //balance was updated by someone else,
        //so we will get it's last value and try to update again
        context.Entity(balance).Reload();
    }
}

Upvotes: 2

Related Questions