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

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);

    ...
}