Self-Referencing Keys Gotcha in EF
October 22, 2013 Leave a comment
Looking at the Employee table in Northwind, there is a self-referencing key on the ‘ReportsTo’ field
When a series of new employees are created, you might run into problems trying to commit those changes all at once. For example:
- static void Main(string[] args)
- {
- Console.WriteLine("Start");
- using (NorthwindEntities entities = new NorthwindEntities())
- {
- Employee employee1 = new Employee();
- employee1.FirstName = "Test";
- employee1.LastName = "Employee1";
- entities.Employees.Add(employee1);
- Employee employee2 = new Employee();
- employee2.FirstName = "Test";
- employee2.LastName = "Employee2";
- employee2.ReportsTo = employee1.EmployeeID;
- entities.Employees.Add(employee2);
- entities.SaveChanges();
- }
- Console.WriteLine("-Stop-");
- Console.ReadKey();
- }
When you run this, you get this kind of exception:
The reason is that the PK of the 1st employee is 0 and the PK of the second employee is ….. 0. Since both are created on the client and have not had the ‘real’ primary key assigned by the database, the FK gets confused because there are 2 Primary Keys with the same value. And according to these calculations, that is impossible.
The way to fix this problem is to move the SaveChanges() to after each add like this:
- using (NorthwindEntities entities = new NorthwindEntities())
- {
- Employee employee1 = new Employee();
- employee1.FirstName = "Test";
- employee1.LastName = "Employee1";
- entities.Employees.Add(employee1);
- entities.SaveChanges();
- Employee employee2 = new Employee();
- employee2.FirstName = "Test";
- employee2.LastName = "Employee2";
- entities.Employees.Add(employee2);
- employee2.ReportsTo = employee1.EmployeeID;
- entities.SaveChanges();
- }
Or to not associate the FK until the 1st bulk commit like this;
- using (NorthwindEntities entities = new NorthwindEntities())
- {
- Employee employee1 = new Employee();
- employee1.FirstName = "Test";
- employee1.LastName = "Employee1";
- entities.Employees.Add(employee1);
- Employee employee2 = new Employee();
- employee2.FirstName = "Test";
- employee2.LastName = "Employee2";
- entities.Employees.Add(employee2);
- entities.SaveChanges();
- employee2.ReportsTo = employee1.EmployeeID;
- entities.SaveChanges();
- }
