Entity Framework 4.0 and self-referencing tables; .SaveChanges() fails on .AddObject()

PROBLEM SUMMARY:

When using self-referencing relationships and attempting to add objects, .SaveChanges() fails with “Unable to determine the principle end of the ‘PlannerModel.FK_PlanItem_PlanItem’ relationship. Multiple added entities may have the same primary key.”.

PROBLEM DETAILS:

plannerContext = new PlannerEntities2();

var unitPlanQuery = from d in plannerContext.UnitPlans 
                    where d.TeacherId == sourceTeacherId
                    orderby d.TeacherId
                    select d;

var planItem = new PlanItem();
ClonePlanItem(pi, planItem); // where pi is original PlanItem

planItem.ParentPlanItem = (PlanItem)pla开发者_JAVA技巧nItemsAddedHT[pi.ParentPlanItemId];

// above object on right is the previously added PlanItem

plannerContext.PlanItems.AddObject(planItem);

plannerContext.SaveChanges();

I went back to my code and commented it up such that I knew for sure only a single call to ‘plannerContect.PlanItems.AddObject(planItem)’ was taking place. Thus there was only a single object to insert. The error message changed to:

“Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.”

I went back and added “Allow Nulls” to the ParentPlanItemId column in SQL Management Studio (SQL Server 2008 btw), and refreshed my model…but this did not make a difference.

Table: PlanItem

PlanItem int PK, identity

ParentPlanItemID int , allow null

ItemText varchar(200)

Referential Constraint from Model Designer: Principal = PlanItem; Principal Key = PlanItemId; Dependant Property = ParentPlanItemId

Association from Model Designer:

AssociationSet Name: FK_PlanItem_PlanItem

End1 Multipllicity: 1(One Of PlanItem)

End1 Navigation prop: PlanItem1

End2 Multuplicity: * (Collection of PlanItem)

End2 Nav: ParentPlanItem

Name: FK_PlanItem_PlanItem

Have you tried setting directly

planItem.ParentPlanItemReference

property?

You would actually set it to a particular plan item identifier (that you already have from before as I understood you) that already exists in the DB. I'm not saying it would work, but it's worth a try. I've used these *Reference properties several times to speed up the process if insertion and sometimes also reading.

Check this blog post for further reference.

Turns out, setting the parent property like I was doing was not the problem. For a self-referencing table like this, the multiplicity needed to be (zero or 1) and the property of ParentPlanItemId needed to allow nulls.

Cheers to all!