Ayman Ayman - 2 months ago 14
ASP.NET (C#) Question

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

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????

Answer

You shall handle dependency before deleting data, otherwise it will throw error if there is foreign key dependency and you try to delete data. There are two option either you check dependency manually or let entity framework find the dependency. Check Manually before deleting data, check below syntax

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 code at runtime(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