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
- Linq to SQL Quirks Part 2: DeleteOnSubmit and Entity not Attached
- Linq to SQL Quirks Part 3 - Extension to Get Around the Delete Problem
- Linq to SQL Quirks Part 4: Circular References