getting a violation error while copying entities

Question

i have below entity project weather having below structure

public class ProjectWeather
{
    [Key, ForeignKey("Project")]
    public string ProjectNumber { get; set; }
    [GraphQLIgnore]
    public virtual Project Project { get; set; }
    [ForeignKey("WeatherStation")]
    public string WeatherStationNumber { get; set; }
}

and i have other entity project and structure is looks like as below

public class Project : PatchEntityProperties
{
    [Key, GraphQLNonNullType]
    public string ProjectNumber { get; set; }
    public string Name { get; set; }
    public bool IsLocked { get; set; }
    public ProjectWeather Weather { get; set; } = new ();
}

what i am trying to do is copying one project info into other using the below code

   var cloneOfSourceProject = dbContext.Projects
        .AsNoTracking()
        .Where(a => a.ProjectNumber == sourceProjectNumber)
        .SingleOrDefault();

    // Tracked so we can remove existing project before adding copied entity
    var targetProject = dbContext.Projects
        .Where(a => a.ProjectNumber == targetProjectNumber)
        .SingleOrDefault();

    if (cloneOfSourceProject == default)
    {
        throw new ArgumentException($"The project with project number '{sourceProjectNumber}' does not exist; an invalid project cannot be copied.");
    }
    
    string targetProjectName;
    if (targetProject == default)
    {
        targetProjectName = dbContext.Projects.AsNoTracking().Where(a => a.Number == targetProjectNumber).SingleOrDefault()?.Name ?? "";
    }
    else
    {
        targetProjectName = targetProject.Name;
        dbContext.Remove(targetProject);
    }

    cloneOfSourceProject.ProjectNumber = targetProjectNumber;
    cloneOfSourceProject.Name = targetProjectName;
    dbContext.Add(cloneOfSourceProject);
    dbContext.SaveChanges();

but getting an error on duplicate key value violates unique constraint on PK_projectweather on project weather and i am not sure how to overcome that error, is there anything wrong with the above code. I am using EFcore with postgresql.

Could any one please let me know any idea on this, many thanks in advance.

Answer 1

Okay, I have an idea of what might be going on. When you query into cloneOfSourceProject, you're specifying AsNoTracking(). This means that the ProjectWeather object attached to the Project you're querying is returned as well, but EF Core is not tracking its identity. Then you change the Project's ProjectNumber and save it, and so EF Core tries saving the whole object, along with its ProjectWeather (whose ProjectNumber was unchanged). That results in postgres writing a new ProjectWeather row with a key that already exists, and thus an exception.

If that's correct, you should be able to solve the issue simply by setting the ProjectWeather's key to the new ProjectNumber before saving the project:

cloneOfSourceProject.Weather.ProjectNumber = targetProjectNumber;