none
Transactions and SqlCe RRS feed

  • Question

  • It is my understanding the DbContext.SaveChanges does all of its database updates within a transaction and that SQLCe supports transactions. But I have a case where this did not seem to be happening.

    I have SQL Ce table with a unique constraint mapped to an entity. I want to flip the values of two entities that are evolved with this constraint. I thought if I do just one SaveChanges after the two modifies that it would work without getting a database unique key constraint error. But it doesn't.

    I not sure where the problem lies. Is EF not doing the transaction? Or is Sql CE not deferring constraint checking until the end of the transaction? Do I need to turn some option on?

    I can't find any thing about transactions in Sql Ce except that they happen automatically by SQL statement unless you say Begin Transaction. 



    Saturday, October 1, 2011 2:22 AM

Answers

  •  

    I figured out how to get the  ObjectContext from DBContext:

    Private pObjectContext As System.Data.Objects.ObjectContext =
    
    		 CType(Me, IObjectContextAdapter).ObjectContext
    

    In then call ObjectConext's SaveChanges within my transaction scope using parameter:

     System.Data.Objects.SaveOptions.DetectChangesBeforeSave
    

    At the end of my Transaction scope, I call ObjectContext's AcceptAllChanges method. In the Catch clause, I can now rollback the changes by setting the Entry.State to Unchanged.

    It is now working fine.

     

    • Marked as answer by DavidSherwood Saturday, October 8, 2011 9:32 PM
    Saturday, October 8, 2011 9:32 PM

All replies

  • Hi,

    Constraints and transactions are unrelated. If a key is unique, it must be ALWAYS unique including during the transaction (else when you are in the transaction you have no way to distinguish each row anyway).

    I know you can disable constraints but after a quick look at the doc this is for FK, CHECK constraints. You can't disable default, PK and unique constraints (for the last two it can give unexpected results as once again you can process unexpectedly several rows rather than a single row).

    Knowing your use case would perhaps helps. Usually this is done by using an intermediate step but I've never have yet to use that with EF. It could be perhaps better to implement this server side so that you don't have to deal with this explicitely on the EF side ?

    Also double check you can't do otherwise. Usually updating a unique value is considered as being to avoid as much as possible. Is this some kind of user unique code ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Saturday, October 1, 2011 9:08 AM
  • Yes, I do use an intermediate step in other places. Say that I'm flipping "1" and "2". I would:

    1) change the first to "3"

    2) change the second to "1"

    3) change the first to "2"

    This requires two changes to the first entity, but EF only makes the last change. I'm forced to do a SaveChages after step 1) and now the update is not happening under one transaction.

    Saturday, October 1, 2011 10:46 PM
  • You could wrap both calls inside an outer explicit TransactionScope. See http://geekswithblogs.net/SudheersBlog/archive/2010/04/11/139193.aspx.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Sunday, October 2, 2011 11:11 AM
  • Looks like what I want to do, but the blog is using ObjectContext and I'm using DBContext and it doesn't have the Connection property. 

    So, how do I get the Connection?

    Sunday, October 2, 2011 5:08 PM
  • Since Connection is not a property of DbContext, I added my own property. Then I added a constructor that requires a EntityConnection which I then save in the property. So now I create both the EntityConnection and the DbContext. 

    Why doesn't DbContext have this property so I wouldn't have to do this?

    I did as the blog you referenced said and it worked. When I caused an exception after the second update, the database did roll back. But EF considered the changes successful. So I had to save the original values myself and in the Catch:

    1. fetch the entries for the properties

    2. set their original values to my saved version

    3. fetch the entries for the entities

    4. change their state to "Unchanged"

    I notice ObjectContext has a AcceptAllChanges method and an option on SaveChanges that prevents its automatic issue. Why doesn't DbContext? Isn't this what I need to do? Or maybe EF should not AcceptAllChanges when it sees the connection already open and then do it when the connection closes. 

    Having to do my own role back could get quite involved.    

     

    Sunday, October 2, 2011 7:40 PM
  • Looks like what I want to do, but the blog is using ObjectContext and I'm using DBContext and it doesn't have the Connection property. 

    So, how do I get the Connection?


    How is the difference between dbcontext and objectcontext? they are exact the same. you can also use transactionscope in dbcontext like datacontext or objectcontext.

    did you have any other concerns?

    Thanks,

    Werewolf,


    Just a newbie for everything.
    Tuesday, October 4, 2011 3:25 AM
  • How is DbContext and ObjectContext the same?  DbContext does not have AcceptAllChanges nor any option on the SaveChanges to stop the automatic issue. Both of these things I need inorder to rollback my extended transaction.

    So how can I do it with DbContext?

    Wednesday, October 5, 2011 6:05 PM
  •  

    I figured out how to get the  ObjectContext from DBContext:

    Private pObjectContext As System.Data.Objects.ObjectContext =
    
    		 CType(Me, IObjectContextAdapter).ObjectContext
    

    In then call ObjectConext's SaveChanges within my transaction scope using parameter:

     System.Data.Objects.SaveOptions.DetectChangesBeforeSave
    

    At the end of my Transaction scope, I call ObjectContext's AcceptAllChanges method. In the Catch clause, I can now rollback the changes by setting the Entry.State to Unchanged.

    It is now working fine.

     

    • Marked as answer by DavidSherwood Saturday, October 8, 2011 9:32 PM
    Saturday, October 8, 2011 9:32 PM