How to update FK to null when deleting optional related entity

Question

I'm reasonably new to EF, and struggling a little to facilitate deleting my objects. My two objects and associated DbContext look as follows:

public class Context: DbContext
{
    public Context() : base(){}
    public DbSet<Person> Persons {get;set;}
    public DbSet<Vehicle> Vehicles {get;set;}
}

public class Person
{
   public int PersonID {get;set;}
   public string Name {get;set;}
}

public class Vehicle
{
   public int VehicleID {get;set;}

   public int? PersonID {get;set;}

   [ForeignKey("PersonID")]
   public virtual Person Person {get;set;}
} 

As per above, one person can be linked to multiple vehicles. There is no explicit link from the person to the vehicle, but there is a link from the vehicle to a 'parent' person through the foreign key relationship.

I then create various vehicles in my code, and link these to optional person objects (foreign key is nullable).

My question is around deleting Person objects. I usually go about deleting the objects as follows:

private void DeletePerson()
{
    using (var context = new Context())
    {
        int personID = 4; //Determined through other code
        var person = context.Persons.Find(personID);
        context.Persons.Remove(person);
        context.SaveChanges();
    }
}

The above code however would fail, giving me a reference constraint exception (due to the vehicle foreign key). I however would have expected the foreign key of all vehicles linked to the specific person to simply be set to null?

I was able to get the code to work by explicitly loading all relevant vehicles to the context as follows:

private void DeletePerson()
{
    using (var context = new Context())
    {
        //Determined through other code
        int personID = 4; 
        // Single vehicle associated with this person, there can be multiple vehicles
        int vehicleID = 6; 

        var person = context.Persons.Find(personID);
        // Seems to force loading of the vehicle, facilitating setting 
        // its "PersonID" property to null
        var vehicle = context.Vehicles.Find(vehicleID); 
        context.Persons.Remove(person);
        context.SaveChanges();
    }
}

The problem with the above code however, is that I need to create a List object inside my Person class that contains a reference (or ID) to all potential dependent objects (vehicles is just one example here, there will be various other similar classes with similar relationships to Person).

Is the creation of this List in the Person object the only way of doing this? And is there some way of automating the creation of this list / automating the adding of the dependents? I'd prefer to not have to explicitly manage these relationships through a list object in my Person class.

Thanks!

Solution

Although SQL Server supports it, as you have guessed, EF is not able to set a cascading rule to nullify the FK when the related object is deleted: Entity Framework: Set Delete Rule with CodeFirst

So you need to include in the context the related objects, so that when you delete the Person the related vehicles are updated with a null PersonId. You don't need to include a list for this. You can make the DbContext aware of the related entities like this:

ctx.Vehicles.Where(v => v.PersonId == personId).Load();

Then, if you call delete, it will work as expected.

This is a sample DbContext, configured with fluent API, which works as expected:

public class SampleDbContext: DbContext
{
    public SampleDbContext()
        : base("name=CascadeOnDelete")
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Vehicle>()
            .HasOptional(v => v.Person)
            .WithMany()
            .HasForeignKey(v => v.PersonId);
            //.WillCascadeOnDelete();
        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Person> Persons {get;set;}
    public DbSet<Vehicle> Vehicles {get;set;}
}

public class Person
{
    public int PersonId {get;set;}
    public string Name {get;set;}
}

public class Vehicle
{
    public int VehicleId {get;set;}
    public string Model { get; set; }
    public int? PersonId { get; set; }
    public virtual Person Person {get;set;}
} 

And this console app shows the expected behavior:

class Program
{
    static void Main(string[] args)
    {
        using (var ctx = new SampleDbContext())
        {
            Console.WriteLine("Creating John McFlanagan and their 2 vehicles");
            var person = new Person {Name = "John McFlanagan"};
            var vehicle1 = new Vehicle { Person = person, Model = "Vauxhall Astra" };
            var vehicle2 = new Vehicle { Person = person, Model = "Ford Capri" };

            ctx.Vehicles.AddRange(new[] {vehicle1, vehicle2});
            ctx.SaveChanges();
        }

        using (var ctx = new SampleDbContext())
        {
            var person = ctx.Persons.First();
            // Loading related vehicles in the context
            ctx.Vehicles.Where(v => v.PersonId == person.PersonId).Load();
            Console.WriteLine("Deleting the person, and nullifying vehicles PersonId");
            ctx.Persons.Remove(person);
            ctx.SaveChanges();
        }

    }
}

In (EF7) EF Core it's possible to set the behaviour

Thanks to @Dabblernl comment: http://blogs.msdn.com/b/adonet/archive/2015/10/15/ef7-beta-8-available.aspx#comments

Diego B Vega [MSFT] 17 Oct 2015 9:21 PM # @DabblerNL yes, the functionality is already implemented in current nightly builds. You will have to explicitly specify it in the model using .OnDelete(DeleteBehavior.SetNull).

The previous link is dead. You can see the description of this model property here: http://www.learnentityframeworkcore.com/conventions/one-to-many-relationship

Comments

[Dabblernl]

As a consolation: it seems that EF7 will finally support On Delete Set Null blogs.msdn.com/b/adonet/archive/2015/10/15/…

[Matthias Burger]

links are dead, take a look here: learnentityframeworkcore.com/conventions/…

[SDJMcHattie]

That's pure black magic, but thank you. This saved my bacon.

[zolty13]

unfortunately it creates n updates sql in sql database. (where n is count of vehicles assigned to Person). Is it possible to generate one Update?

[JotaBe]

@zolty13 I think it's clearly explained that with EF 6.x it's not possible, but with EF Core it is (it can set the "ON DELETE" behavior, which happens automatically on the serves side). If you think this answer is useful, please, vote for it.

[zolty13]

I do know it. But hace some legacy app