ADO.NET Entity Data Model Allows Circular Foreign Key Refs

Question

I am using the new ADO.NET Entity Data Model on a simple database. I have a table/entity with a primary key (PageID) and a ParentID foreign key that references back to itself on PageID for a parent/child "0..1 to many" relationship. On a ASP.Net page, I'm using a FormView with a asp:DynamicControl to express this as a control. That part works fine except for one crucial detail: When the page renders, the list of possible parents includes itself, and setting an item to have a parent of itself causes no errors and saves to the database. Obviously a hierarchical object isn't supposed to have a parent of itself, so how do I restrict this behavior?

As far as I can see, my options are:

• Somehow change the ForeignKey_edit.ascx in the DynamicData FieldTemplates. This is problematic because I can't see how to get a reference to the current entity's primary key to remove it from the possible foreign key choices. Plus, this will potentially hose any non-hierarchical fkey refs usages where the keys happen to collide.

• Do some PreRender jiggery-pokery on the page to try to remove the ListItem from the rendered DropDownList. This doesn't seem like the Right Way as it only fixes it for the page in question. EDIT: This is how I'm solving it currently.

• Some sort of insert/update trigger to force the ParentID to null if it matches itself. This isn't good because it fails silently from the user's perspective.

Has anyone out there got a better way? Also, let me know if I need to provide any more detail.

-Kelly

Answer 1

I ended up moving the hierarchy to an assembly table because I had to store more than just parent/child relationships. Doing so also meant I could dump the DynamicData control and use a more user-intuitive TreeView with drag-n-drop server-side events (ComponentArt). Because of the nature of the control, no item could be it's own parent so the problem was moot. Wow, 7 months is a long time in my first experience with Entity Framework :)