CODEWITHSUNDEEP
c#mysqlentity-framework
Bart Calixto
Bart Calixto

Reputation: 19725

Increase or update amount on Entity Framework

how can I translate this:

INSERT INTO test (id, amount) VALUES(1, 5) 
ON DUPLICATE KEY 
UPDATE amount=amount + VALUES(amount)

into Entity Framework with mysql query?

Upvotes: 1

Views: 621

Answers (2)

CodeCaster
CodeCaster

Reputation: 151674

Extract the logic. Like explained in the manual:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;    

// And

UPDATE table SET c=c+1 WHERE a=1;

Your query says: insert a row with id 1 and amount 5, but if that exists, increase the amount of the row with id 1 by 5.

MS-SQL does, as far as I know not support such a statement, so you'll have to do the work yourself:

public void InsertOrUpdateTest(Test input)
{
    var entity = _dbContext.Test.FirstOrDefault(t => t.ID == input.ID);

    // If that record doesn't exist, create it and add it to the DbSet<Test> Tests
    if (entity== null)
    {
        entity= new Test(id = input.ID);
        _dbContext.Tests.Add(entity);
    }
    
    // Increase the amount. For a new entity this will be equal to the amount,
    // whereas an already existing entitiy gets its current value updated.
    entity.Amount += input.Amount;
    
    _dbContext.SaveChanges();
}

Upvotes: 2

Alden
Alden

Reputation: 6713

You could try something like this:

var id = 1;
var amount = 5;

var existing = db.Test.SingleOrDefault(x => x.Id == id);

if (existing != null)
{
    existing.Amount = existing.Amount + amount;
}
else 
{
    db.Test.Add(new Test{Id=id,Amount=amount});
}

db.SaveChanges();

Upvotes: 1

Related Questions