Handle duplicate key violations in EntityFramework Core

Question

According to other questions (here and here) it's possible to catch unique key violations in Entity Framework 6 by catching the thrown exception and inspecting it's InnerException.

When calling DbContext.SaveChanges() with a duplicate set of data, an exception is thrown but it is a fairly standard InvalidOperationException, and it's InnerException is null.

How can I detect duplicate key violations in Entity Framework Core?

Update with more context (pun intended)

The specific violation I'm trying to catch/detect is when adding a link between two entities (Team and User) that are joined by a many-to-many relationship.

System.InvalidOperationException: The instance of entity type 'TeamUser' cannot be tracked because another instance of this type with the same key is already being tracked. When adding new entities, for most key types a unique temporary key value will be created if no key is set (i.e. if the key property is assigned the default value for its type). If you are explicitly setting key values for new entities, ensure they do not collide with existing entities or temporary values generated for other new entities. When attaching existing entities, ensure that only one entity instance with a given key value is attached to the context.

User entity class:

public class User
{
    [Key]
    public string Name { get; set; }

    public ICollection<TeamUser> TeamUsers { get; set; }
}

Team entity class:

public class Team
{
    [Key]
    public string Id { get; set; }

    [Required]
    public string Name { get; set; }

    public ICollection<Template> Templates { get; set; }
    public ICollection<Checklist> Checklists { get; set; }

    public ICollection<TeamUser> TeamUsers { get; set; }
}

TeamUser entity class:

public class TeamUser
{
    public string TeamId { get; set; }
    public Team Team { get; set; }

    public string UserName { get; set; }
    public User User { get; set; }
}

My DbContext subclass configures the many-to-many relationship between Teams and Users:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var teamUserEntity = modelBuilder.Entity<TeamUser>();

    teamUserEntity
        .HasKey(tu => new { tu.TeamId, tu.UserName });

    teamUserEntity
        .HasOne(tu => tu.Team)
        .WithMany(t => t.TeamUsers)
            .HasForeignKey(tu => tu.TeamId);

    teamUserEntity
        .HasOne(tu => tu.User)
        .WithMany(u => u.TeamUsers)
        .HasForeignKey(tu => tu.UserName);
}

EF Core has generated the TeamUser table as follows:

CREATE TABLE "TeamUser" (
    "TeamId" TEXT NOT NULL,
    "UserName" TEXT NOT NULL,
    CONSTRAINT "PK_TeamUser" PRIMARY KEY ("TeamId", "UserName"),
    CONSTRAINT "FK_TeamUser_Teams_TeamId" FOREIGN KEY ("TeamId") REFERENCES "Teams" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_TeamUser_Users_UserName" FOREIGN KEY ("UserName") REFERENCES "Users" ("Name") ON DELETE CASCADE
);
CREATE INDEX "IX_TeamUser_UserName" ON "TeamUser" ("UserName");

Have you set your unique constraint set up in your db context ? — H. Herzl, May 28 '17 at 16:48
I mean something like this: entity.HasAlternateKey(p => new { p.ProductName }).HasName("U_ProductName"); — H. Herzl, May 28 '17 at 16:59
@H.Herzl please check the updated question, I've added the relevant entity classes and `OnModelCreating` method. — Steve Wilford, May 28 '17 at 17:01
Sorry, I didn't explain in a good way, as I understand your question you need to catch a unique constraint violation; so my suggestion is add the configuration for your unique constraint in your DbContext and try the operations in order to review the exception details, this makes sense? — H. Herzl, May 28 '17 at 17:09
@H.Herzl As far as I know I've got the unique constraints setup correctly. I've added the relevant table schema and it shows the primary key is a compound key of `TeamId` and `UserName`. The issue is not with preventing duplicates, but detecting when the system tries to insert duplicates. — Steve Wilford, May 28 '17 at 17:19
@SteveWilford - I am getting duplicate key violation inner exception: `SqlException: Violation of PRIMARY KEY constraint 'PK_TeamMembers'. Cannot insert duplicate key in object 'dbo.TeamMembers'. The duplicate key value is (69f55c9b-5ed2-4abf-a008-c465d1920994, Alice). The statement has been terminated.` (BTW: I renamed `User` model to `Member` and renamed `TeamUser` to `TeamMember`) — kimbaudi, May 29 '17 at 06:59
@kimbaudi I am using EF Core 1.1.2, can you confirm you are using the same version. If so, other than the renaming of a model have you done anything else that I have not shown? — Steve Wilford, May 29 '17 at 07:17
@SteveWilford - I am using Microsoft.EntityFrameworkCore v1.1.2. I was able to figure out why you are getting invalid operation exception instead of duplicate key violation exception. Please see my answer below. — kimbaudi, May 29 '17 at 17:04

score 7 · Answer 1 · answered Mar 15 '20 at 22:06

To handle cases like this I have written an open source library: EntityFramework.Exceptions It allows you to catch types exceptions like this:

