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
No related posts.
Tags: .NET, .NET 3.5, Circular Reference, Insert, Linq, Linq to SQL, Workarounds
Thank you, that solved the issue for me today with inserting.
Does the same issue ever come up with updates?
No – the reason it is a problem with inserts is that Linq to SQL doesn’t know which way around to insert the records because they both have IDs referencing each other and neither ID exists yet because they haven’t been inserted. With updates, the records already have IDs, so it just keeps those
Hi,
You solved this problem by breaking the circular reference in two operations. But don’t you need a transaction for these two operations? I mean, to treat these operation as one operation only?
Thanks,
Adriano
Ideally, yes, and if you can work out a way to do it in a single transaction, that would be better. However, I was really just focusing on the problem and a workaround. It isn’t a perfect workaround, but the risk of failure between the 2 transactions is fairly small, and in the case of the system that I was working on, the consequences are not so severe.