entity framework perform update before delete, I want to stop this

Question

I have Problem with entity framework I have two code first class Countries and Cities

  [Table("dbo.Countries")]
public class Country
{
    public int CountryId { get; set; }
    public string CountryNameAr { get; set; }
    public virtual ICollection<City> Cities { get; set; }
}

 [Table("dbo.Cities")]
public class City
{
    public int CityId { get; set; }
    public int CountryId { get; set; }
    public string CityNameAr { get; set; }
    public virtual Country Country { get; set; }

}

each country have many city,,, i already added some countries and cities. my problem is: when i delete any country it will update countryId in cities to null. and i already wrote in DBContext:

ModelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        ModelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

when I trace it in SQL server ... entity framework make update statement in cities table then delete statement in country table...

exec sp_executesql N'UPDATE [dbo].[Cities]
SET [CountryId] = NULL
WHERE ([CityId] = @0)    

exec sp_executesql N'DELETE dbo.Countries
WHERE (CountryId = @0)',N'@0 int',@0=6

i want to stop this .. i want entity framework refuse delete if their is any fk_ related with any table any one know how to resolve this problem????

Do not make your complete logic based in Entity Framework FK dependency, you shall check dependency before deleting any value. — Gaurav P, Oct 03 '16 at 07:28
How I will check plz because I'am using code first??? how i make my complete logic based in Entity Framework FK dependency — Ayman, Oct 03 '16 at 08:41

score 2 · Answer 1 · answered Oct 03 '16 at 07:22

2

Replace your City model with code below, all you need to do is to tell Entity that Country is required for a City to exist:

[Table("dbo.Cities")]
public class City
{
    public int CityId { get; set; }
    public int CountryId { get; set; }
    public string CityNameAr { get; set; }
    [Required]
    public virtual Country Country { get; set; }
}

After declaring Country as Required, the foreign key won't be generated as a Nullable column.

answered Oct 03 '16 at 07:22

sachin

2,341
12
24

Thank you my main problem resolved ... But what if the field not required ... I want to stop change any FK_ to null When delete any primary key .. – Ayman Oct 03 '16 at 09:00
You have to tell EntityFramework about the relationship being Required for each of the Entities. – sachin Oct 03 '16 at 09:05
I want it as my database if there is any FK_ entity framework must alert me. if i forget to put [Required] in entitiy may entity framework will null some important data for me ... – Ayman Oct 03 '16 at 10:16
If we forget to add Required, we're not configuring the model in the way we should. That is on us. We have to take care while defining the relationships. – sachin Oct 03 '16 at 10:26
I got it ... Thank you :) – Ayman Oct 03 '16 at 11:55

score 1 · Accepted Answer · edited May 23 '17 at 11:48

You shall check the dependency before deleting data. It will throw error in case there is foreign key dependency and you try to delete the Primary Key value on which table data is dependent. There are two options, either check dependency manually or use entity framework find the dependency. For manually checking column data dependency, the below below syntax will find the count.

using (var context = new YourDbContext())
{
    //check if the Country is not in used in City table
    var count = context.ModelNameGivenForCityDb.Count(u => u.CountryId== countryKeyToDelete);
    if(count == 0)
   {
      // code to delete
   }
}

Other option is to find the Foreign Key dependency using EF at run-time (note this logic will make you code dependent on database constraint). Check the below link to find foreign key dependency

Read foreign key metadata programatically with Entity Framework 4

entity framework perform update before delete, I want to stop this

2 Answers2