Concurrency Handling in Entity Framework Core with Transactions and Master Data

Binu S 0 Reputation points
2024-06-20T13:55:57.4333333+00:00

Question:

We have two tables:

Orders (transaction table): Stores order details and references a customer using CustomerID.

Customers (master table): Stores customer information.

The Orders table has a RowVersion column for optimistic locking.

The Customers table lacks a RowVersion column.

The Problem:

If a user modifies customer data concurrently while another user creates an order referencing that customer, the following might occur:

The order creation succeeds, but it references outdated customer information, leading to data inconsistency or exception.

The Question:

How does latest Entity Framework .net core handle concurrency in this scenario?

What are best practices and strategies to address this issue?

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,385 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,324 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,629 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 61,266 Reputation points
    2024-06-20T15:36:40.32+00:00

    if you are not setting a blocking isolation level and you don't have a concurrency column defined, than its last update wins.

    the best practice, if last one wins is not an option, would be to add a concurrency column.


  2. Michael Taylor 51,341 Reputation points
    2024-06-20T16:09:23.6066667+00:00

    The order creation succeeds, but it references outdated customer information, leading to data inconsistency or exception.

    Unless you're doing something wrong that isn't possible. Let's imagine that you have this code.

    var customer = await _context.Customers.FindAsync(10);
    
    customer.Orders.Add(new Order());
    
    await _context.SaveChangesAsync();
    

    Nothing changed on customer so there would be no changes saved to the Customers table. There wouldn't be any inconsistency or exception. The new order would be added to the Orders table with a reference to the existing customer. Of course if someone else made a change to Customer after you grabbed the data from the DB but before you saved it is inconsistent but saving your changes wouldn't change anything. It wouldn't update anything (because nothing changed as far as it knows) and it won't fetch the updated data (because it doesn't know to).

    If you want the above scenario to "fail" if the customer is changed outside this call then you would need to add a concurrency token AND mark the object as changed. Normally EF tracks an object changed automatically but if you want to treat the object as modified without actually changing anything then you'd need to change its state. But honestly there is no benefit in doing that.

    Now, if you do make a change to customer then things change.

    var customer = await _context.Customers.FindAsync(10);
    customer.Changed = "Something";
    
    customer.Orders.Add(new Order());
    
    await _context.SaveChangesAsync();
    

    Now when you save changes a transaction is created, customer is updated and the new order is added. If anything fails then everything is rolled back. Thus it is not possible to have an error saving customer but order still being saved.

    The only way to get inconsistencies is if you were to save your changes in separate calls to SaveChanges.