LINQ to SQL Multiple DBML files

Question

Hi all,

Here is the scene..... I 2 separate contexts in LINQ, the actual database is one, and the contexts basicaly break concerns, and in this case, I using 3 tables.

Table 1 (AccountId) (Context 1)

Table 2 (PersonId) (Context 2)

Table 3 (AccountId, PersonId) (Context 2)

So what happens here is that (Context 2) can only create a record on Table 3 after a record has been created in Table 1.

With that in mind, I created a ActionScheduler, that takes all 3 Linq to SQL objects, and creates watchers on the properties that matter (AccountId, PersonId), and when both of them are not the default values ( 0 ), meanind that they have being inserted, the 3rd one (Table 3) object is then assined the AccountId, and added to Table 2s list of Table 3 objects.

So what I tecnically would have to do is basically call SubmitChanges 2 times and things would be good.

Well thats exactly when things fall apart. I make the first call, Table 1, gets inserted triggering the event on the ActionScheduler, that then updates Table 3 object, then Table 2 get inserted triggering Table 3 object, which now meet the requirement (Both PersonId, AccountId are valid), Table 3 object is added to Table 2 object. The second save is called, and there is no changes here , so nothing gets added at all, now if while having the same contexts open I add another set of objects, and save all over again, the new Table 3 object does not get added (same behavior) but the other one does with perfect values, so it seem like this guys is always one step behind.

Any sugestions? Is there a better way of doing this, how exaclty is the SubmitChanges lifecycle?

Sorry for all the questions, but this is really bugging me. And an obvious thanks.

Answer 1

You really shouldn't be breaking a single database up into separate contexts. If you consolidate down to a single context you can then do:

YourDataContext db = new YourDataContext();

Table1 account = new Table1();
db.Table1s.InsertOnSubmit(account);

Table2 person = new Table2();
db.Table2s.InsertOnSubmit(person);

Table3 link = new Table3();
link.Account = account;
link.Person = person;
db.Table3s.InsertOnSubmit(link);

db.SubmitChanges();

That will submit all three in a single call and (assuming the ID properties are identity columns) update the IDs on your objects.