Open Access Executed Query in Wrong Sequence

[Fakhru]

I have this code :

using(var ctx = new DbContext())
{
  // test Id (sequence - Firebird)
   var test = new Test() { Name="0001" };
   db.Add(test);
   // Other doesn't have any relationship with Test
   var other = new Other() { Remarks = "None", Source=test.Id };
   db.Add(other );

   db.SaveChanges(ConcurrencyConflictsProcessingMode.StopOnFirst);

  // OpenAccess will exec this Sql  in this order
  // INSERT INTO Other (Remarks,Source) VALUES (?,?);
  // INSERT INTO Test (Id, Name) VALUES (?,?) returning Id;
}

This was wrong, because Other will need value from Test.Id first.
And how to use explicit Transaction in OpenAccessContext ?

[Petko_I]

Hi Fakhru,

There are several solutions to your case. One way to enforce the right foreign key constraint is to first persist the test entity by invoking context.SaveChanges() before assigning the id to the other entity.

var test = new Test() { Name="0001" };
db.Add(test);
db.SaveChanges();

var other = new Other() { Remarks = "None", Source=test.Id };
db.Add(other );

db.SaveChanges();

This way after the first changes are saved the property values for the new entity will be refreshed.
If you have an association between test and other entities and for example the class Other has a property Test, then you can assign the newly created test entity to the reference property of the related other.

var test = new Test() { Name="0001" };
db.Add(test);

var other = new Other() { Remarks = "None", Test = test };
db.Add(other );

db.SaveChanges();

In your case, the value for the Id property is supplied only after commit. Therefore assigning the Id property value to Source of other before having its real value will not work.

Notify us whether the remarks made above answer your questions. Do not hesitate to contact us if you need further assistance.


Greetings,
Petko_I
the Telerik team

[Fakhru]

Hi, thanks for your replay.

When I call SaveChanges on first added test entity,
and what if when I added other entity and failed on database because it duplicate Remarks for other entity can I rollback all change (rollback added test entity before) ?

What I want is this 2 process execute in 1 transaction, because of that I only call SaveChanges once.


Best Regards,
Fakhru Rahman

[Fakhru]

Any sugestion or pointer for this ?


Many thanks,
Fakhru Rahman

[Petko_I]

Hi Fakhru,

The SaveChanges() method acts like a transaction. All changes to the entities under the scope of the context will be applied successfully or the whole transaction will be rolled back. Unfortunately, you cannot control the order of insertions on the context level directly as this is something that should be handled transparently for the user. In your case you need to insert an entity that has its primary key calculated on the database server and also insert another entity that relies on the value provided by the server. As the test entity has not been persisted yet you can only make guesses as to what the real value of its identity is. Therefore, to take advantage of a batched update on the database and execute everything in one transaction you need to specify to the context that the other entity relies somehow on the value provided by the test entity. This can be accomplished if you use an association between the two persistent types and set the navigational reference value for the other entity as shown in the second example in the previous reply. Do you have an  association between the two entities? Can you tell us if the second example is a solution for you? Or are there any other considerations that apply and that prevent you from using the proposed approach?

We are looking forward to resolving this issue.

Best wishes,
Petko_I
the Telerik team