CODEWITHSUNDEEP
entity-frameworklinq
Harald Coppoolse
Harald Coppoolse

Reputation: 30492

Entity Framework: add value to existing record, or insert record

A nice puzzle for the Entity-Framework Guru's out here!

I have a collection of customers. Each customer has some expenses. I need to remember the total of the expenses per day.

In entity framework this is fairly straightforward:

public class Customer
{
    public int CustomerId {get; set;}
    public string Name {get; set;} // and other name/address fields

    public ICollection<Expense> ExpenseHistory{get; set;}
}

public class Expense
{
    public int ExpenseId {get; set;}
    public int CustomerId {get; set;}   // foreign key to customer
    public Customer {get; set;}

    public DateTime Date {get; set;}
    public decimal Total {get; set;}     // total expenses on date
}

public class ExpenseContext : DbContext
{
    public DbSet<Customer> Customers {get; set;}
    public DbSet<Expense> Expenses {get; set;}
}

I am certain that the combination (Expense.CustomerId, Expense.Date) is unique: for every date I only have one expense-total per customer. So I could use this combination as a primary key instead of ExpenseId. But that's another discussion.

A process sends me enumerations of messages: "the customer with this customerId just spent an amount of Value on date Date."

My process needs to check if the customer already spent something on Date.

Is it possible to do this in one query or do I have to check existence of each Expense and either add the expense value or create it? Apart from that the latter is slow, there is the possibility of race conditions: while I just found out a record does not exist, someone else might have created it. Or while I am just adding a value to an existing record, someone else might already have increased the value of the existing record.

The last method would be like:

public void ProcessExpenses(IEnumerable<Expense> expenses)
{
    using (var dbContext = ...)
    {
        foreach (var expense in expenses)
        {
            var existingExpense = dbContext.Expenses.SingleOrDefault(item =>
                item.CustomerId == expense.CustomerId &&
                item.Date == expense.Date);
             if (existingExpense != null)
             {   // already something spent on Date. Add the value:
                 existingExpense.Total += expense.Total;
             }
             else
             {   // nothing spent by customerId on Date yet. Add expense:
                 dbContext.Add(expense);
             }
             dbContext.SaveChanges();
        }
    }
}

The above is a bit simplified, the enumerable is in fact a different class that contains the spent customerId / date / value, but you get the gist.

Upvotes: 0

Views: 239

Answers (1)

Sergey L
Sergey L

Reputation: 1492

You cannot get it with EF 6. Assuming that you working with MS SQL, you can use this query to achieve what you want.

var result = ((IObjectContextAdapter)ExpenseContext).ObjectContext.ExecuteStoreQuery<Expense>(
                    @"  MERGE [Expense] AS target
                USING (select @CustomerId as CustomerId, @Date as Date, @Total as Total ) AS source
                ON (target.CustomerId = source.CustomerId and target.Date = source.Date)
                WHEN MATCHED THEN
                UPDATE
                SET Total = Total+source.Total
                WHEN NOT MATCHED THEN
                INSERT (CustomerId, Date, Total ) 
                VALUES (source.CustomerId, source.Date, source.Total)
                OUTPUT inserted.CustomerId, inserted.Date, inserted.Total;",
                    new SqlParameter("@CustomerId", SqlDbType.Int) { Value = ..},
                    new SqlParameter("@Date", SqlDbType.Date) { Value = ..},
                    new SqlParameter("@Total", SqlDbType.Decimal) { Value = ..}
                    );

If you don't need the resulting entity, you may replace this call with

ExpenseContext.Database.ExecuteSqlCommand("MERGE ...", new SqlParameter(....)

If you use other SQL dialects, check for the UPSERT command or something similar to MERGE.

Also, be careful about comparing valued of the DATE type when you provide them from .NET code to SQL, make sure they do not have TIME part. In .NET they are represente public DateTime Date {get; set;} as DateTime that has TIME part.

Upvotes: 1

Related Questions