Handle duplicate key violations in EntityFramework Core

.net-core c# entity-framework entity-framework-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");
1
5
5/28/2017 5:13:39 PM

Popular Answer

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.

3
5/29/2017 5:03:45 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow