code first one-to-one enable cascade delete

Question

I have one to one relationship with foreign keys but the Cascade Delete is not enabled for some reason. The sample code is below.

public class AppRegistration
{
    public int AppRegistrationId { get; set; }
    [Required]
    [StringLength(50)]
    [Display(Name = "Username")]
    public string UserName { get; set; }
    [Required]
    [StringLength(100)]
    public string Password { get; set; }
    [StringLength(20)]
    public string StudentOrAgent { get; set; }
    // navigation properties
    public virtual AppStatus AppStatus { get; set; }
    public virtual Agreement Agreement { get; set; }
    public virtual AnotherTable AnotherTable { get; set; }
}

The dependent table with a foreign key is below.

public class Agreement
{
    [Key]
    [ForeignKey("AppRegistration")]
    public int AppRegistrationId { get; set; }
    public DateTime DateAgreed { get; set; }
    public virtual AppRegistration AppRegistration { get; set; }
}

When I try to delete an entry from the generated AppRegistrations table I get a Reference constraint conflict.

I tried putting [Required] on the navigation property in the dependent table but it doesn't do anything - the Update-Database command shows the No pending code-based migrations. message. Any ideas? Thanks.

Update: I'm getting the following error message:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.AppStatus_dbo.AppRegistrations_AppRegistrationId". The conflict occurred in database "MVCapp", table "dbo.AppStatus", column 'AppRegistrationId'.

Answer 1

I decided to work out the cascade delete problem in a separate sample project. I found the following blog & MSDN pages very useful.

• http://blog.bennymichielsen.be/2011/06/02/entity-framework-4-1-one-to-one-mapping/
• http://msdn.microsoft.com/en-us/library/gg671256%28v=VS.103%29.aspx
• http://msdn.microsoft.com/en-us/library/gg671273%28v=VS.103%29.aspx

Using the Code First approach create the following Model.

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public virtual Book Book { get; set; }
}
public class Book
{
    public int CategoryId { get; set; }
    public string BookTitle { get; set; }
    public string BookAuthor { get; set; }
    public string BookISBN { get; set; }
    public virtual Category Category { get; set; }
}

(I realize the entity names suggest one-to-many relationship, but I am trying to model 1-to-1 relationship, as in my original question at the top.)

So, in the above model each Category can only have one Book.

In your DbContext-derived class add the following.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

    modelBuilder.Entity<Book>()
        .HasKey(t => t.CategoryId);

    modelBuilder.Entity<Category>()
        .HasRequired(t => t.Book)
        .WithRequiredPrincipal(t => t.Category)
        .WillCascadeOnDelete(true);
}

(The following namespaces are required for the above code: System.Data.Entity, System.Data.Entity.ModelConfiguration.Conventions.)

This properly creates the 1-to-1 relationship. You'll have a primary key in each table and also a foreign key in Book table with ON DELETE CASCADE enabled.

In the above code, on the Category entity I used WithRequiredPrincipal() with t => t.Category argument, where the argument is the foreign key column in the dependent table.

If you use WithRequiredPrincipal() without an argument you'll get an extra column in the Book table and you'll have two foreign keys in the Book table pointing to CategoryId in Category table.

I hope this info helps.

UPDATE

Later on I found answer directly here:

http://msdn.microsoft.com/en-us/data/jj591620#RequiredToRequired

Answer 2

A reason why you're not getting cascading delete is because your relationship is optional.

If you want the relationship required i.e. an AppRegistration has to have one Agreement you can use (cascading delete configured automatically):

public class Agreement
{
    ...
    [Required]
    public AppRegistration AppRegistration{ get; set; }
}

If you want the relationship to be optional with cascading delete you can configure this using Fluent API:

modelBuilder.Entity<AppRegistration>()
    .HasOptional(a => a.Agreement)
    .WithOptionalDependent()
    .WillCascadeOnDelete(true);