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

asp.net asp.net-mvc c# entity-framework sql-server

Question

My website uses EntityFramework Code First and Asp.Net MVC 5. My website includes aRestaurant model with the next identifier:

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

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

Moreover, the code for theRestaurantType is:

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

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

    public virtual Restaurant Restaurant { get; set; }
}

When I attempt to remove therestaurant I encounter the following error based on the context:

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 want to manage this as quietly as I can because I have data in production. I tested the subsequent 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();

However, I continue to receive this problem. There isn't a row in the database with the restaurant ID, according to my search. Even so, I tested it:

        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 also didn't work. Is it possible for me to resolve this using C# code?

1
3
2/3/2014 8:47:19 AM

Popular Answer

Remove might not truly delete the row in RestaurantTypes, depending on your model. Instead, try DeleteObject (see .DeleteObject versus.Remove() in Entity Framework ()). Additionally, try committing the changes after deleting all RestaurantTypes to make sure that the deletion of Restaurant is not occurring before that.

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();

In addition, as a side remark (and unrelated to your problem message): Your database model's relationships seem to be in reverse. You should have a reference from the Restaurant to the RestaurantTypes if a Restaurant can have one or more RestaurantTypes. Therefore, you require a separate table to store the relationships, such as RelRestaurantRestaurantType:

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

Each restaurant would have its own copies of the types if you did it that way. Consider having 100 Chinese restaurants and having the type "Chinese." The result would be that your table RestaurantType would have 100 entries labeled "Chinese."

6
5/23/2017 12:17:18 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow