EF Core 5.0: How to use SavePoints

In this post, let's have a look at SavePoints that was introduced with EF Core 5.0.

Savepoints are basically points within a database transaction that may later be rolled back to if an error occurs or for any other reason. Let's go by an example.

Consider the below simple MyDbContext.

public class MyDbContext : DbContext
{
    public DbSet<Category> Categories { get; set; }
 
    public DbSet<Product> Products { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(@"Data Source=.;Initial Catalog=EfCore5;Integrated Security=True")
            .EnableSensitiveDataLogging()
            .LogTo(Console.WriteLine, LogLevel.Information);
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Category>(builder =>
        {
            builder
                .HasMany(x => x.Products)
                .WithOne()
                .HasForeignKey(x => x.CategoryId);
        });
    }
}
 
public class Category
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public ICollection<Product> Products { get; set; }
}
 
public class Product
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public int CategoryId { get; set; }
}

And I am inserting some data as below.

using var context = new MyDbContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
 
Category category = null;
Product product = null;
 
using IDbContextTransaction transaction = context.Database.BeginTransaction();
try
{
    category = new Category() { Name = "Some Category" };
    context.Categories.Add(category);
    await context.SaveChangesAsync();
 
    await transaction.CreateSavepointAsync("CategoryCreated");
 
    // Setting incorrect CategoryId FK, this will throw FK constraint exception
    product = new Product { Name = "Some Product", CategoryId = 999 };
    context.Products.Add(product);
    await context.SaveChangesAsync();
 
    await transaction.CommitAsync();
}
catch (Exception)
{
    await transaction.RollbackToSavepointAsync("CategoryCreated");
 
    // Remove the invalid existing product
    context.Products.Local.Remove(product);
    product = new Product { Name = "Some Product", CategoryId = category.Id };
    context.Products.Add(product);
 
    //// Update/fix the invalid existing product
    //context.Products.Local.First(x => x.Name == product.Name).CategoryId = category.Id;
 
    await context.SaveChangesAsync();
 
    await transaction.CommitAsync();
}

First I am creating a Category and then I am inserting a Product. The Product save will throw an exception because I am setting an invalid CategoryId. Then inside the catch block, I am rolling back the transaction to the SavePoint I created when the Category is created ("CategoryCreated") and retrying to insert the Product. An important thing to note here is only the transaction is getting rolled back, whatever I have added to the DbContext, is staying as it is. So we MUST either remove the invalid existing product and then re-add or update/fix the invalid existing product.

Special thanks to Dr. Holger Schwichtenberg for helping me to understand how this works when I raised an issue: https://github.com/dotnet/efcore/issues/24862.

More reading: Using Transactions: SavePoints

Hope this helps.

Happy Coding.

Regards,

Jaliya