New Posts New Posts RSS Feed: Unbeliveable error inserting child entity before parent
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Unbeliveable error inserting child entity before parent

 Post Reply Post Reply
Author
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105
Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Topic: Unbeliveable error inserting child entity before parent
    Posted: 15-Aug-2011 at 3:24pm
I wish it was a DevForce bug.  At least I'd be able to get a fast response and a quick patch :-)
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786
Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 15-Aug-2011 at 3:22pm
Hi pk55,
Thanks for the update.
Silvio.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105
Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 15-Aug-2011 at 3:06pm
This seems to be an EF issue (and I've posted on the EF forums).  We were able to reproduce this just using the EF ObjectContext outside of the DevForce Entity Manager. 
 
Other information is that it really seems to be a problem with EF not using the conceptual model's (CSDL) ReferentialConstraint when there isn't a storage (SSDL) AssociationSet (which gets generated when there are true database-backed foreign keys).  If we manually add the SSDL (just copying the CSDL version), EF inserts in the right order.  Of course, if you update the model from database in the designer, EF wipes out those manual edits of the EDMX file.
 
And if it ignores the CSDL Referential Constraint, then it just seems to use the name of the table for the order of inserts.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105
Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 13-Aug-2011 at 8:59pm

Here's the setup. Using a vanilla new 6.1.2.0 DevForce SL project.  Added  a new data model with just 2 entities from our domain model.  One is a parent table that has a single INT primary key (plus other columns but those don't matter). The second is a child table of the parent having a two-part key; both INTs.  The first part of the key is the parent table INT key and the second just a sequence number.  Child table also has others columns but again, they don't matter.

Parent Table: Table_B (you'll see why this name is important later)
Columns:
 MyKey INT NOT NULL
 other columns

Child Table: Table_A
 MyParentKey INT NOT NULL
 MyChildKey INT NOT NULL
 other columns

The child table has a database trigger that enforces RI (on SQL server 2008 r2 but this also happens on SQL 2008); you can't insert the child if the parent row doesn't exist.  Pretty standard.  There is NOT a database foreign key on either table. The domain model has an association between them.  Normal everyday stuff that works everywhere else except for these 2 tables.

Create one entity of each and add them to the entity manager (same as always).  Save Changes.  The relationship is populated correctly like any other entity relationship.

But, for some reason, EF or DevForce (not sure who) would always try to insert the child ahead of the parent and of course, the db trigger would catch it and throw an error.  I (and others) spent several days trying to figure it out.  Eventually I created a separate project to be sure it wasn't our custom T4s or our ancestor classes or our save inspectors (it wasn't).

So I created the vanilla project and after it failed there as well, I brought into the model another parent-child pair of tables.  Did the same thing and this time it worked in the vanilla DevForce project.  So what was different?

The names of the database tables seem to matter to either DevForce or EF (I'm guessing EF) when determining what order to generate inserts.  Oh I can see you right now shaking your head and saying, no that can't be.  EF looks at the EDM and figures out that child entity must wait for parent entity.   If only that were true.

The tables that worked were named so the parent table sorted alphabetically ahead of the child.  The tables that didn't work were named alphabetically backwards in the SQL server (the parent was named say "Table_B" and the child named "Table_A").  By simply renaming them from SQL Server and of course, refreshing the data model (have to get rid of the old child entity since EF has no clue its backing table has been renamed) so that the parent ordered ahead of the child in the SQL information schema, the exact same code worked and EF/DevForce inserts the parent first and the child second.

Note that the Entity names in the model don't seem to matter.  I left them named the original names (which were also alphabetically backwards).  Only the physical data store backed table name seemed to matter.

Prove it yourself.  Add a couple of tables to northwindIB with a trigger on the child where the child sorts ahead of the parent.

This also was broken in the 6.1.2."e" patch build but we didn't notice it until we added the database triggers back in.

So if someone from DevForce could definitely say it's an EF bug, I'll go throw bricks at the Microsoft team building :-)  otherwise, please tell me there's some magic work around.

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down