Entities saved in incorrect order without exposed Foreign Keys

I'm running into problems when I try to save entities that have a parent/child relationship in EF4. Sometimes, the child will be inserted before the parent - which obviously will cause problems from a referential constraint point of view. The two tables are structured like this:

OrderHeader
 - OrderID (PK)

OrderDetails
 - OrderID (PK)
 - DetailID (PK)

(there are more columns, but they don't matter).

Note: We don't expose a true foreign key on OrderDetails.OrderID but instead we use triggers to enforce that the OrderID of the Details table exists in the Header table. Exposing FKs might be a quick solution but in the our application, those kinds of changes to the database won't be allowed - we have to deal with legacy code, etc.

Looking at the XML in the .edmx file that the designer generates, I've noticed that it makes an AssociationSet and Association in the Conceptual Model (CSDL section) - and the Association is setup with a ReferentialConstraint. Howe开发者_开发问答ver, it doesn't make an AssociateSet or Assocation in the SSDL section. It seems like those only get added when the FK is exposed in the database. For other databases I've tested on (like AdventureWorks or Northwind) that do have true FKs in the database, when I generate the model from the database, I see that the EDMX includes Association and AssociationSet sections in the SSDL.

It appears that EF ignores the association that we have in the model (CSDL) and instead just does inserts alphabetically by table name - so we are out of luck in this case since OrderDetail sorts before OrderHeader (and we certainly don't have the power to rename tables in our case). If I manually add a corresponding AssociationSet and Association in the SSDL section, the save occurs in the correct order (first insert Header, then insert Detail). However, anytime we do an 'Update Model from Database' those manual changes go away so it's not a very practical solution. I've thought about trying to do that fix-up dynamically at runtime but it feels like that is a lot of effort to work around something that should 'just work'.

I'm hoping there is a way that EF can respect the Referential Constraint and/or Association that is defined in the CSDL when it orders Inserts.

I think I understand the problem now. You don't have relations in SSDL at all. When EF generates SQL commands it uses only information from SSDL so it doesn't know about dependency between entities defined in CSDL. I will verify it later but it looks like a bug / design flaw in EF architecture.

What can you do to avoid the bug? Define association sets manually in SSDL and use some better tool (commercial) to work with EF designer and update mapping - or go to manual maintaining of EDMX because designer is only for simple scenarios.

You can also report the behavior as a bug to MS Connect or put it as a support ticket if you have partnership with Microsoft but be aware that even if you get a solution it will take several months.

After hearing Ladislav Mrnkka's answer and having no luck of my own finding an 'easy' solution, I came up with an alternate solution. It took some effort but the end result is worth it in my opinion.

I made a Visual Studio Extension that uses the IModelTransofmrationExtension.OnBeforeModelSaved() method. In that method, you are given the EDMX document as an XDocument and you can manipulate it as desired before it gets saved. I use that opportunity to scan the CSDL section for Associations and Association Sets that don't exist in the SSDL. I then copy those into the SSDL section - after mapping Entity/Property names to Table/Column names with data in the MSL section.

This effectively 'tricks' EF into think there are real Foreign Keys for our associations so it does the INSERTs in the correct order.

The only down side is that everyone on our team that edits the models needs to have the extension installed - otherwise they won't get the generated FK Associations. Luckily we have just a few people that edit the models so this is manageable.

Short of changing to actually use FKs not sure that you can do what you want out of the box. Have you tried just putting the whole insert (in the correct order and using the output clause to get back the id if they are identities for the second insert) into a stored proc and having EF call the proc?