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 edite开发者_运维知识库d FK as follows:

• delete rule: cascade
• update rule: cascade
• enforce foreign key constraint: no

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.

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.

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.

• 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