CODEWITHSUNDEEP
sql-serverentity-framework
ahmet alp balkan
ahmet alp balkan

Reputation: 45242

Deleting Parent Entity of FK Constraint in Entity Framework & SQL Server

I'm using Entity Framework with .NET 4.0 and SQL Server 2008.

I have two entities A and B, A has many B's (there's a field on B table, i.e. B->many to one ->A). When I try to delete a specific parent entity A, the query fails

A is parent entity, B is children entity.

Here's the code I use for deletion of a particular A entity:-

var obj = (from A in context.As where A.id=someValue select A).First();
context.As.DeleteObject(obj);
context.SaveChanges();

However it throws the famous exception

The DELETE statement conflicted with the REFERENCE constraint "FK_bs_as". The conflict occurred in database "myDb", table "dbo.bs", column 'a'.

The statement has been terminated.

Then I looked at the foreign key constraint using SQL Server Management Studio and edited FK as follows:

and tried to set FK to ON DELETE CASCADE. However obviously there's still a problem with it.

I don't want to fetch every damn child entity (B) of the parent entity (A) and delete them individually. That's too expensive when compared to executing a single SQL query.

Upvotes: 0

Views: 2630

Answers (3)

Han Stehmann
Han Stehmann

Reputation: 1

  • Set in the association between A and B OnDelete to Cascade in EDMX
  • Generate Database from Model
  • Use the new model.edmx.sql to re-create the database
  • fill the database with your data
  • try again

Upvotes: 0

FilipRot
FilipRot

Reputation: 83

Be sure that your entity model is in sync with the database model. Refresh your entity model every time you make changes to the database (update model from database), otherwise you can get unexpected exceptions.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364349

What you need to do is:

  • Setting delete rule in the database as you did (don't set update rule and don't change enforcing constraints!)
  • Setting on delete rule on the relation in EDMX

The first step will ensure that cascade delete will work if your related entities are not loaded and second step will ensure that cascade delete delete will work if your related entities are loaded. Both steps are necessary.

Once you have this done you don't need to load related entities and delete them one by one.

Upvotes: 4

Related Questions