EF delete many to many relationship

Question

Imagine this database model:

public class User
{
  public int Id { get; set; }
  public string Username { get; set; }
  public string Firstname { get; set; }
  public ICollection<Role> Roles { get; set; }
}

public class Role
{
  public int Id { get; set; }
  public string RoleType { get; set; }
  public ICollection<User> Users { get; set; }
}

There is an intermediate table that looks like this (not present as a POCO):

UserRole UserId RoleId

Then i decided to delete a role, which means that all the relationships of this role in the intermediate table should be deleted as well.

No matter what I try, I get either this error message:

The DELETE statement conflicted with the REFERENCE constraint "FK_UserRole_Role". The conflict occurred in database "dbname", table "dbo.UserRole", column 'RoleId'.

Or this error message:

The object cannot be deleted because it was not found in the ObjectStateManager.

The first error message comes from this try:

_dataContext.Entry(role).State = EntityState.Deleted;
_dataContext.SaveChanges();

This one is responsible for the second error message:

_dataContext.Circuit.Remove(role);
_dataContext.SaveChanges();

I did a few other tries, but I don't remember them as I have been trying to get this working from this morning (GMT +2).

Can anyone point me into the right direction?

score 5 · Accepted Answer · edited May 23 '17 at 12:31

You can make the second exception disappear by attaching the role to the context first before you call Remove:

_dataContext.Roles.Attach(role);
_dataContext.Roles.Remove(role);
_dataContext.SaveChanges();

But then you will most likely get the first exception also for this code because the real problem is that you apparently don't have enabled cascading delete in the database for the two relationships from UserRoles table to Users table and Roles table respectively.

You can set the Delete Rule for both relationships to "Cascade" in SQL Server Management Studio like show here for example. After that deleting a role should also delete the entries in the UserRoles link table.

Edit

When you load the related users you can also delete a role successfully without having cascading delete enabled:

var role = _dataContext.Roles.Include(r => r.Users)
    .Single(r => r.Id == someRoleId);

_dataContext.Roles.Remove(role);
// _dataContext.Entry(role).State = EntityState.Deleted; will work as well
_dataContext.SaveChanges();

The difference is that when the related users are attached to the context together with the role EF will send a separate DELETE statement for each row in the UserRoles link table and then a DELETE statement for the role so that it works without violating a FK constraint.

Hi, well the crazy thing is, this exact scenario is being used in 2 other entities where cascade isn't enabled. But it does remove the entity and the entries in the link table. I just can't find how different this scenario is than the other one where it works like a charm. — Yustme, Jul 09 '13 at 16:04
@Yustme: Possibly the difference is that you have loaded/attached the related objects in the other relationship. Deleting works differently then and it will indeed work without cascading delete. See my Edit above. — Slauma, Jul 09 '13 at 17:10
I came a cross a sample like that too. But when i use 'Include', it's expecting a string. Can't use lambda expressions. Any idea why? — Yustme, Jul 09 '13 at 17:22
@Yustme: You must add `using System.Data.Entity;` to your code file. — Slauma, Jul 09 '13 at 17:25

EF delete many to many relationship

1 Answers1