CODEWITHSUNDEEP
c#sql-serverdatabaseentity-framework
xpasd
xpasd

Reputation: 81

How delete many-to-many relation in Entity Framework 6

I have a problem with the removal of items from the database if connects them a many to many relationship

My database look like

| [Project] | <-- | [JobInProject] | --> | [Job] |
=============     ==================     =========
| ProjectID |     | JobInProjectID |     | JobID |
|           |     | ProjectID      |     |       |
|           |     | JobID          |     |       |

Primary keys from Project and Job table are also set as foreign key in others tables but I think it isn't problem because when I remove item from Job table, it is removed correctly with all related items in others tables

All foreign keys are set by constraint and on delete cascade on update cascade

Code which I use to delete job item

 Job job = await db.Jobs.FindAsync(id);
 db.Entry(job).State = EntityState.Deleted;
 await db.SaveChangesAsync();

and project:

Project project = await db.Projects.FindAsync(id);
db.Entry(project).State = EntityState.Deleted;
await db.SaveChangesAsync();

Code to remove project item remove only data from Project table and JobInProject table do not remove Job and related items.

When I modify code to remove Project like this:

 Project project = await db.Projects.FindAsync(id);

 foreach (var item in project.JobInProjects)
 {
     db.Entry(item.Job).State = EntityState.Deleted;
 }

 db.Entry(project).State = EntityState.Deleted;
 await db.SaveChangesAsync();

I get an error on the await db.SaveChangesAsync(); line

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

How can I remove Project item and related Job items ?

I will be grateful for help

Upvotes: 6

Views: 8185

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

It's because you don't mark the JobInProject object as Deleted:

foreach (var item in project.JobInProjects)
{
    db.Entry(item.Job).State = EntityState.Deleted;
    db.Entry(item).State = EntityState.Deleted; // <= line added
}

If you don't do that, EF will assume you don't want to delete the item and try to set both foreign keys in JobInProject to null, but of course one or more of the foreign-key properties is non-nullable (both aren't).

Upvotes: 2

Related Questions