Preventing circular references in self-referencing table RRS feed

  • Question

  • I have a table named Items with the following structure:

    ItemID uniqueidentifier primary key,
    Name nvarchar(100) not null,
    ParentItemID uniqueidentifier null

    I'm using EF 4 with self-tracking entities. I have a method named UpdateItem. Inside that method, I want to check for a circular reference before allowing the updated item to be saved. It's easy enough to query the table and look at existing ParentID references, but I want to make sure my code is executed in a transaction such that I'm guaranteed that if a given item is updated, it can't possibly circularly reference itself.

    My restrictions are these:

    1. I can't use triggers, functions, views, or stored procedures. This has to be done through either the table definition itself or through code.

    2. The solution needs to work on SQL Server 2005 Express and SQL Server 2008 Express.

    I am somewhat familiar with TransactionScope, but I'm not sure which option (ReadCommitted, RepeatableRead, etc.) to use, and the transaction needs to be a local transaction (it can't promote).

    Any assistance would be greatly appreciated.

    Wednesday, October 26, 2011 7:30 PM