“A cycle was detected in the set of changes” with Linq to SQL

Question

I am currently developing an application in which i experience the exception "A cycle was detected in the set of changes" when calling DataContext.SubmitChanges(). I know why this exception is thrown but i have not been able to find a fix for my situation. Let me explain the situation. I have a database with a table as shown below which i access with LINQ to SQL so it gets mapped to classes in vb.net.

Device
-------
ID
DefaultGatewayID

The DefaultGatewayID is a Device an can even be the same object or another Device. The user uses a GUI with a DataGrid to alter and add new records. The updating records is no problem. The ID already exists and the DefaultGatewayObject is attached to the record (the ID is stored in database).

However when i try to add a new record and set the DefaultGatewayObject in the same transaction i get the 'Cycle detected in set of changes'-exception. I suspect this is caused by LINQ to SQL because it does not know which record to add first, although it is the same item in this case.

I do not have the option to break the insertion into two parts, one for the Device and then adding the DefaultGateway because my submit button is bound to a XAML Command which executes the SubmitChanges.

Ideally i would have some option to specify which object is to be created first, or something like that. I think it's an option to remove the connection to itself and just set the ID in this field, but i'd rather find a fix within LINQ to SQL.

I hope SO has an answer to this. I could only find this related post "Cycle detected while adding Circularly linked list"

Answer 1

You can break the insertion into two parts ans still have one transaction if you wrap your code in a TransactionScope.

Using trans As New TransactionScope()
    'Code that generates a new ID in the database
    dc.SubmitChanges()

    'Code that uses the new ID value.
    dc.SubmitChanges()

    trans.Complete()
End Using

This is the only way to avoid the exception. If this is impossible because of architectural decisions ("my submit button is bound to a XAML Command") you need to change the architecture. I think a UI command should never be so close to the data access layer anyway. You better call a service method from XAML.