Entity Framework: Set Delete Rule with CodeFirst

Question

I am using EF4 CTP 5, CodeFirst.

Please see my classes first:

public class Guest
{
        [Key]
        public Guid GuestID { get; set; }

        public Language PreferredLanguage { get; set; }
        public Guid? LanguageID { get; set; }
}

public class Language
{
        [Key]
        public Guid LanguageID { get; set; }

        [Required(ErrorMessage = "Enter language name")]
        [StringLength(50, ErrorMessage = "Language name is too long")]
        public string LanguageName { get; set; } // in origine language
}

My goal is to set a certain "Delete Rule" for the Guest-Language relationship. When a language is deleted, I do not want to delete the corresponding guests (so NO cascade delete). Instead I want the guest's LanguageID to be "Set NULL".

I was hoping for the fluent API to support me here. But I couldn't find anything helpful besides .WillCascadeOnDelete(bool), which does not provide the options I need. Did I miss anything? Or is this just not implemented in CTP 5?

Thanks for any help!

Answer 1

What you are looking for can be achieved by setting up an optional association between Guest and Language entities:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Guest>()
                .HasOptional(p => p.PreferredLanguage)
                .WithMany()
                .HasForeignKey(p => p.LanguageID);
}

Unit Test:

using (var context = new Context())
{
    var language = new Language()
    {
        LanguageName = "en"
    };
    var guest = new Guest()
    {
        PreferredLanguage = language
    };
    context.Guests.Add(guest);
    context.SaveChanges();

    context.Languages.Remove(language);
    context.SaveChanges();
}

As a result, we'll end up having a guest record with a DB null value for the LanguageID FK column.

Update:

First let's see why the above Unit Test succeeded by looking into SQL Profiler. The below shows the trace right after calling the second SaveChanges() method:

So as you can see, EF is smart enough to first update the guest record by setting its LanguageID to null and then submit a delete statement to remove the language record which is the default EF behavior when you set up an optional association. So it has been taken care of on the application side by EF and of course you'll get an error from the DBMS if you try to manually delete the language record inside the SQL Server like you also mentioned.

However, there is more to this story. Consider the following unit test:

using (var context = new Context())
{
    var language = new Language() { LanguageName = "en" };
    var guest = new Guest() { PreferredLanguage = language };
    context.Guests.Add(guest);
    context.SaveChanges();
}

using (var context = new Context())
{
    var language = context.Languages.First();        
    context.Languages.Remove(language);
    context.SaveChanges();
}     

This one fails with throwing a SQLException contains the exact message that you got from the SQL Server while trying to manually delete the record. The reason for that is because in the second unit test we do no have the related guest object loaded in the context so that EF is not aware of it and won't submit the necessary update statement like it did in the first example.

Back to your question, unfortunately EF Code First does not allow explicitly changing delete/update rule on relationships but we can always resort to SqlCommand method as you see an example of it in this post. In your case, we can code:

protected override void Seed(Context context)
{
    context.Database.SqlCommand("ALTER TABLE dbo.Guests DROP CONSTRAINT Guest_PreferredLanguage");
    context.Database.SqlCommand("ALTER TABLE dbo.Guests ADD CONSTRAINT Guest_PreferredLanguage FOREIGN KEY (LanguageID) REFERENCES dbo.Languages(LanguageID) ON UPDATE NO ACTION ON DELETE SET NULL");
}

Which is what you are looking for. With having the above seed method in place, the second unit test will also pass.

Hope this helps,
Morteza