CODEWITHSUNDEEP
c#entity-frameworkpostgresqlnpgsql
abdul.badru
abdul.badru

Reputation: 531

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

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?

Upvotes: 12

Views: 27465

Answers (2)

Shay Rojansky
Shay Rojansky

Reputation: 16692

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.

Upvotes: 8

abdul.badru
abdul.badru

Reputation: 531

Is definitely something related to entity framework. Although I am creating a new user attached to an existing role, is trying to also recreate the role. I went to my and point and added the following lines before saving the entity to make sure that the role attached to the user, uses the same database context as the user being saved:

 var rl = _context.Role.FirstOrDefault(r=> r.Id==user.role.Id);
 user.role= rl;

Now it works...

Do I need to do this always when I have to save objects that references other objects?

Upvotes: 1

Related Questions