How to make EF execute Insert Queries in the right order?

Question

Hello,

I am using the Entity Framework with a MySQL server because I want to be able to do nice and easy LINQ queries againy my MySQL database. I have a very simple problem and I'm frustrated because I can't believe that the EF is making such a horrible mistake.

To simplify, I have parent and child classes, and I want to execute two inserts in one transaction.

To demonstrate: I have A, B, C and D. A is parent of B and C, B and C are parents of D (it needs to be like that).

I do the following:

B b = new B() { B_ID = 1 };
A.Bs.Add(b);
C.Ds.Add(new D() { B_ID = b.B_ID } );

I am doing it this way because all this actually happens inside the C class. Why am I getting an UpdateException (Entitities in '...' participate in the 'BD' relationship. 0 related 'B' were found. 1 'B' is expected)? Because when I leave out the last command (inserting D, child of B) it works fine, and when I issue an insert afterwards (ie. in a new transaction), it works fine as well.

Can it be that EF is blindly inserting elements in a random order, but noticing for itself that it can't be? Or am I approaching the problem in a completely wrong way?

EF version: I have the .NET FX 3.5 SP1, so from what I know it is version 1.

Update: Very sorry, tracked down the error to a mistake I made, so now the error doesn't happen on the EF level (with the exception I said above), but actually an INSERT for the chidl element is sent to the DB, I can see it in the log. So the problem still persists, but the exception is different (a foreign key constraint fails).

Thank you for your help, Michael

(edited to answer comments)

Answer 1

Does b.B_ID represent an identity column (or other ID that's autogenerated by the database)? If so, then it won't have a usable value until after its insert operation takes place. In this case, you'll have to set one side of the relationship using an object reference rather than a database identifier.

Either:

C.Ds.Add(new D() { B = b });

Or

b.Ds.Add(new D());

Answer 2

After hours of trying around I think I've had a breakthrough. This needs some more testing but I found that the EF can handle INT relationships much better than VARCHAR(n) ones. Going to update this answer as soon as I know it.

Just though someone else would be struggling as well...

Edit: Yup, definitely. For my configuration (EF1/MySQL), the EF puts child-INSERTs first if the primary key of the parent (and the foreign key of the child) is a VARCHAR. I tried with VARCHAR(120) and VARCHAR(255), none worked.