Removing FK if there is no corresponding entry (linq-to-sql)

Question

Problem

I got two linked tables, with a foreign key that does not apply FK contraindications. The "child" table must be allowed to specify a "parent" key, which is either NULL or a non-existent identifier.

My data access is built using Linq2Sql, and it generates a one-to-many relationship in my table model.

The problem arises in the cleaning code, which daily looks at all changes for the last day and corrects any "errors" in the data.

Example

foreach (var user in data.Users) { // Check if the user has a specified office, which does not exists. if (user.OfficeId != null && user.Office == null) { user.OfficeId = null; // This throws exception } } 

Exception: System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException: operation is not valid due to the current state of the object

This is normal behavior and somewhat expected, so I tried to set the reference object. But how to define an "empty" office?

 var emptyOffice = new Office(); user.Office = emptyOffice; 

The above code sets the OfficeId to NULL in the user instance, but obviously it does not work during the upgrade as it tries to add a new “empty” Office to the database.

Possible Solution

In what I remain, my own SQL works, which does this update, although it would be nice to really do it through Linq2Sql, since there are other columns in the same row that I update when the associated "parent" does not exist.

Notes

There are some requirements that are important for any comments:

• Unable to modify database.
• Several systems depend on the same database schema.
• May potentially change to Entity Framework if it supports such a scenario.
• Running custom SQL is not a problem, and I will do it until I find the best solution.
• I would like to know and learn how to do this without a special code.

Answer 1

There are FKs in my code, so I have not tested this, but you tried to explicitly install

 user.Office = null; 

This should force the FK OfficeId be null.

If this does not work, then it must be:

 user.Office = new Office(); // or keep a cached dummy Office object to save time user.Office = null; 

This will make the FK link understand that its value has been changed and set to null.