Linq to SQL – Using EntitySet.Remove to delete records

Download source

Quite often you have two tables with a linker table which causes Linq to SQL to generate an EntitySet of the related records in each table.

Take the following example:

Entities

Here, the User entity will have an EntitySet called UserRoles containing the Role entities that the user is part of.

When I want to remove a role from a user what I’d like to do is the following:

user.UserRoles.Remove(userRole);

Unfortunately after doing this, when I try to submit the changes to the database I get the following error:

“An attempt was made to remove a relationship between a User and a UserRole. However, one of the relationship’s foreign keys (UserRole.UserID) cannot be set to null.”

This is because rather than deleting the record in UserRole, it is trying to set the UserID value to NULL, which won’t work as UserID and RoleID are composite keys.

To solve this issue I can use the DeleteOnNull attribute on the Association element between User and UserRole.

Open the DBML file in the XML Editor (right-click > Open with > XML Editor), and find the Association element under the UserRole table that couples it with the User table. In this element set the DeleteOnNull attribute to true:

xml

Now the same code will work as expected as when the UserID field tries to be set to NULL, it deletes the record.

Download source

Posted on by Joe in ASP.NET, C#, Linq

3 Responses to Linq to SQL – Using EntitySet.Remove to delete records

  1. rkay

    Thanks. I was in a similar situation and this really helped me.

  2. QiuLiang

    thank u!
    This article solved my problem.
    I’m from BeiJing , China :)

  3. Jonathan

    Thanks for this, I was battling on this and had previously created a class using the same method and forgot about the step in the XML … was going insane because it all looked right. Another case were interface has broken functionality. Often we rely too much on the gui representation of objects.

Add a Comment