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:

  1. Change the table's foreign key to a composite key, as well as the entity's configuration in EF.
  2. 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

Other Background Information