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

Question

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.

score 6 · Answer 1 · edited May 23 '17 at 12:25

6

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

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

edited May 23 '17 at 12:25

Community

1
1

answered Apr 08 '16 at 10:56

Atron Seige

2,783
4
32
39

score 5 · Answer 2 · answered Nov 07 '16 at 22:07

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

This answer addresses the title of the question but not the body of the question. Yes, the question’s title is misleading and results in this being a top Google hit for the question you answered, but this question isn’t that question x.x. Waaah — binki, Apr 15 '22 at 21:21

score 4 · Accepted Answer · answered Mar 02 '13 at 01:36

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

I don't completely understand why this problem is caused. Are you saying that creating 2 equivalent dbcontext instance causes the problem. meaning that only one dbcontext to a specific db can exist in the application ? — eran otzap, Mar 08 '16 at 09:28
Eran Otzap, I know it is a litle late, but having faced this issue today, I thought it would be helpfull to publish an answer explaining the root causes. Hope it helps. — Julio Nobre, Nov 07 '16 at 22:09

score 3 · Answer 4 · answered Mar 02 '13 at 09:46

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

anil soni · Answer 5 · 2018-08-14T07:44:41.257

-1

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

}

edited Aug 14 '18 at 07:44

answered Aug 14 '18 at 07:38

anil soni

75
1
4

Only creates an empty database. Then what? – Gert Arnold Mar 24 '21 at 14:40

score -1 · Answer 6 · answered Mar 24 '21 at 11:27

-1

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

answered Mar 24 '21 at 11:27

Wonde_Man

55
6

1

How would you do that in a unit test method? – Gert Arnold Mar 24 '21 at 11:38

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

6 Answers6

Linked