The Problem
Here's a typical pattern for deleting a child entity we've all tried when using EF6.
public void DeleteItem(int orderId, int itemId)
{
var db = MyDbContext();
var order = db.Orders.Find(orderId);
order.Items.Remove(order.Items.Find(itemId));
db.SaveChanges();
}
Then we get one of these errors:
Cannot insert the value NULL into column 'OrderId', table 'MyDbContext.dbo.Items'; column does not allow nulls. UPDATE fails.
Or
The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.
This article explains why this happens and what to do about it. It also demonstrates configuring for five different key datatypes:
- String
- Manually-generated Int
- Identity Int
- Manually-generated GUID
- "Identity" GUID
It's nigh-on impossible to find any EF examples of foreign key relationships that don't use a integer identity column for the primary key. Also, I haven't seen any author demonstrate the approach I'm taking, even though it's perfectly legitimate from an Entity Framework perspective. Simply put:
We don't have to model the database exactly as it is.
The Solution - Short Version for the Impatient
Most SQL databases I've seen create a one-to-many relationship this way. All of these columns are not nullable.
Order
-----
OrderId PK
Item
----
ItemId PK
OrderId FK
This is technically called a non-identifying relationship. An item belongs to an order, but it's only required because OrderId is not null. OrderId isn't part of the item's primary key. The OrderId foreign key can change. The foreign key could be changed to nullable, and there could then be items that exist without an order.
Here's an identifying relationship. And item's uniqueness is bound to an order. The foreign key is also part of the primary key (it's a composite key). Thus, the foreign key can't change and the item must be part of an order.
Order
-----
OrderId PK
Item
----
ItemId PK
OrderId PK, FK
Entity Framework will automatically delete child entities that are part of an identifying relationship. If the relationshiop is non-identifying, EF will try to set the foreign key to null.
I'm sure there's a reason for this behavior, but I don't understand it. If EF knows the foreign key can't be null, why not go ahead and delete the child entity?
If your child table has a non-identifying relationship (which is common), you have two options:
- Change the table's foreign key to a composite key, as well as the entity's configuration in EF.
- Only change the entity's EF configuration.
I like the second one. Here's how this looks.
Again, the non-Identifying Item relationship in the table
Order
-----
OrderId PK
Item
----
ItemId PK
OrderId FK
Identifying Item relationship in Entify Framework
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Item>()
.HasKey(a => new { a.ItemId, a.OrderId });
}
With this in place, using Order.Items.Remove(item) will result in Entity Framework deleting the item.
Foreign Keys That Are Manually Generated, And/Or a String, Int or GUID
Let's keep assuming a non-identifying relationship with non-nullable foreign keys. How does the EF configuration change if we're dealing with string or GUID primary keys? What if the keys aren't auto-generated?
I wrote a console application to explore this. It turns out each flavor needs a little tweak because of EF conventions.
Test Schema
Create a new database and run this script to create the tables.
--String Keys
create table Customers (
CustomerId varchar(10) not null primary key,
)
create table Addresses (
AddressId varchar(10) not null primary key,
CustomerId varchar(10) not null foreign key references Customers(CustomerId),
)
--Manually-generated Int keys
create table Orders (
OrderId int not null primary key,
)
create table Items (
ItemId int not null primary key,
OrderId int not null foreign key references Orders(OrderId),
)
--Identity Int keys
create table Projects (
ProjectId int not null identity primary key,
)
create table Tasks (
TaskId int not null identity primary key,
ProjectId int not null foreign key references Projects(ProjectId),
)
--Manually-generated GUID keys
create table Stores (
StoreId uniqueidentifier not null primary key,
)
create table Employees (
EmployeeId uniqueidentifier not null primary key,
StoreId uniqueidentifier not null foreign key references Stores(StoreId),
)
--"Identity" Default Sequential GUID keys
create table Gardens (
GardenId uniqueidentifier default newsequentialid() not null primary key,
)
create table Flowers (
FlowerId uniqueidentifier default newsequentialid() not null primary key,
GardenId uniqueidentifier not null foreign key references Gardens(GardenId),
)
Classes
Here are the classes that map to the above tables.
//String Keys
public class Customer
{
public string CustomerId { get; set; }
public virtual ICollection<Address> Addresses { get; set; } = new HashSet<Address>();
}
public class Address
{
public string AddressId { get; set; }
public string CustomerId { get; set; }
public Customer Customer { get; set; }
}
//Manually-generated Int keys
public class Order
{
public int OrderId { get; set; }
public virtual ICollection<Item> Items { get; set; } = new HashSet<Item>();
}
public class Item
{
public int ItemId { get; set; }
public int OrderId { get; set; }
public Order Order { get; set; }
}
//Identity Int keys
public class Project
{
public int ProjectId { get; set; }
public virtual ICollection<Task> Tasks { get; set; } = new HashSet<Task>();
}
public class Task
{
public int TaskId { get; set; }
public int ProjectId { get; set; }
public Project Project { get; set; }
}
//Manually-generated GUID keys
public class Store
{
public Guid StoreId { get; set; }
public ICollection<Employee> Employees { get; set; } = new HashSet<Employee>();
}
public class Employee
{
public Guid EmployeeId { get; set; }
public Guid StoreId { get; set; }
public Store Store { get; set; }
}
//Identity GUID keys
public class Garden
{
public Guid GardenId { get; set; }
public ICollection<Flower> Flowers { get; set; } = new HashSet<Flower>();
}
public class Flower
{
public Guid FlowerId { get; set; }
public Guid GardenId { get; set; }
public Garden Garden { get; set; }
}
Basic DbContext class
My DbContext is named EfTestDb. The basic setup lets us view SQL statements.
I've turned off lazy loading, because I'm finding that a typical enterprise practice. The configurations are the same if lazy loading is turned on.
public class EfTestDb : DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Address> Addresses { get; set; }
public DbSet<Order> Orders { get; set; }
public DbSet<Item> Items { get; set; }
public DbSet<Project> Projects { get; set; }
public DbSet<Task> Tasks { get; set; }
public DbSet<Store> Stores { get; set; }
public DbSet<Employee> Employees { get; set; }
public DbSet<Garden> Gardens { get; set; }
public DbSet<Flower> Flowers { get; set; }
public EfTestDb()
{
Database.SetInitializer<EfTestDb>(null);
Configuration.LazyLoadingEnabled = false;
Configuration.ProxyCreationEnabled = false;
//Show SQL in Output Debug window.
this.Database.Log = s =>
{
System.Diagnostics.Debug.Write(s);
System.Diagnostics.Trace.Write(s);
Console.WriteLine(s);
};
}
}
String Key (Manually Generated)
It's still pretty common to have primary keys that are "intelligent" strings such as "blue-washer-20050225" that are generated by the application.
Here's the EF configuration.
I'm relying on EF conventions for key column naming, otherwise I'd need more configurations for .HasKey, .HasColumnName, etc.
// # String Keys
modelBuilder.Entity<Customer>()
.HasMany(a => a.Addresses);
modelBuilder.Entity<Address>()
.HasKey(a => new { a.AddressId, a.CustomerId })
.HasRequired(a => a.Customer);
This is the general pattern we'll see. The difference will be in how the keys and properties are defined.
Here's the testing code. I'm using DetachAllEntities to simulate saving a complex entity in one session, then deleting a child in another.
class Program
{
static void Main(string[] args)
{
StringKey();
ManualInt();
IdentityInt();
ManualGuid();
IdentityGuid();
Console.ReadLine();
}
static void DetachAllEntities(EfTestDb db)
{
foreach (var entry in db.ChangeTracker.Entries()) { entry.State = EntityState.Detached; }
}
static void StringKey()
{
using (var db = new EfTestDb())
{
//clean up
db.Addresses.RemoveRange(db.Addresses);
db.Customers.RemoveRange(db.Customers);
db.SaveChanges();
DetachAllEntities(db);
//add complex entity
var customer = new Customer()
{
CustomerId = "a",
Addresses = new HashSet<Address>()
{
new Address() { AddressId = "aa" },
new Address() { AddressId = "bb" }
}
};
db.Customers.Add(customer);
db.SaveChanges();
DetachAllEntities(db);
//delete child
customer = db.Customers.Include(a => a.Addresses).First();
var address = customer.Addresses.First();
customer.Addresses.Remove(address);
db.SaveChanges();
}
}
//... remaining test methods
}
Manually-Generated Int keys
// # Manually-generated Int keys
modelBuilder.Entity<Order>()
.HasMany(a => a.Items);
modelBuilder.Entity<Order>()
// I don't know why this is required, except that
// maybe EF assumes an int key is going to be an identity column
.Property(p => p.OrderId)
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
modelBuilder.Entity<Item>()
.HasKey(a => new { a.ItemId, a.OrderId })
.HasRequired(a => a.Order);
static void ManualInt()
{
var db = new EfTestDb();
//clean up
db.Items.RemoveRange(db.Items);
db.Orders.RemoveRange(db.Orders);
db.SaveChanges();
DetachAllEntities(db);
//add complex entity
var order = new Order()
{
OrderId = 1,
Items = new List<Item>()
{
new Item() { ItemId = 11 },
new Item() { ItemId = 22 }
}
};
db.Orders.Add(order);
db.SaveChanges();
DetachAllEntities(db);
//delete child
order = db.Orders.Include(a => a.Items).First();
var Item = order.Items.First();
order.Items.Remove(Item);
db.SaveChanges();
}
Identity Int keys
modelBuilder.Entity<Task>()
.HasKey(a => new { a.TaskId, a.ProjectId });
modelBuilder.Entity<Task>()
.Property(p => p.TaskId)
// required because EF can't assume TaskId is an identity column since it's part of a composite key.
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
static void IdentityInt()
{
var db = new EfTestDb();
//clean up
db.Tasks.RemoveRange(db.Tasks);
db.Projects.RemoveRange(db.Projects);
db.SaveChanges();
DetachAllEntities(db);
//add complex entity
var Project = new Project()
{
Tasks = new List<Task>()
{
new Task(),
new Task()
}
};
db.Projects.Add(Project);
db.SaveChanges();
DetachAllEntities(db);
//delete child
Project = db.Projects.Include(a => a.Tasks).First();
var Task = Project.Tasks.First();
Project.Tasks.Remove(Task);
db.SaveChanges();
}
Manually-Generated GUID keys
modelBuilder.Entity<Store>()
.HasMany(a => a.Employees);
modelBuilder.Entity<Employee>()
.HasKey(a => new { a.EmployeeId, a.StoreId })
.HasRequired(a => a.Store);
static void ManualGuid()
{
var db = new EfTestDb();
//clean up
db.Employees.RemoveRange(db.Employees);
db.Stores.RemoveRange(db.Stores);
db.SaveChanges();
DetachAllEntities(db);
//add complex entity
var Store = new Store()
{
StoreId = Guid.NewGuid(),
Employees = new List<Employee>()
{
new Employee() { EmployeeId = Guid.NewGuid() },
new Employee() { EmployeeId = Guid.NewGuid() }
}
};
db.Stores.Add(Store);
db.SaveChanges();
DetachAllEntities(db);
//delete child
Store = db.Stores.Include(a => a.Employees).First();
var Employee = Store.Employees.First();
Store.Employees.Remove(Employee);
db.SaveChanges();
}
"Identity" GUID (Default Sequential GUID keys)
modelBuilder.Entity<Garden>()
.Property(p => p.GardenId)
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Garden>()
.HasMany(a => a.Flowers);
modelBuilder.Entity<Flower>()
.Property(p => p.FlowerId)
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Flower>()
.HasKey(a => new { a.FlowerId, a.GardenId })
.HasRequired(a => a.Garden);
static void IdentityGuid()
{
var db = new EfTestDb();
//clean up
db.Flowers.RemoveRange(db.Flowers);
db.Gardens.RemoveRange(db.Gardens);
db.SaveChanges();
DetachAllEntities(db);
//add complex entity
var Garden = new Garden()
{
Flowers = new List<Flower>()
{
new Flower(),
new Flower()
}
};
db.Gardens.Add(Garden);
db.SaveChanges();
DetachAllEntities(db);
//delete child
Garden = db.Gardens.Include(a => a.Flowers).First();
var Flower = Garden.Flowers.First();
Garden.Flowers.Remove(Flower);
db.SaveChanges();
}
Lessons Learned
- Non-identifying, but required, parent-child relationships are common.
- The database doesn't have to be changed to allow Entity Framework to easily delete child records.
- The most common key types can be modeled.
References
