Enforcing referential integrity on object delete in 1-to-0..1 relationship

Question

I have created an asp.NET MVC web app with two models using EF Code First which have a 1-to-0..1 relationship.

public class ClassA
{
    public int Id {get;set;}
    //other properties
    public virtual ClassB ClassB {get;set;}
}

public class ClassB
{
    public int Id {get;set;}
    //other properties
}

In my database, this successfully creates the two tables with ClassA having a Nullable FK for ClassB. This works great, except for the scenario in which a ClassA record is deleted. In that case, any associated ClassB record is left in the database. I know that I can manually remove them in the Delete POST method:

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
    ClassA classA = context.ClassA.Include(c => c.ClassB).First(c => c.Id == id);
    context.ClassB.Remove(classA.ClassB);
    context.ClassA.Remove(classA);
    context.SaveChanges();
}

I don't really like this approach because it relies on me not making a mistake and assumes that this method is the only way that a record might be deleted (there might also be direct DELETE SQL statements run against the database at some point).The example I've created here is simple, but my actual application involves a lot of models and associations and it has gotten quite complicated. While I like to think that I am infallible, I have learned from experience that I am not and would rather not rely on myself to ensure that records don't become orphaned =)

How can I force the database to enforce referential integrity so that when a ClassA is deleted, any ClassB's that have foreign keys in that ClassA are also deleted?

SOLVED (kinda)

As suggested by Gert, I used Fluent API to ensure that the right entity was set as the Principle and to set all of the relationships to cascade on delete. I did run into a few issues though, mostly due to there already being data in my database. Fortunately, I am at a point in development where I can simply delete all of the data; otherwise, I'm not sure how I would have fixed this.

First I tried to just add the Fluent API and update-database. I got an error that read in part: "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong" which seemed to be due to EF trying to change the name of an existing FK column. That being the case, I resolved to use 2 migrations: One to remove existing relationships and another to add the newly reconfigured relationships. I had to do this in a rather specific series of events.

1. Commented out all references to the affected relationships in my controller to avoid errors while updating.

2. Commented out the relationship in the Model.

   public class ClassA
   {
       public int Id {get;set;}
       //other properties
       //public virtual ClassB ClassB {get;set;}
   }
   

3. Add-Migration and Update-Database to remove the existing relationships.
4. Undid all changes to the model and controller by uncommenting everything that I commented out in step 1 and 2.

5. Configured the new relationship in OnModelCreating as suggested by Gert to save the new relationships.

   modelBuilder.Entity<ClassA>()
       .HasOptional(b => b.ClassB)
       .WithRequired()
       .Map(m => m.MapKey("ClassB_Id"))
       .WillCascadeOnDelete();
   

6. Add-Migration and Update-Database to create the new relationships. This step failed when there was existing data in my database. If I didn't have the option to simple clear the database of all data, I'm not sure how I would have accomplished this.

Answer 1

In one-to-one associations you always have to think about which entity is the principle entity and which is the dependent one. The dependent one, as the word implies, can't exist without the other.

In your design the principle and dependent are the wrong way round: ClassA is the dependent, ClassB can live on its own. This is how EF happens to interpret the class model. If you want it to do it otherwise, you'll have to add some mapping instructions. Keeping the class model unaltered, this can only be done by the fluent API, for instance in the context's OnModelCreating override:

modelBuilder.Entity<ClassA>().HasOptional(a => a.ClassB)
    .WithRequired().Map(m => m.MapKey("ClassAId"))
    .WillCascadeOnDelete();

Now there will be a foreign key, ClassAId, in the ClassB table. The cascaded delete makes sure that when a ClassA is deleted, its dependent ClassB is deleted automatically.

Answer 2

Change the foreign key constraint in the sql server database to "ON DELETE CASCADE"

https://stackoverflow.com/a/6260736/1056639

ALTER TABLE dbo.T2
   DROP CONSTRAINT FK_T1_T2   -- or whatever it's called

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE