Insert order

Hi,

I was just wondering if it was possible to do automatic insert ordering to meet foreign key constraints; I am likely to be missing something obvious! First the situation example;

 I have an EmailMessage class which defines three properties:

To (many)
Bcc (many)
From (one)

Each of the properties uses an inheritance model - EmailMessageAddress with a ToEmailMessageAddress, BccEmailMessageAddress etc which links it all up.

In code when I create an email message, I would ideally like to be able to do something like:

EmailMessage msg = new EmailMessage();
msg.From = new FromEmailMessageAddress(...);

and then simply be able to add the email message in one line; i.e.

UnitOfWorkScope.Current.Add(message);
SaveChanges();

At the moment when I do this it gives a foregin key constraint error - I'm guessing because it is adding the ToEmailMessageAddress etc first when in this case it should be added second (ie it needs to link back to the messageid).

Is there a way to get around this, or is the correct method to add and save each object in the correct order manually? I don't mind either way - I'm still getting my head around all the hidden things the library can do and thought this might be one of them.

Thanks for your help.

LightSpeed analyses your model to figure out a safe order for inserts.  Basically if Entity A has a to-one association with (effective, a foreign key to) Entity B, it will insert As before Bs Bs before As.  (Edit: doh!)  So you should be able to add a mix of As and Bs to the unit of work, save them all in one go, and let LightSpeed take care of ordering them so that it works.

So I'm not sure what's going wrong in your case.  Clearly something with a FK is getting saved before the target of the FK, but I think we need to drill into exactly what is being saved when so as to make sure it's what you think it is.

Could you try the following:

1. Turn on SQL logging, by setting LightSpeedContext.Logger = new TraceLogger(); (or new ConsoleLogger() depending on your environment) -- this will tell us in what order LightSpeed is performing the inserts.

2. Capture the exception message (and if necessary the inner exception) relating to the foreign key constraint -- this should tell you exactly which foreign key is being violated.

If you could post this info, ideally with the code for the EmailMessage and *EmailMessageAddress entities, then we can take a look at this and figure out what's going on.  Thanks!

That should of course say "Bs before As".  What I'm trying to say is that LightSpeed knows that the target (PK end) of a FK relationship has to be inserted before the source (FK end) of a FK relationship.

 Thanks for that Ivan; I wasn't seeing the woods throught the trees but logging came to the rescue!

It came down to a case of a wrongly named field (so it wasn't populated correctly) and a circular reference.