Posts Tagged ‘Insert’

Linq to SQL Quirks Part 4: Circular References

Friday, July 10th, 2009
This entry is part of a series, Linq to SQL Quirks»

In a project that I am working on at the moment, I have a situation where I have a Person table and a Family table.  The Person table has a FamilyID field, which is a foreign key to the Families ID (primary key) field, and the Family table has a DefaultPersonID, which is another foreign key back to Person to store the default contact for a family.

The problem is that if I create a new Person and Family with the Person being the DefaultContact, I get an InvalidOperationException thrown with a message “A cycle was detected in the set of changes”.  The problem is that Linq to SQL doesn’t know what to store first.  The only way that I have come up with to get around this is to do the following…

            If Person.ID = 0 AndAlso Not Person.Family Is Nothing Then
               
                Dim fam = Person.Family
                Person.Family = Nothing

                DBContext.SubmitChanges()
                Person.Family = fam

            End If

            DBContext.SubmitChanges()

This works by breaking the circular reference, so that there is only a 1 way reference, then submitting to the database, and once submitted, restoring the removed reference and submitting changes again

Linq to SQL Quirks Part 1: Implicit Inserts

Tuesday, June 23rd, 2009
This entry is part of a series, Linq to SQL Quirks»

There are a few things that Linq to SQL can be a bit funny about that aren’t so obvious.

First of all, there are what I call implicit inserts.  You might not have realised that you don’t actually need to explicitly call InsertOnSubmit on a table for Linq to SQL to do the insert as long as you have created a relationship to an object which already exists in the database.  Linq to SQL will automatically insert the new object on the next SubmitChanges call.