Delete child entity when removed from parent collection in one to many relationship Entity Framework

Question

I have the following database setup:

The tables are mapped as follow:

public class OrderMapping : EntityTypeConfiguration<Order>
{
    public OrderMapping()
    {
        this.ToTable("Orders", "prf");

        this.HasKey(o => o.OrderId);

        this.HasMany(o => o.OrderItems)
            .WithRequired(oi => oi.Order)
            .HasForeignKey(oi => oi.OrderId);

        this.HasRequired(o => o.Institution)
            .WithMany()
            .HasForeignKey(o => o.InstitutionId);

        this.Property(o => o.IsConfirmed)
            .IsRequired();

        this.Ignore(o => o.Id);
    }
}

public class OrderItemMapping : EntityTypeConfiguration<OrderItem>
{
    public OrderItemMapping()
    {
        this.ToTable("OrderItems", "prf");

        this.HasKey(oi => oi.OrderItemId);

        this.HasRequired(oi => oi.Order)
            .WithMany(oi => oi.OrderItems)
            .HasForeignKey(oi => oi.OrderId);

        this.HasRequired(oi => oi.Proficiency)
            .WithMany()
            .HasForeignKey(oi => oi.ProficiencyId);

        this.HasOptional(oi => oi.Enrolment)
            .WithMany()
            .HasForeignKey(oi => oi.EnrolmentId);

        this.HasMany(oi => oi.OrderItemSets)
            .WithRequired(ois => ois.OrderItem)
            .HasForeignKey(ois => ois.OrderItemId);

        this.Property(oi => oi.DateCreated);

        this.Ignore(oi => oi.Id);
    }
}

public class OrderItemSetMapping : EntityTypeConfiguration<OrderItemSet>
{
    public OrderItemSetMapping()
    {
        this.ToTable("OrderItemSets", "prf");

        this.HasKey(ois => ois.OrderItemSetId);

        this.HasRequired(ois => ois.OrderItem)
            .WithMany(ois => ois.OrderItemSets)
            .HasForeignKey(ois => ois.OrderItemId);

        this.Property(ois => ois.NumberOfSets);
        this.Property(ois => ois.Month);
    }
}

When I try to remove an OrderItemSet from the OrderItem's collection Entity Framework is trying to set the foreignkey in OrderItemSet as null instead of deleting the row even though the foreignkey is not nullable and therefore throwing an exception stating the foreignkey cannot be set to null.

this.OrderItemSets.Remove(orderItemSet);

I don't know what is wrong with my mapping to have Entity Framework think it should set the foreignkey to null instead of deleting the row.

Answer 1

What you need is an identifying relationship between OderItem and OrderItemSet. From the section Considerations for Identifying and Non-identifying Relationships in the link provided above:

Removing the relationship deletes the dependent object. Calling the Remove method on the EntityCollection marks both the relationship and the dependent object for deletion.

You should consider the same type of relation ship for Order and OrderItem.

The basic idea is that, for the model of OrderItemSet, you make the foreign key to OrderItem part of the OrderItemSet's primary key, thus creating a composite key. Inside the mapping for OrderItemSet try to map the primary key like so:

public OrderItemSetMapping()
{
    ...

    this.HasKey(ois => new { ois.OrderItemSetId, ois.OrderItemId });

    ...
}

In case this doesn't work with the fluent API, then try to create the mapping with attributes:

public class OrderItemSet
{
    [Key, ForeignKey("OrderItem"), Column(Order = 1)]
    public <YourKeyType> OrderItemId { get; set; }

    [Key, Column(Order = 2)]
    public <YourKeyType> OrderItemSetId { get; set; }

    ...
}

Answer 2

Why don't you just remove the child directly:

context.OrderItemSets.Remove(orderItemSet);
context.SaveChanges();

When you remove a child from a parent, you may want to add it to another parent, so it would be inappropriate for Entity Framework to delete the child automatically. You should do it yourself.

Answer 3

I have resolved the issue as per Abbondanza's suggestion. First off create the key of the entity to contain the foreign key (this will force entity framework to delete the child item as it cannot exist without the foreign key):

public OrderItemSetMapping()
{
    ...

    this.HasKey(ois => new { ois.OrderItemSetId, ois.OrderItemId });

    ...
}

Entity Framework will now delete the entity if removed from the collection of the parent, however since OrderItemSetId is an Identity column this creates another issue where Entity Framework now wants to insert a value in that column when adding a new item to the parent collection (which will throw an exception). By specifying a DatabaseGenerationOption on this column the problem will be resolved:

public OrderItemSetMapping()
{
    ...

    this.Property(r => r.OrderItemSetId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    ...
}