Joe Stevens' BlogEverything .NET and C#

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:

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:

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