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!

Samovar

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

Statute

Wow! Excxellent answer! I need to check out your blog! :-) – Glance

The disadvantage of running sql from the Seed method is that it runs every time you call Update-Database. From EF5 onward you can run Sql from a single migration: https://mcmap.net/q/455469/-since-ef-doesn-39-t-support-delete-set-null-can-i-run-an-sql-command-outside-ef-to-do-it – Gundry

Excellent answer indeed! – Handcraft

Answer 2

With Entity Framework Core, I did something like this...

modelBuilder.Entity<Guest>()
   .HasOne(g => g.Language)
   .WithMany(l => l.Guests) // I needed a return collection
   .OnDelete(DeleteBehavior.SetNull);

Vange

unfrtunately this post is about EF not EF core. – Mitzi