using (var demoContext = new DemoContext())
{
    demoContext.Products.Add(new Product
    {
        Name = "a",
        Price = 1
    });

    demoContext.Products.Add(new Product
    {
        Name = "a",
        Price = 1
    });

    try
    {
        demoContext.SaveChanges();
    }
    catch (UniqueConstraintException e)
    {
        //Handle exception here
    }
}

All you have to do is install it from Nuget and call it in your OnConfiguring method:

class DemoContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<ProductSale> ProductSale { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseExceptionProcessor();
    }
}

kimbaudi · Answer 2 · 2017-05-29T17:03:45.313

The reason you are unable to detect duplicate key violation is because you are using a single instance of dbcontext to save duplicate data. Let me explain with a sample controller:

MyController.cs

public class MyController : Controller
{
    private readonly MyDbContext _context;

    public MyController(MyDbContext context)
    {
        _context = context;
    }

    public IActionResult AddFirst()
    {
        var user = new User
        {
            Name = "Alice"
        };
        _context.Users.Add(user);

        var team = new Team
        {
            Id = "uniqueteamid",
            Name = "A Team"
        };
        _context.Teams.Add(team);

        var teamuser1 = new TeamUser()
        {
            User = user,
            Team = team
        };
        _context.TeamUsers.Add(teamuser1);

        _context.SaveChanges();

        return View();
    }

    public IActionResult AddSecond()
    {
        var teamuser2 = new TeamUser()
        {
            UserName = "Alice",
            TeamId = "uniqueteamid"
        };
        _context.TeamUsers.Add(teamuser2);

        _context.SaveChanges();

        return View();
    }

    public IActionResult AddFirstAndSecond()
    {
        var user = new User
        {
            Name = "Bob"
        };
        _context.Users.Add(user);

        var team = new Team
        {
            Id = "anotherteamid",
            Name = "B Team"
        };
        _context.Teams.Add(team);

        var teamuser1 = new TeamUser()
        {
            User = user,
            Team = team
        };
        _context.TeamUsers.Add(teamuser1);

        var teamuser2 = new TeamUser()
        {
            User = user,
            Team = team
        };
        _context.TeamUsers.Add(teamuser2);

        _context.SaveChanges();

        return View();
    }

    public IActionResult AddFirstAndSecondAgain()
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplication1;Trusted_Connection=True;MultipleActiveResultSets=true");

        using (var context = new MyDbContext(optionsBuilder.Options))
        {
            var user = new User
            {
                Name = "Cat"
            };
            context.Users.Add(user);
            context.SaveChanges();
        }

        using (var context = new MyDbContext(optionsBuilder.Options))
        {
            var team = new Team
            {
                Id = "andanotherteamid",
                Name = "C Team"
            };
            context.Teams.Add(team);
            context.SaveChanges();
        }

        using (var context = new MyDbContext(optionsBuilder.Options))
        {
            var teamuser1 = new TeamUser()
            {
                UserName = "Cat",
                TeamId = "andanotherteamid"
            };
            context.TeamUsers.Add(teamuser1);
            context.SaveChanges();
        }

        using (var context = new MyDbContext(optionsBuilder.Options))
        {
            var teamuser2 = new TeamUser()
            {
                UserName = "Cat",
                TeamId = "andanotherteamid"
            };
            context.TeamUsers.Add(teamuser2);
            context.SaveChanges();
        }

        return View();
    }
}

In this controller, there are 4 action methods: AddFirst, AddSecond, AddFirstAndSecond, and AddFirstAndSecondAgain.

Case 1 (AddFirst and AddSecond):

Suppose AddFirst is called first. This will create a new User, a new Team, and a TeamUser. Now if AddSecond is called afterwards, this will attempt to add a duplicate TeamUser and throw a duplicate key violation exception. The reason is that the second call to insert duplicate TeamUser is using a different instance of dbcontext than the first call to insert TeamUser.

Case 2 (AddFirstAndSecond):

Suppose you call AddFirstAndSecond. This will throw an invalid operation exception. Why? Because you are using a single instance of dbcontext to add both the first TeamUser and the second duplicate TeamUser. Entity Framework Core is already tracking the first TeamUser so it cannot track the second duplicate TeamUser.

Case 3 (AddFirstAndSecondAgain):

If you really need to add duplicate TeamUser in a single action method, you need to use different instances of dbcontext when adding each TeamUser. Take a look at AddFirstAndSecondAgain action method. This will also throw a duplicate key violation exception because you are using different instances of dbcontext to add the first and second duplicate TeamUser.

Apologies but I'm not really following what you're suggesting I need do. Could you take a look at the [sample project](https://github.com/NxSoftware/EFCoreDuplicateRecord) I've pushed to Github and give some more pointers? — Steve Wilford, May 29 '17 at 11:17
When you add something to DbContext using `Add` method, context starts tracking it (to monitor if what needs to be saved when `SaveChanges` is called). If you try to add same instance multiple times to context, then EF throws exception that it cannot track the new entities. It is like unique constraint violation happening inside tracked entities in single DbContext instance. As a pointer, you should look for where the exception is arising and what is the entity which is being added twice in same context instance. That would help you locate what added it twice. — Smit, Jun 02 '17 at 01:37

Handle duplicate key violations in EntityFramework Core

2 Answers2