Entity Framework Core: Npgsql.PostgresException: 23505: duplicate key value violates unique constraint

Question

I am getting the following exception when I try to insert an user from asp.net web api: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23505: duplicate key value violates unique constraint

Below are my entity models: Role and User. Where Each user is linked to one Role.

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime? LastUpdate { get; set; }
}

public class User
{
    public int Id { get; set; }
    public Role role { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailAddress { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public DateTime? DateCreated { get; set; }
    public DateTime? LastLogin { get; set; }
}

My Endpoint looks like this:

[HttpPost]
    public async Task<IActionResult> PostUser([FromBody] User user)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        user.DateCreated = DateTime.Now;
        //user.LastLogin = DateTime.Now;
        var hashedPassword = BCrypt.Net.BCrypt.HashPassword(user.Password);
        user.Password = hashedPassword;
        _context.User.Add(user);
        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateException ex)
        {
            Console.WriteLine(ex.Message);
            if (UserExists(user.Id))
            {
                return new StatusCodeResult(StatusCodes.Status409Conflict);
            }
            else
            {
                Console.WriteLine(ex.Message);
            }
        }

        return CreatedAtAction("GetUser", new { id = user.Id }, user);
    }

Notice that after doing some debugging, the new user being passed from the body it passes the check below, meaning that my model is valid:

if (!ModelState.IsValid)
{
    return BadRequest(ModelState);
}

But at the end, ends up on the catch block and printing out the exception mentioned above.

It seems to try to create a role linked to the new user. I don't now why because the role already exists.

What could be the cause of this issue?

Solution

If your role instance already exists, you need to Attach it to let EF know that it already exists in the database. Otherwise EF assumes it's a new instance and attempts to recreate it, causing a unique constraint violation. This is simply how EF works, you can read https://msdn.microsoft.com/en-us/data/jj592676.aspx for more details (it's about EF6 but applies to EFCore as well).

Note that you can also load your existing role from the database as you've done in your own answer (_context.Role.FirstOrDefault(...)), but this may involve an unnecessary database query. As long as you're able to fully construct your Role object in .NET, all you need to do is to attach it to your context and EF will understand that it's supposed to already exist in the database.

Comments

[Shay Rojansky]

How is your User instance created exactly, and what is its Role set to? Try setting the role to null before saving the changes, just to confirm that it's the role. If it's indeed the role causing the exception, you need to understand exactly when it's being created and how. You may need to attach your role instance to the existing database entity (otherwise EF things it's a new one and attempts to insert). Read up on Add vs. Attach to understand this better.

[abdul.badru]

My user interface is created using angular2. When I pass null on role property it works. But when I specify an existing role it fails because is trying to recreate the role instead of just attaching it to the new user being created.