Self-Referencing Keys Gotcha in EF

Looking at the Employee table in Northwind, there is a self-referencing key on the ‘ReportsTo’ field

image

When a series of new employees are created, you might run into problems trying to commit those changes all at once.  For example:

  1. static void Main(string[] args)
  2. {
  3.     Console.WriteLine("Start");
  4.  
  5.     using (NorthwindEntities entities = new NorthwindEntities())
  6.     {
  7.         Employee employee1 = new Employee();
  8.         employee1.FirstName = "Test";
  9.         employee1.LastName = "Employee1";
  10.         entities.Employees.Add(employee1);
  11.  
  12.         Employee employee2 = new Employee();
  13.         employee2.FirstName = "Test";
  14.         employee2.LastName = "Employee2";
  15.         employee2.ReportsTo = employee1.EmployeeID;
  16.         entities.Employees.Add(employee2);
  17.  
  18.         entities.SaveChanges();
  19.     }
  20.  
  21.     Console.WriteLine("-Stop-");
  22.     Console.ReadKey();
  23. }

 

When you run this, you get this kind of exception:

image

 

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:

  1. using (NorthwindEntities entities = new NorthwindEntities())
  2. {
  3.     Employee employee1 = new Employee();
  4.     employee1.FirstName = "Test";
  5.     employee1.LastName = "Employee1";
  6.     entities.Employees.Add(employee1);
  7.     entities.SaveChanges();
  8.  
  9.     Employee employee2 = new Employee();
  10.     employee2.FirstName = "Test";
  11.     employee2.LastName = "Employee2";
  12.     entities.Employees.Add(employee2);
  13.     employee2.ReportsTo = employee1.EmployeeID;
  14.     entities.SaveChanges();
  15.  
  16. }

Or to not associate the FK until the 1st bulk commit like this;

  1. using (NorthwindEntities entities = new NorthwindEntities())
  2. {
  3.     Employee employee1 = new Employee();
  4.     employee1.FirstName = "Test";
  5.     employee1.LastName = "Employee1";
  6.     entities.Employees.Add(employee1);
  7.  
  8.     Employee employee2 = new Employee();
  9.     employee2.FirstName = "Test";
  10.     employee2.LastName = "Employee2";
  11.     entities.Employees.Add(employee2);
  12.     entities.SaveChanges();
  13.  
  14.     employee2.ReportsTo = employee1.EmployeeID;
  15.     entities.SaveChanges();
  16.  
  17. }

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: