Handling Concurrency in Entity Framework Core: A Guide for SQLite and Relational Databases

Concurrency issues can be a silent nemesis in any application dealing with data persistence. Recently, I found myself grappling with such issues while working on a .NET Core project using Entity Framework Core (EF Core) with a SQLite database. In this post, I'll share my journey of discovering, simulating, and resolving concurrency conflicts in EF Core with SQLite. I hope this guide will help you navigate similar challenges in your projects.

Introduction

Concurrency conflicts occur when multiple users or processes attempt to modify the same data at the same time. Without proper handling, this can result in lost updates and inconsistent data states. While EF Core provides mechanisms for concurrency control, special considerations are required when using SQLite due to its lack of native support for row versioning.

This guide demonstrates how to simulate a concurrency issue in EF Core with SQLite and provides a solution using a custom concurrency token and SQLite triggers.

Setting Up the Project

Step 1: Create a New .NET Core Console Application

Use the .NET CLI to create a new console application:

dotnet new console -n ConcurrencyDemo
cd ConcurrencyDemo

Step 2: Add Necessary Packages

Install the required NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package xunit
dotnet add package xunit.runner.visualstudio
dotnet add package Microsoft.NET.Test.Sdk

Step 3: Create Project Structure

Organize the project with the following folders:

• Models: Contains entity classes.

• Data: Includes the DbContext class.

• Tests: Holds the test classes.

Simulating a Concurrency Issue

Step 1: Define the Product Entity

Create a Product class in the Models folder:

namespace ConcurrencyDemo.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int StockQuantity { get; set; }
    }
}

Step 2: Set Up the AppDbContext

Create an AppDbContext class in the Data folder:

using Microsoft.EntityFrameworkCore;
using ConcurrencyDemo.Models;

namespace ConcurrencyDemo.Data
{
    public class AppDbContext : DbContext
    {
        public DbSet<Product> Products { get; set; }

        public AppDbContext(DbContextOptions<AppDbContext> options)
            : base(options)
        {
        }
    }
}

Step 3: Write a Test to Simulate the Issue

Create a ConcurrencyTests class in the Tests folder:

using Xunit;
using ConcurrencyDemo.Models;
using ConcurrencyDemo.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.Sqlite;
using System.Linq;

namespace ConcurrencyDemo.Tests
{
    public class ConcurrencyTests : IDisposable
    {
        private readonly DbContextOptions<AppDbContext> _options;
        private readonly SqliteConnection _connection;

        public ConcurrencyTests()
        {
            _connection = new SqliteConnection("Filename=:memory:");
            _connection.Open();

            _options = new DbContextOptionsBuilder<AppDbContext>()
                .UseSqlite(_connection)
                .Options;

            using var context = new AppDbContext(_options);
            context.Database.EnsureCreated();

            // Seed initial data
            if (!context.Products.Any())
            {
                context.Products.Add(new Product
                {
                    Id = 1,
                    Name = "Sample Product",
                    StockQuantity = 10
                });
                context.SaveChanges();
            }
        }

        [Fact]
        public void ConcurrencyIssue_ShouldOverwriteWithoutWarning()
        {
            using var context1 = new AppDbContext(_options);
            var product1 = context1.Products.First(p => p.Id == 1);

            using var context2 = new AppDbContext(_options);
            var product2 = context2.Products.First(p => p.Id == 1);

            product1.StockQuantity = 20;
            context1.SaveChanges();

            product2.StockQuantity = 30;
            context2.SaveChanges();

            using var verificationContext = new AppDbContext(_options);
            var finalProduct = verificationContext.Products.First(p => p.Id == 1);

            Assert.Equal(30, finalProduct.StockQuantity);
        }

        public void Dispose()
        {
            _connection.Close();
        }
    }
}

Observing the Problem

Running the test reveals that the second user's update overwrites the first user's changes without any warning. This demonstrates the concurrency issue where updates are silently lost.

Implementing Concurrency Control with SQLite

Step 1: Writing a Failing Test

To detect concurrency conflicts, write a test expecting a DbUpdateConcurrencyException:

[Fact]
public void ConcurrencyIssue_ShouldThrowException()
{
    using var context1 = new AppDbContext(_options);
    var product1 = context1.Products.First(p => p.Id == 1);

    using var context2 = new AppDbContext(_options);
    var product2 = context2.Products.First(p => p.Id == 1);

    product1.StockQuantity = 20;
    context1.SaveChanges();

    product2.StockQuantity = 30;

    // Expecting a concurrency exception
    Assert.Throws<DbUpdateConcurrencyException>(() => context2.SaveChanges());
}

