3

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. However, 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.

Stephen McDaniel
  • 2,938
  • 2
  • 24
  • 53
  • Do you have some reproducible sample? I did this several times and it simply worked (in EFv4). – Ladislav Mrnka Aug 16 '11 at 07:00
  • It worked without FKs? It breaks every time for me. A coworker has posted full details on the EF Forums. You can see a reproducible case here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/31ffcfa0-e69e-460b-a767-d28376a57fe9 The big thing seems to be not having an Assocation and AssociationSet in the SSDL section. When it works for you, is there an Association in the SSDL section? – Stephen McDaniel Aug 16 '11 at 07:11
  • And of course....if you don't have special triggers or aren't profiling the SQL, it can be easy to miss the 'incorrect' behavior. Without FKs, SQL Server might let you insert a Detail before the Header but that is the wrong behavior. – Stephen McDaniel Aug 16 '11 at 07:17
  • Your description looks like common [FK association](http://stackoverflow.com/questions/5281974/code-first-independent-associations-vs-foreign-key-associations/5282275#5282275) which doesn't have it counter part in SSDL. I will check it at home. – Ladislav Mrnka Aug 16 '11 at 07:18
  • Btw. are you using EFv4 (.NET 4.0)? You probably have to because referential constrains didn't exist in .NET 3.5. – Ladislav Mrnka Aug 16 '11 at 07:20
  • Yup, we're using EF4/.Net4 (but not EF4.1 - I might try the latest). In other DBs with 'real' FKs, the designer does write data to the SSDL - and that seems to be the only way it works...although, I'm hoping there is some other way (I updated the question to include these answers - thanks). – Stephen McDaniel Aug 16 '11 at 07:30

3 Answers3

3

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.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thanks for the information. That confirms what I was afraid of - we need the association in the SSDL for EF to take it into account. I've looked at that tool and it seems promising but still requires effort on an ongoing effort. I'm going to explore making my own Visual Studio Add-in to do this. We'll see how that goes. Any additional information you find would be very helpful. – Stephen McDaniel Aug 16 '11 at 23:45
  • 1
    Even EF6 has the same...misbehavior :( – Pavel Voronin Nov 25 '13 at 14:39
2

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.

Stephen McDaniel
  • 2,938
  • 2
  • 24
  • 53
1

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?

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • We thought about ordering the inserts manually (either via a Stored Procedure or multiple calls to SaveChanges) but we have many, many tables like this - and some with even more complex relationships (parent > child > grand child), etc. so we are really trying to avoid that. – Stephen McDaniel Aug 15 '11 at 22:27