Downside of using transaction dispose with entity framework integration testing

Question

I am looking for a quick way to clean my tables data doing integration tests with EF.

Everyone seems to wrap a transaction around its test method and dispose the transaction after the test.

That way the data is never written to the table.

Things like new auto ids for inserts are still working but I ask myself wether this method is really reliable compared to .commit() the transaction.

Are there any downsides of using this approach which seem not to be a real integration test as the database is never touched...

Or asked in other words are there faulty scenarios which do not pop as exceptions using the transaction without commit() ?

UPDATE

public abstract class IntegrationTestsBase
    {
        protected TransactionScope TransactionScope;

        public abstract void TestSetup();
        protected void InitTestSetupOnTable(string tableName)
        {
            TransactionScope = new TransactionScope();

            using (var context = new TGBContext())
            {
                var cmdCommand = string.Format("DBCC CHECKIDENT ({0}, RESEED, 1)", tableName);
                context.Database.ExecuteSqlCommand(cmdCommand);
                context.SaveChanges();
            }
        }

        [TestCleanup]
        public void TestCleanup()
        {
            TransactionScope.Dispose();
        }
    }

[TestClass]
public class MyTests : IntegrationTestsBase
{
        [TestInitialize]
        public override void TestSetup()
        {
            base.InitTestSetupOnTable("MyTableName");          
        }
}

Answer 1

as the database is never touched

Surely it's touched. Everything that happens within the transaction happens in the database. Identity values and sequences (if any) are incremented, triggers fire, referential constraints are checked, etc. etc. The only thing is, it happens in isolation and in the end everything (except incremented identities and sequences) is reverted.

Are there any downsides of using this approach?

Not really. I use this approach extensively in my own code and it has great merits. Green tests give a very high level of assurance. I will never feel safe with "real" unit test using mocked contexts and DbSet's (although I use unit test for many other things).

But there are some restrictions you should be aware of.

• Identity / sequence values are not deterministic, so you can't assert them. As I said, these values will not be rolled back. If you really need asserts in this area, you can reset identities/sequences after each test.
• We can't ever test concurrency issues by this approach.
• You can't open a connection to another database, or even to the same database if its connection string differs in the slightest detail (e.g. a different application name, or different MARS setting), without DTC kicking in. In the application code this would be possible because there these different calls wouldn't be wrapped in a TransactionScope. So maybe not all application paths can easily be tested this way.
• Developers should use their own copy of the test database. If multiple developers (and maybe a build server) run tests on the same database they may deadlock one another.