Violation of PRIMARY KEY constraint ''. Cannot insert duplicate key in object ''

Question

I'm trying to create Match between two User's. However i'm getting this error

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY "PK_Users" constraint. Cannot insert duplicate key into object 'dbo.Users'. Duplicate key value: (0c7a0cdc-a7ba-4cf8-ade2-0cef44761597).

I have this entities

public class Match
{
    public Guid Id { get; set; }
    public User User { get; set; }
    public User LikedUser { get; set; }
    public bool Matched { get; set; }
}

public class User
{
    public string Username { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public Guid CityId { get; set; }
    public City City { get; set; }
    public Guid GenderId { get; set; }
    public Gender Gender { get; set; }

    public ICollection<Match> Matches { get; set; } = new HashSet<Match>();
    public ICollection<Match> LikedBy { get; set; } = new HashSet<Match>();
}

That's unfinished method i just want to check if it would create Match or not

public async Task<Guid> Handle(CreateMatchCommand request, CancellationToken cancellationToken)
{
    var liker = await _unitOfWork.UserRepository.FindByIdAsync(request.Dto.Liker);
    var liked = await _unitOfWork.UserRepository.FindByIdAsync(request.Dto.Liked);

    var match = new Match { LikedUser = liked, User = liker };

    var matchId = await _unitOfWork.MatchRepository.CreateAsync(match);
    await _unitOfWork.SaveChangesAsync(cancellationToken);

    return matchId;
}

MatchRepository

public async Task<Guid> CreateAsync(Match match)
{
    var matchEntity = _mapper.Map<MatchEntity>(match);
    await _context.Matches.AddAsync(matchEntity);

    return matchEntity.Id;
}

UserRepository

public async Task<User> FindByIdAsync(Guid guid)
    {
        var user = await _context.Users
            .FirstOrDefaultAsync(u => u.Id == guid);
        if (user == null) throw new NotFoundException(typeof(User), guid);

        _context.Entry(user).State = EntityState.Detached;
        return _mapper.Map<User>(user);
    }

Why i'm getting this error?

Answer 1

In userRepository, try removing this line.

public async Task<User> FindByIdAsync(Guid guid)
    {
        var user = await _context.Users
            .FirstOrDefaultAsync(u => u.Id == guid);
        if (user == null) throw new NotFoundException(typeof(User), guid);

        //_context.Entry(user).State = EntityState.Detached; <-- Remove this
        return _mapper.Map<User>(user);
    }

So instead of tracking the Liked and Like users as new object, we will keep tracking them as an existing objects that are fetch from database, so EF Core will just update the foreign key in the Match entity automatically.

Edit : Since first solution didn't work, you can also try like this to automatically attached it:

public async Task<Guid> Handle(CreateMatchCommand request, CancellationToken cancellationToken)
{
    var liker = await _unitOfWork.UserRepository.FindByIdAsync(request.Dto.Liker);
    var liked = await _unitOfWork.UserRepository.FindByIdAsync(request.Dto.Liked);

_unitOfWork.Entry(liker).State = EntityState.Unchanged;
_unitOfWork.Entry(liked).State = EntityState.Unchanged;

    var match = new Match { LikedUser = liked, User = liker };

    var matchId = await 
    
     
    _unitOfWork.MatchRepository.CreateAsync(match);
    await _unitOfWork.SaveChangesAsync(cancellationToken);

    return matchId;
}

So we will tell EF Core that entity Liker and Liked are existing entities from database, and no need for it to be reinserted. Just update the Match foreign keys to point to the Liker and Liked entities

Answer 2

Yes, this is what will actually happen to you, the reason is that both the user and the second user will be considered new objects. What you will do is the following: add the UserId and LikedUserId in the Match, and save the new ones as follows:

var match = new Match { LikedUserId = liked.Id, UserId = liker.Id };