When you are seeding the instance data in Entity Framework, you might see the following error when you are doing AddOrUpdate on an Entity which has explicit Foreign Keys defined.
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_something". The conflict occurred in database "database_name", table "table_name", column 'column'. The statement has been terminated.
For instance let’s take the following scenario.
public class Department
{
public int Id { get; set; }
public string Name { get; set; }
public virtual List<Employee> Employees { get; set; }
}
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public virtual Department Department { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<Department> Departments { get; set; }
}
internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(MyDbContext context)
{
context.Departments.AddOrUpdate(d => d.Name, new Department()
{
Id = 1,
Name = "Microsoft Visual Studio"
});
context.Departments.AddOrUpdate(d => d.Name, new Department()
{
Id = 2,
Name = "Microsoft SQL Server"
});
context.SaveChanges();
context.Employees.AddOrUpdate(e => e.Id, new Employee()
{
Id = 1,
Name = "Jaliya Udagedara",
Department = context.Departments.First(d => d.Name == "Microsoft Visual Studio")
});
context.Employees.AddOrUpdate(e => e.Id, new Employee()
{
Id = 2,
Name = "John Smith",
Department = context.Departments.First(d => d.Name == "Microsoft SQL Server")
});
context.SaveChanges();
}
}
Here I have
Employee and
Department Entities where Employee belongs to a Department. A foreign key will be created by EF on
Employee towards
Department. I am seeding up some instance data using
IDbSetExtensions.AddOrUpdate and I can run
Update-Database without any issues.
But now, let’s changed the Employee entity as follows. Here I have explicitly defined a Foreign Key using data annotations (you can do it using Fluent API as well).
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public int Department_Id { get; set; }
[ForeignKey("Department_Id")]
public virtual Department Department { get; set; }
}
Now after adding a new migration and if I try to run Update-Database, I will be getting the mentioned error.
 |
| Error |
At first you might find this bit surprising. Now let’s see what is happening here. I have attached a debugger for the Seed to make it much easy to understand.
 |
| Debugging |
From the Seed method, we were setting Department property of Employee which was working fine. But now after explicitly defining Foreign Keys, as you can see it’s not getting set even though we are setting it. The reason is now the relationship is working through Department_Id property. And since we are not setting a value to Department_Id, it is 0, and that’s the reason behind the error we were getting. So to fix this, we will have to set the value for Department_Id property and not to the Department property.
internal sealed class Configuration : DbMigrationsConfiguration<MyDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(MyDbContext context)
{
context.Departments.AddOrUpdate(d => d.Name, new Department()
{
Id = 1,
Name = "Microsoft Visual Studio"
});
context.Departments.AddOrUpdate(d => d.Name, new Department()
{
Id = 2,
Name = "Microsoft SQL Server"
});
context.SaveChanges();
context.Employees.AddOrUpdate(e => e.Id, new Employee()
{
Id = 1,
Name = "Jaliya Udagedara",
Department_Id = context.Departments.First(d => d.Name == "Microsoft Visual Studio").Id
});
context.Employees.AddOrUpdate(e => e.Id, new Employee()
{
Id = 2,
Name = "John Smith",
Department_Id = context.Departments.First(d => d.Name == "Microsoft SQL Server").Id
});
context.SaveChanges();
}
}
Now you can run Update-Database command without any errors.
 |
| Update-Database Succeeded |
I am uploading the sample code to OneDrive, so you can try it yourself.
Happy Coding.
Regards,
Jaliya
![image_thumb[3]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsEVkfe5TO0-QL8R-4R2BivfuYSt5Si0k07GkqDYSbR5DZGi4yGbXK7DKIQsV0Lrzja0ORaf9S8Ll6S1asLXRE34yB3VYFpStmsVpCb7GzS690tj8f8LZr27qnZLJl2O9OcGNXnHQsgf8/s1600-h/image_thumb%25255B3%25255D%25255B5%25255D.png "image_thumb[3]")
