CODEWITHSUNDEEP
.netentity-frameworktransactionsentity-framework-coreunique-constraint
grokky
grokky

Reputation: 9265

Changing unique constrained column in EF Core using a transaction

I'm using EF Core (inside ASP.NET Core, and my DbContext lives per request/scope).

My Employee entity has this property:

public bool? IsBoss { get; set; }

and this config:

entityBuilder.Property(b => b.IsBoss).IsRequired(false);
entityBuilder.HasIndex(b => b.IsBoss).IsUnique();

This creates a filtered index, so there can be only one true, one false, but many nulls.

My app requires that I always have exactly one employee with IsBoss==true.

Suppose I want to swap two employees around.

employee1.IsBoss = null;
employee2.IsBoss = true;
context.SaveChanges();

This throws a unique constraint violation exception.

I can fix that by wrapping it in a transaction:

using (var transaction = context.BeginTransaction())
{
  try
  {
    employee1.IsBoss = null;
    context.SaveChanges();

    employee2.IsBoss = true;
    context.SaveChanges();

    transaction.Commit();
  }
  catch
  {
    transaction.Rollback();
  }
}

My question is: why does the first approach fail? I thought EF Core automatically wraps everything in a transaction. Why must I use a transaction?

Upvotes: 1

Views: 517

Answers (1)

Smit
Smit

Reputation: 2459

The first approach fails due to reason different than transactions. Tracking issue on EF repo which covers scenario same as yours.

When SaveChanges is called, EF processes the changes and computes commands to be sent to database. This set of commands can have dependency. Like in your case, you need to set value to null for employee1 before you can set value to true for employee2. EF does sorting of commands to find out order in which they need to be executed. EF did this sorting based on the foreign key constraints. But as that issue reported, we did not account for unique index so commands were being sent in wrong order causing unique constraint violation.

The issue is already fixed in current codebase. It will be available in next public release. Meanwhile as a work-around, you need to call SaveChanges twice as you are doing in your second code. Calling SaveChanges multiple times you can control ordering of commands being sent to database. You don't need to wrap it in a transaction unless you want both the changes to be one atomic operation. Each SaveChanges have own transaction unless user starts one.

Upvotes: 1

Related Questions