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

Question

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.Dispose();

    var after = (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(after == 0);
}

UPDATE This worked for rolling back and checking, but still can't query within the using section:

using(TransactionScope transactionScope = new TransactionScope())
{
    DataContextHandler.Context.AddToForumUser(userToTest);
    DataContextHandler.Context.SaveChanges();
    Assert.IsTrue(userToTest.UserID > 0);
    //Still can't query here.

}

var after = (from user in DataContextHandler.Context.ForumUser
            where user.UserID == userToTest.UserID
            select user).Count();

Assert.IsTrue(after == 0);

Answer 1

From MSDN;

"SaveChanges operates within a transaction. SaveChanges will roll back that transaction and throw an exception if any of the dirty ObjectStateEntry objects cannot be persisted. "

So it seems that there is no need to to explicitly add your own transaction handling through TransactionScope.

Answer 2

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;
    }

Answer 3

I have been able to solve very similar problem using this code fragment:

var connection = new EntityConnection("name=MyEntities");
connection.Open();
var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);

try
{
    var dataContext = new MyEntities(connection);

    //CRUD operation1

    //CRUD operation2

    //CRUD operation3 ...

    Assert.AreEqual(expected, actual);
}
catch
{
    throw;
}
finally
{
    tran.Rollback();
    connection.Close();
}

Where MyEntities is the EF DataModel. The crucial part is the setting of the transaction: System.Data.IsolationLevel.ReadUncommitted.
Using this isolation level SQL queries can read you changes done inside the transaction. Also you need to explicitly create a connection as I do on the first and second line. I couldn't make it work using TransactionScope unfortunately.