'EntityFramework in test initialization error: CREATE DATABASE statement not allowed within multi-statement transaction

I'm trying to build a quick test that deletes and recreates a database every time it runs. I have the following:

[TestClass]
public class PocoTest
{
    private TransactionScope _transactionScope;
    private ProjectDataSource _dataSource; 
    private Repository _repository = new Repository();
    private const string _cstring = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";

    [TestInitialize]
    public virtual void TestInitialize()
    {
        _dataSource = new ProjectDataSource(_cstring);
        _dataSource.Database.Delete();
        _dataSource.Database.CreateIfNotExists();
        _transactionScope = new TransactionScope();
    }
    [TestMethod]
    public void TestBasicOperations()
    {                
        var item = _repository.AddItem(new Item(){Details = "Test Item"});
        //  AddItem makes a call through the data context to add a set and then calls datacontext.SaveChanges()
    }


    [TestCleanup]
    public void TestCleanup()
    {
        // rollback
        if (_transactionScope != null)
        {
            _transactionScope.Dispose();
        }
    }

However when I run the test I get the following error:

Result Message: Test method Project.Repository.UnitTests.PocoTest.TestBasicOperations threw exception: System.Data.SqlClient.SqlException: CREATE DATABASE statement not allowed within multi-statement transaction.

ProjectDataSource is here: 

public class ProjectDataSource : DbContext, IProjectDataSource
{

    public ProjectDataSource() : base("DefaultConnection")
    {

    }

    public ProjectDataSource(string connectionString) : base(connectionString)
    {

    }

    public DbSet<Set> Sets { get; set; }
}

Repository: 

public class Repository : IRepository
{
    private readonly ProjectDataSource _db = new ProjectDataSource();
    public Item AddItem(Item item)
        {
            _db.Items.Add(item);
            _db.SaveChanges();
            return item;
        }
}

Why is this happening?

Also - if it makes any difference - the error doesn't occur if I comment out the AddItem line in TestMethod.

c++sharpentity-framework


Solution 1:[1]

In case anyone else runs into this issue:

In my Repository class, I have another definition of what's commonly labeled a "dbContext" - ProjectDataSource. This means that one context was created in my test class, while another was created in my Repository object. Sending the connectionstring to my repo class solved the problem:

In Repository: 

public class Repository : IRepository
    {
        private readonly ProjectDataSource _db;

        public Repository(string connectionString)
        {
            _db = new ProjectDataSource(connectionString);   
        }

        public Repository()
        {
            _db = new ProjectDataSource();   
        }

From my test: 

private TransactionScope _transactionScope;
        private Repository _repository;
        private ProjectDataSource _dataSource; 
        private const string _connectionString = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";

        [TestInitialize]
        public virtual void TestInitialize()
        {
            _repository = new Repository(_connectionString);
            _dataSource = new ProjectDataSource(_connectionString);
            _dataSource.Database.Delete();
            _dataSource.Database.CreateIfNotExists();
            _transactionScope = new TransactionScope();
        }

Solution 2:[2]

You can also use db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sqlCommand);

See https://stackoverflow.com/a/24344654/375114 for details

Solution 3:[3]

For your information, this error occurs by design and it happens whenever non-transactionable commands are issued to Microsoft SQL Server within an active transaction.

The solution is, therefore, granting that Database.CreateIfNotExists() hits the database out of any transaction scope. Remember, SQL Profiler is your friend.

You can get a roughly updated list of commands that are not allowed to run whithin transactions.

Note: In case one wonders why am I providing a list based on a Sybase's product, bear in mind that Microsoft SQL Server shares most of its basic genetic with Sybase' engine. For further reading, refer to https://en.wikipedia.org/wiki/Microsoft_SQL_Server

Solution 4:[4]

You can not use implicit commits around certain SQL commands. Creating and Deleting databases is an example SQL server will do an AUTOCommit

See the remarks section in the MS SQL help. http://msdn.microsoft.com/en-us/library/ms176061.aspx

and something on Auto Commit for more info... http://msdn.microsoft.com/en-us/library/ms187878%28v=sql.105%29

Solution 5:[5]

Try this code

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress))

{
var sqlCommand = String.Format("Create DATABASE [{0}]", "TempBackupDB"); _context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sqlCommand);

ts.Complete();

}

Solution 6:[6]

You need to run Update-Database command on package manager console.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RobVious
Solution 2 Community
Solution 3 Julio Nobre
Solution 4 phil soady
Solution 5
Solution 6 Wonde_Man

Related Questions