Fix 'The DELETE statement conflicted with the REFERENCE constraint' error with code

Question

I have an Asp.Net MVC 5 website with EntityFramework Code First. In my site, I have a Restaurant model with the following code:

public class Restaurant
{
    [Required]
    public string Name { get; set; }

    //....
    public virtual IList<RestaurantType> Types { get; set; }
}

And the code for the RestaurantType is:

public class RestaurantType
{
    [Key]
    public int ID { get; set; }

    [Required]
    public string Type { get; set; }

    public virtual Restaurant Restaurant { get; set; }
}

When I try to delete the restaurant from context, I get the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.RestaurantTypes_dbo.Restaurants_Restaurant_ID". The conflict occurred in database "aspnet-Test-20131111052251", table "dbo.RestaurantTypes", column 'Restaurant_ID'. The statement has been terminated.

I have data in production and I want to handle this as silently as possible. I tried the following code:

            for (int i = 0; i < restaurant.Types.Count; i++)
            {
                var type = restaurant.Types[i];
                db.RestaurantTypes.Remove(type);
                restaurant.Types.Remove(type);
            }

            db.Restaurants.Remove(restaurant);
            await db.SaveChangesAsync();

But I get the same error. I checked the database and there's no row with the restaurant ID. I even tried this:

        var list = db.RestaurantTypes.Where(t => t.Restaurant == null || t.Restaurant.ID == restaurant.ID);
        foreach (var ib in list)
        {
            db.RestaurantTypes.Remove(ib);
        }
        db.SaveChanges();

It didn't work either. Is there a way for me to fix this with C# code?

Answer 1

Depending on your model, Remove might not actually delete the row in RestaurantTypes. Try using DeleteObject instead (see Entity Framework .Remove() vs. .DeleteObject()). Also to be sure that the deletion of Restaurant is not happening before the deletion of all RestaurantTypes, try commiting the changes after deletion of all RestaurantTypes:

for (int i = 0; i < restaurant.Types.Count; i++)
{
    var type = restaurant.Types[i];
    db.DeleteObject(type);
    restaurant.Types.Remove(type);
}
db.SaveChanges();

db.DeleteObject(restaurant);
db.SaveChanges();

Also, on a side note (this has nothing to do with your error message): Aren't the relationships in your database model backwards. If a Restaurant can have one or many RestaurantTypes, you should have a references from the Restaurant to the RestaurantTypes. That means you need an additional table that holds the relationships (e.g. RelRestaurantRestaurantType):

---------------                      ------------------                          ------------------ 
|             |                      | Rel            |                          |                |
| Restaurant  | <-- Restaurant_ID -- | Restaurant     | -- RestaurantType_ID --> | RestaurantType |
|             |                      | RestaurantType |                          |                |
---------------                      ------------------                          ------------------

The way you are doing it, each Restaurant would have it's own copies of types. E.g. let's say you have a type "Chinese" and you are having 100 chinese restaurants. Then you would end up with 100 "Chinese" entries in your table RestaurantType.