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?

Answer 1

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.