When running this test, it fails because EF Core with SQLite does not detect concurrency conflicts by default.

Step 2: Implementing the Fix

Since SQLite doesn't support rowversion or timestamp types, implement a custom concurrency token using a Version property and a SQLite trigger.

Update Product Entity:

namespace ConcurrencyDemo.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public int StockQuantity { get; set; }

        // Concurrency token
        public int Version { get; set; }
    }
}

Configure the Version Property in AppDbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .Property(p => p.Version)
        .IsConcurrencyToken()
        .HasDefaultValue(0)
        .ValueGeneratedOnAddOrUpdate();
}

Add a SQLite Trigger:

In the ConcurrencyTests constructor, add the trigger just after context.Database.EnsureCreated();:

context.Database.ExecuteSqlRaw(@"
    CREATE TRIGGER SetProductVersionOnUpdate
    AFTER UPDATE ON Products
    BEGIN
        UPDATE Products
        SET Version = Version + 1
        WHERE rowid = NEW.rowid;
    END;
");

This trigger increments the Version column whenever a Product is updated, serving as a concurrency token.

Step 3: Verifying the Fix

Run the previously failing test again. It should now pass, confirming that the concurrency exception is correctly thrown.

Handling the Concurrency Exception

To gracefully handle concurrency conflicts, modify the test to catch the exception and resolve it.

Handling the Exception:

[Fact]
public void ConcurrencyIssue_HandleConflictGracefully()
{
    using var context1 = new AppDbContext(_options);
    var product1 = context1.Products.First(p => p.Id == 1);

    using var context2 = new AppDbContext(_options);
    var product2 = context2.Products.First(p => p.Id == 1);

    product1.StockQuantity = 20;
    context1.SaveChanges();

    product2.StockQuantity = 30;

    try
    {
        context2.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Simulate informing the user about the conflict
        Console.WriteLine("A concurrency conflict occurred. The data has been modified by another user.");

        // In a real application, you would inform the user and reload the latest data:
        var entry = ex.Entries.Single();
        var currentDatabaseValues = entry.GetDatabaseValues();

        // Log the current database values
        Console.WriteLine($"Current Database Stock Quantity: {(currentDatabaseValues.ToObject() as Product).StockQuantity}");

        // Refresh original values from the database
        entry.OriginalValues.SetValues(currentDatabaseValues);

        // Update only the StockQuantity property for retry
        entry.CurrentValues["StockQuantity"] = 35; // Simulating user's decision to retry with new value

        // Retry the save operation with the updated StockQuantity
        context2.SaveChanges(); // Try again with the updated value
    }

    using var verificationContext = new AppDbContext(_options);
    var finalProduct = verificationContext.Products.First(p => p.Id == 1);

    Assert.Equal(35, finalProduct.StockQuantity);
}

This code simulates resolving the conflict by updating the StockQuantity to a new value and retrying the save operation.

Conclusion

By implementing a custom concurrency token and using SQLite triggers, it's possible to handle concurrency conflicts effectively in EF Core with SQLite. This approach ensures that data integrity is maintained, and users are aware of any conflicts that occur during simultaneous data modifications.

Additional Notes for Other Databases

For databases like SQL Server or PostgreSQL, concurrency control is more straightforward due to native support for row versioning. You can use a [Timestamp] or rowversion attribute in your entity, and EF Core will handle concurrency tokens automatically.

Example for SQL Server:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int StockQuantity { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }
}

In such cases, triggers are not necessary, and EF Core manages concurrency conflicts seamlessly.

GitHub Repository

You can find the complete code for this project, including all the steps and commits, in my GitHub repository:

GitHub - Freeman-md/ConcurrencyDemo

Feel free to clone the repository, run the tests, and explore the code.

Final Thoughts

Handling concurrency in EF Core requires a clear understanding of how your database manages concurrent updates. While SQLite presents some challenges due to its lack of native row versioning, with a bit of ingenuity, we can implement effective concurrency control mechanisms.

I hope my journey and this guide help you navigate concurrency issues in your own projects. Remember, testing and simulating these scenarios are crucial to ensure data integrity in multi-user applications.

Happy coding!