LINQ to SQL Entity Association Quirks

On my current project, we’re trying to adhere to the Simplest Thing That Could Possibly Work principle and this unfortunately led to some pains with LINQ to SQL surfacing after we had claimed victory on a few stories.

We are early in this project, and didn’t have a wire-frame of what the screen flow would eventually look like, so we were just adding buttons to allow a user to see each piece of functionality as we delivered stories. But, as we finished each story, it became more and more clear that we needed to do some proper UI soon.

Enter “The Wizard”
We realized that the independent stories were really leading up to a flow of execution that a wizard style approach seemed to be a perfect fit for. Things came together pretty well, until I started to mess around with back-and-forward navigation and I started to get “An attempt was made to remove a relationship between a PARENT and a CHILD. However, one of the relationship’s foreign keys (CHILD.PARENT_ID) cannot be set to null”. This only occurred when I did any back-and-forward navigation and if I only navigated the happy-path straight through the wizard, things worked just fine.

Part of my problem was that I wasn’t submitting my changes until the user completed work in the wizard, but I was running code to disassociate existing entities (that may or may not have been persisted to the database) from local collections – each time the user left particular steps in the wizard. I was able to overcome some of those quirks with the “what didn’t work” approach below, but I still would saw the “null relationship” error on a fairly regular basis.

After much time searching Google, and trying a number of approaches as recommended here and here, I started to realize that I wasn’t properly disassociating my entities from their parent relationships before I deleted them.

What didn’t work:

try{
Repository.Delete(pricingElementDepartment);
}
catch(InvalidOperationException ) {}

What did work:

try{
childEntity.Parent = null;
Repository.Delete(childEntity);
}
catch(InvalidOperationException ) {}