Removing FK when there are no related record (linq-to-sql)

Problem

I got two related tables, with a foreign key that doesn't enforce FK contraints. The "child" table should be allowed to specify a "parent" key, which is either NULL or non-existing ID.

My data access is built using Linq2Sql and it generates a one-to-many relation in the model of my tables.

The problem comes in some cleanup code, which will daily look through all the changes for the last day and fix 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 instead. But how do I define an "empty" Office?

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

The above code sets the OfficeId to NULL on the user instance, but obviously it fails during update开发者_Python百科 as it tries to add a new "empty" Office to the database.

Possible solution

What I'm left with is running my own SQL which does the update, though it would be nice to actually be able to do this through Linq2Sql as there are other columns on the same row I'm updating when the related "parent" is non-existing.

Notes

There are some requirements here that is important for any comments:

• The database cannot be changed.
• Multiple systems is dependent on the same database schema.
• Can potentially change to Entity Framework, if it supports such a scenario.
• Running a custom SQL is not a problem and I'll be doing that until I find a better solution.
• Would like to learn and know how to do this without any special code.

My code has FKs enforced, so I haven't tested this, but have you tried explicitly setting

user.Office = null;

That should force the FK value OfficeId to null, too.

If that doesn't work, then it the following certainly should:

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

That will force the FK reference to realize that its value has been changed and set to null.