Alex Rivera | Logout

Entity Framework: Using transactions and rollbacks... Possible?

Asked 2009-01-16T21:39:04.590
18

Issue: (With Sql 2005)

  • How can I query the database while the transaction is up? (Since it locks the table)
  • How can cause a transaction to rollback and then close itself to allow the table to be queried?

So I found this much:

[TestMethod]
public void CreateUser()
{
    TransactionScope transactionScope = new TransactionScope();

    DataContextHandler.Context.AddToForumUser(userToTest);
    DataContextHandler.Context.SaveChanges();

    DataContextHandler.Context.Dispose();
}

Where DataContextHandler is just a simple singleton that exposes the context object for my entities. This seems to work just as you would think. It creates the user, saves, then rolls back when the program ends. (IE test finishes)

Problem: How do I force the transaction to rollback and kill itself so that I can query the table?

Reason: For testing purposes, I want to make sure the user:

  • Is Saved
  • Can be queried correctly to prove its existence
  • Is removed (Junk data)
  • Can be queried to make sure it was removed.

As of right now, I can only get the transaction to rollback if the test ends AND I can't figure out how to query with the transaction up:

[TestMethod]
public void CreateUser()
{
    ForumUser userToTest = new ForumUser();

    TransactionScope transactionScope = new TransactionScope();

    DataContextHandler.Context.AddToForumUser(userToTest);
    DataContextHandler.Context.SaveChanges();     

    Assert.IsTrue(userToTest.UserID > 0);

    var foundUser = (from user in DataContextHandler.Context.ForumUser
                    where user.UserID == userToTest.UserID
                    select user).Count();  //KABOOM Can't query since the 
                                           //transaction has the table locked.

    Assert.IsTrue(foundUser == 1);

    DataContextHandler.Context.Dispos
entity-framework transactions
Edit
Report

1 Answer

7

In my case I delete all records from a Table through plain SQL since EF doesn't provide a functionality for this. After that I add some new entities - but when it fails, the table shouldn't be empty. The use of MSDTC (TransactionScope) seems not possible for me. I reduced the transaction to the DB:

My code:

using (var transaction = context.Connection.BeginTransaction())
{
      // delete all
      base.DeleteAll<TESTEntity>(context);

      // add all
      foreach (var item in items)
      {
           context.TESTEntity.AddObject(item);
      }

      try
      {
           context.SaveChanges();
           transaction.Commit();
           return true;
      }
      catch (Exception ex)
      {
           Logger.Write("Error in Save: " + ex, "Error");
           transaction.Rollback();
           return false;
      }
}

And here the helper functions

    protected void DeleteAll<TEntity>(ObjectContext context) where TEntity : class
    {
        string tableName = GetTableName<TEntity>(context);
        int rc = context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE FROM {0}", tableName));
    }

    protected string GetTableName<TEntity>(ObjectContext context) where TEntity : class
    {
        string snippet = "FROM [dbo].[";

        string sql = context.CreateObjectSet<TEntity>().ToTraceString();
        string sqlFirstPart = sql.Substring(sql.IndexOf(snippet) + snippet.Length);
        string tableName = sqlFirstPart.Substring(0, sqlFirstPart.IndexOf("]"));
        return tableName;
    }
answered 2010-12-17T11:40:16.943

Your Answer