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

This is complex and dependant on which database (SQL Server/Oracle/etc.) AND which data access technology you are using (ADO.Net/OleDb/ODBC/etc.) ??

Answer 2

From : http://msdn.microsoft.com/en-us/library/bb336792.aspx

"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.