EF conflict with PK trying to save to the DB

Question

I have app with 3 entity models

public class Project
{
    public Guid ProjectId { get; set; }
    public string ProjectName { get; set; }
    public string ProjectInfo { get; set; }
    public Guid DepartMentId { get; set; }

    public ICollection<Employee> Employees { get; set; } = new List<Employee>();

    public Department Department { get; set; }
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PayrollNumber { get; set; }
    public int Seniority { get; set; }
    [Column(TypeName = "decimal(10,2)")]
    public decimal Salary { get; set; }
    public int Rank { get; set; }
    public int Hours { get; set; }
    public ICollection<Project> Project { get; set; }
}

public class Department
{
    public Guid DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public string DepartmentInfo { get; set; }
    public int Rank { get; set; }
    public List<Project> Projects { get; set; } = new List<Project>();
}

I am create a new employee after created department and project like this

public void AddEmployee(string fname, string lname, string payNum, int seniority, decimal salary, int hours, string projname)
    {
        var proj = _projectController.GetProjects().Where(x => x.ProjectName == projname).SingleOrDefault();
        var projects = new List<Project>();
        projects.Add(proj);

        var emp = new Employee { EmployeeId = Guid.NewGuid(), FirstName = fname, LastName = lname, PayrollNumber = payNum, Seniority = seniority, Salary = salary, Hours = hours, Project = projects };

        _employeeController.AddEmployee(emp);
    }

AddEmployee method leads to SaveChanges(); method after all and I am getting this error SqlException: Violation of PRIMARY KEY constraint 'PK_Projects'. Cannot insert duplicate key in object 'dbo.Projects'. The duplicate key value is (10bc054b-b9a7-47da-eb14-08dac1b7a7fe). The statement has been terminated. How can I fix this?

score 0 · Answer 1 · answered Nov 08 '22 at 18:59

0

you need to fix it by checking which columns are the primary keys in the database not C# project if e.g. ProjectId is the unique primary key then error happens when inserted twice, quess the c# code doesn't work except you use meta annotation to indicate which columns are the primary key(s) (using @Key) in a code-first approach using migrations.

answered Nov 08 '22 at 18:59

user13322060

52
1
6

Thanks for answer, my PK's are common, DepartmentId for Department class, ProjectId for Project class, EmployeeId for Employee. I also have many to many relation Employee-Project (as you can see by ICollection on both this classes). So I don't know how to fix it. – Palamar66 Nov 08 '22 at 19:01
i understand, normally in such cases I look into the database whether this guid already exists and/or would print the project to the console before inserting, not sure, maybe it adds >1 projects where guid is null and then tries to use the same guid... – user13322060 Nov 08 '22 at 19:06
I only added 1 project, and it has the Guid – Palamar66 Nov 08 '22 at 19:07
I see, only noticed you have List Projects instead of ICollection not sure whether this has any meaning, only use ICollection – user13322060 Nov 08 '22 at 19:09
I found out, that it is trying to save an item to the database both in dbo.Project and dbo.EmployeeProject (this many to many table), do you know how to fix it? – Palamar66 Nov 08 '22 at 20:27
not sure, but maybe this helps https://stackoverflow.com/questions/25441027/how-do-i-stop-entity-framework-from-trying-to-save-insert-child-objects – user13322060 Nov 09 '22 at 07:30

score 0 · Answer 2 · answered Nov 08 '22 at 19:04

0

Make sure that a link table has been created for the link between an employee and his project. In the model you are providing, it appears that you have a "many to many" relationship between employee and project, meaning an employee can have many prjoect and a project can have many employee.

So a table name like "Project-Employee" must be there and store the ids of both end as foreign key and have it's own primary key. If it's not there, then of course, duplicate primary key are going to be inserted and this error occurs.

Can you check in the database if it is present ? If yes I suggest you code using it.

answered Nov 08 '22 at 19:04

Antoine Pelletier

3,164
3
40
62

Yes it is presented – Palamar66 Nov 08 '22 at 19:04
Include it in your code and manually populate it instead of trying to do it... the right way I guess – Antoine Pelletier Nov 08 '22 at 19:06
You mean, that I should add a class, if so should I add it to the context or what, could you explain – Palamar66 Nov 08 '22 at 19:09
Yes, implement the class for the linking table and when adding these list of project you would add them in the linking table as foreign key only, not the whole object... – Antoine Pelletier Nov 08 '22 at 19:13
I found out, that it is trying to save an item to the database both in dbo.Project and dbo.EmployeeProject (this many to many table) and causing the problem cause such project is already in the db, do you know how to fix it? – Palamar66 Nov 08 '22 at 20:32

EF conflict with PK trying to save to the DB

2 Answers2