EF Core error : duplicate key value violates unique constraint

Question

I am getting this error when I try to Add an entity to the database after I switched from injecting the DbContext directly into the DI to using DbContextFactory:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_Departamente"

In the Entity (ModificareInregistrare) I want to add, I have a reference to a Department that exists in the database. I am pretty sure that ef core tries to add this Department to the database.

    public int Id { get; set; }
    ..
    public Guid DepartamentId { get; set; }
    public virtual Departament Departament { get; set; }
    ..

This is the call:

        var modificare = _mapper.Map<ModificareInregistrare>(request.ModificaInregistrareDto);
        var op = await _dbContext.ModificariInregistrari.AddAsync(modificare, cancellationToken);
        await _dbContext.SaveChangesAsync(cancellationToken);

The DbContext is created from IDbContextFactory

    public CreateModificareInregistrareCommandHandler(IDbContextFactory<ApplicationDbContext> contextFactory, IMapper mapper)
    {
        _dbContext = contextFactory.CreateDbContext();
        _mapper = mapper;
    }

Departament class looks like this (trimmed alot of properties):

public class Departament : SoftDeleteEntity<Guid>, IRepartizabil
{
    /// <summary>
    ///     Numele departamentului
    /// </summary>
    public string Nume { get; set; } = "";

    /// <summary>
    ///     Prescurtarea numelui
    /// </summary>
    public string Prescurtare { get; set; } = "";

    /// <summary>
    ///     Id parintelui pentru organigrama
    /// </summary>
    public int? DepartamentParinteId { get; set; }


    public ICollection<ComentariuDepartament> Comentarii { get; set; } = new List<ComentariuDepartament>();

    public virtual ICollection<RepartizareUtilizatorDepartament> RepartizariUtilizatori { get; set; }

    public virtual ICollection<RepartizareUtilizatorSecretariatDepartament> RepartizariUtilizatoriSecretariat
    {
        get;
        set;
    }

    public virtual ICollection<Registru> RegistrePermise { get; set; }

    public virtual ICollection<ModificareInregistrare> ModificariInregistrari { get; set; }

}

LE: This is the important part of the Modificare object that is sent to AddAsync method:

The Departament is null and the DepartamentId is set.

LLE : Started getting the error after switching from a scoped DbContext to a DbContextFactory with Transient scope. Thing is, this is the only place where I get this error . I am adding Entities with DepartamentId set and Departament null , and I don't get this error.

SOLVED: Inside the Modificare class I had another reference Registru and RegistruId , and inside Registru there was a List of permitted Departaments . I updated the DTO profile from automapper to ignore the Registru reference (set it null) and now there is nothing referencing the Departament and the error is gone .

Answer 1

You can solve it by this

 var modificare = _mapper.Map<ModificareInregistrare>(request.ModificaInregistrareDto);

 modificare.Departament  = _dbContext.Departament.FindAsync(modificare.Departament.DepartamentId);

 var op = await _dbContext.ModificariInregistrari.AddAsync(modificare, cancellationToken);
 await _dbContext.SaveChangesAsync(cancellationToken);

Or

var modificare = _mapper.Map<ModificareInregistrare>(request.ModificaInregistrareDto);

_dbContext.Entry(modificare.Departament).State = EntityState.Modified;

_dbContext.ModificariInregistrari.AddAsync(modificare, cancellationToken);
 await _dbContext.SaveChangesAsync(cancellationToken);

These two approaches will help EF Core to know that the row department with that specified primary key already exist, and track the existing from database instead of inserting new one.

Second way, you can also solve it by this

 var modificare = _mapper.Map<ModificareInregistrare>(request.ModificaInregistrareDto);

 modificare.Departament  = null;

 var op = await _dbContext.ModificariInregistrari.AddAsync(modificare, cancellationToken);
 await _dbContext.SaveChangesAsync(cancellationToken);

Edit :

Since the above code does not work, I believe the reason for the issues might be this property inside Departament class.

public virtual ICollection<ModificareInregistrare> ModificariInregistrari { get; set; }

Since this is a reverse Navigation properties to the ModificareInregistrare, it will in turn re-refer back to the Departament unfortunately this time it will have same primary key to with its granparent of Departament

ModificareInregistrare

 --- Departament

    --- ModificareInregistrare

       --- Departament

           --- ModificareInregistrare

thus the repeating instance will cause the error.

You can solve it using this way

var modificare = _mapper.Map<ModificareInregistrare>(request.ModificaInregistrareDto);

modificare.Departament.ModificariInregistrari = null;

_dbContext.Entry(modificare.Departament).State = EntityState.Modified;

_dbContext.ModificariInregistrari.AddAsync(modificare, cancellationToken);
 await _dbContext.SaveChangesAsync(cancellationToken);