Linq to SQL Quirks Part 4: Circular References

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

Tags: , , , , , ,

5 Responses to “Linq to SQL Quirks Part 4: Circular References”

  1. Wes says:

    Thank you, that solved the issue for me today with inserting.

    Does the same issue ever come up with updates?

    • wizzard says:

      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

  2. Adriano says:

    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

    • wizzard says:

      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.

  3. Slavik says:

    My version:
    db.ResourceVersions.InsertOnSubmit(newResourceVersion);
    db.SubmitChanges();

    newResource.LastVersion = newResourceVersion;
    db.SubmitChanges();

    ts.Complete();

Leave a Reply