Open Access Executed Query in Wrong Sequence

5 posts, 0 answers
  1. Fakhru
    Fakhru avatar
    37 posts
    Member since:
    Oct 2007

    Posted 28 Feb 2011 Link to this post

    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 ?
  2. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 02 Mar 2011 Link to this post

    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
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
  3. DevCraft banner
  4. Fakhru
    Fakhru avatar
    37 posts
    Member since:
    Oct 2007

    Posted 02 Mar 2011 Link to this post

    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
  5. Fakhru
    Fakhru avatar
    37 posts
    Member since:
    Oct 2007

    Posted 07 Mar 2011 Link to this post

    Any sugestion or pointer for this ?


    Many thanks,
    Fakhru Rahman
  6. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 07 Mar 2011 Link to this post

    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
    Registration for Q1 2011 What’s New Webinar Week is now open. Mark your calendar for the week starting March 21st and book your seat for a walk through all the exciting stuff we ship with the new release!
Back to Top
DevCraft banner