Does Entity Framework support circular references?

Question

I have two entities in parent/child relationship. In addition, parent contains a reference to a "main" child, so the simplified model looks like this:

class Parent
{
   int ParentId;
   int? MainChildId;
}

class Child
{
   int ChildId;
   int ParentId;
}

The problem I am experiencing now is that EF does not seem to be able to handle creation of both Parent and Child in a single operation. I am getting an error "System.Data.UpdateException: Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values."

MainChildId is nullable, so it should be possible to generate a parent, a child and then update a parent with the newly generated ChildId. Is this something that EF does not support?

Answer 1

This is an old question but still relevant with Entity Framework 6.2.0. My solution is three-fold:

1. Do NOT set the MainChildId column as HasDatabaseGeneratedOption(Computed) (this blocks you from updating it later)
2. Use a Trigger to update the Parent when I'm inserting both records simultaneously (this isn't a problem if the parent already exists and I'm just adding a new child, so be sure the Trigger accounts for this somehow - was easy in my case)
3. After calling ctx.SaveChanges(), also be sure to call ctx.Entry(myParentEntity).Reload() to get any updates to the MainChildId column from the Trigger (EF won't automatically pick these up).

In my code below, Thing is the parent and ThingInstance is the child and has these requirements:

• Whenever a Thing (parent) is inserted, a ThingInstance (child) should also be inserted and set as the Thing's CurrentInstance (main child).
• Other ThingInstances (children) may be added to a Thing (parent) with or without becoming the CurrentInstance (main child)

This resulted in the following design: * EF Consumer must insert both records but leave CurrentInstanceId as null but be sure to set ThingInstance.Thing to the parent. * Trigger will detect if a ThingInstance.Thing.CurrentInstanceId is null. If so, then it will update it to the ThingInstance.Id. * EF Consumer must reload/refetch the data to view any updates by the trigger. * Two round-trips are still necessary but only one atomic call to ctx.SaveChanges is necessary and I don't have to deal with manual rollbacks. * I do have an extra trigger to manage, and there might be a more efficient way to do it than what I've done here with a cursor, but I'll never be doing this in a volume where performance will matter.

Database:

(Sorry, not tested this script - just generated it from my DB and put it here due to being in a hurry. You should definitely be able to get the important bits out of here.)

CREATE TABLE [dbo].[Thing](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Something] [nvarchar](255) NOT NULL,
    [CurrentInstanceId] [bigint] NULL,
 CONSTRAINT [PK_Thing] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ThingInstance](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ThingId] [bigint] NOT NULL,
    [SomethingElse] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_ThingInstance] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Thing]  WITH CHECK ADD  CONSTRAINT [FK_Thing_ThingInstance] FOREIGN KEY([CurrentInstanceId])
REFERENCES [dbo].[ThingInstance] ([Id])
GO
ALTER TABLE [dbo].[Thing] CHECK CONSTRAINT [FK_Thing_ThingInstance]
GO
ALTER TABLE [dbo].[ThingInstance]  WITH CHECK ADD  CONSTRAINT [FK_ThingInstance_Thing] FOREIGN KEY([ThingId])
REFERENCES [dbo].[Thing] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ThingInstance] CHECK CONSTRAINT [FK_ThingInstance_Thing]
GO

CREATE TRIGGER [dbo].[TR_ThingInstance_Insert] 
   ON  [dbo].[ThingInstance] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @thingId bigint;
    DECLARE @instanceId bigint;

    declare cur CURSOR LOCAL for
        select Id, ThingId from INSERTED
    open cur
        fetch next from cur into @instanceId, @thingId
        while @@FETCH_STATUS = 0 BEGIN
            DECLARE @CurrentInstanceId bigint = NULL;
            SELECT @CurrentInstanceId=CurrentInstanceId FROM Thing WHERE Id=@thingId
            IF @CurrentInstanceId IS NULL
            BEGIN
                UPDATE Thing SET CurrentInstanceId=@instanceId WHERE Id=@thingId
            END 
            fetch next from cur into @instanceId, @thingId
        END
    close cur
    deallocate cur
END
GO
ALTER TABLE [dbo].[ThingInstance] ENABLE TRIGGER [TR_ThingInstance_Insert]
GO

C# Inserts:

public Thing Inserts(long currentId, string something)
{
    using (var ctx = new MyContext())
    {
        Thing dbThing;
        ThingInstance instance;

        if (currentId > 0)
        {
            dbThing = ctx.Things
                .Include(t => t.CurrentInstance)
                .Single(t => t.Id == currentId);
            instance = dbThing.CurrentInstance;
        }
        else
        {
            dbThing = new Thing();
            instance = new ThingInstance
                {
                    Thing = dbThing,
                    SomethingElse = "asdf"
                };
            ctx.ThingInstances.Add(instance);
        }

        dbThing.Something = something;
        ctx.SaveChanges();
        ctx.Entry(dbThing).Reload();
        return dbThing;
    }
}

C# New Child:

public Thing AddInstance(long thingId)
{
    using (var ctx = new MyContext())
    {
        var dbThing = ctx.Things
                .Include(t => t.CurrentInstance)
                .Single(t => t.Id == thingId);

        dbThing.CurrentInstance = new ThingInstance { SomethingElse = "qwerty", ThingId = dbThing.Id };
        ctx.SaveChanges(); // Reload not necessary here
        return dbThing;
    }
}

Answer 2

I had this exact issue. The apparent "Circular reference" is simply good database design. Having a flag on the child table like "IsMainChild" is bad design, the attribute "MainChild" is a property of the parent not the child, so an FK in the parent is appropriate.

EF4.1 needs to figure out a way to handle these type of relationships natively and not force us to redesign our databases to accommodate deficiencies in the framework.

Anyhow my workaround is to do it several steps (like you might when writing a stored procedure to do the same) the only wrinkle is to get round the change tracking on the context.

Using context As New <<My DB Context>>

  ' assuming the parent and child are already attached to the context but not added to the database yet

  ' get a reference to the MainChild but remove the FK to the parent
  Dim child As Child = parent.MainChild
  child.ParentID = Nothing

  ' key bit detach the child from the tracking context so we are free to update the parent
  ' we have to drop down to the ObjectContext API for that
  CType(context, IObjectContextAdapter).ObjectContext.Detach(child)

  ' clear the reference on the parent to the child
  parent.MainChildID = Nothing

  ' save the parent
  context.Parents.Add(parent)
  context.SaveChanges()

  ' assign the newly added parent id to the child
  child.ParentID = parent.ParentID

  ' save the new child
  context.Children.Add(child)
  context.SaveChanges()

  ' wire up the Fk on the parent and save again
  parent.MainChildID = child.ChildID
  context.SaveChanges()  

  ' we're done wasn't that easier with EF?

End Using