The connection object can not be enlisted in transaction scope

Question

I am using TransactionScope to test database actions. This is the test class:

// Test class

private TransactionScope _transactionScope = null;

[TestInitialize]
public void Initialize()
{
    _transactionScope = new TransactionScope();
}

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

[TestMethod]
[DeploymentItem("Db.sdf")]
public void AddToPresentationsTest()
{           
    var item = TestItem();
    var db = new DbEntities();
    var target = new DatabaseController {Entities = db};
    target.AddToItems(item);
    var result = db.Items.Any(p => p.Text.Equals(item.Text));
    Assert.IsTrue(result);
}

A TransactionScope is created before each test and is disposed after the test is complete. When AddToItems method is called I get the following error:

System.Data.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: The connection object can not be enlisted in transaction scope.

DatabaseController has the following code:

// DatabaseController class

private DbEntities _entities;

public DbEntities Entities
{
    get { return _entities ?? (_entities = new DbEntities());}
    set { _entities = value; }
}

protected override void Dispose(bool disposing)
{
    if (disposing && _entities != null)
    {
        _entities.Dispose();
    }
    base.Dispose(disposing);
}

public void AddToItems(Item item)
{
    Entities.Items.Add(item);
    Entities.SaveChanges();
}

I use Sql Server Compact 4.0. Could you please point out what am I doing wrong?

http://stackoverflow.com/questions/5153573/error-on-using-transactionscope-in-ef4-sql-compact-4 — Jayantha Lal Sirisena, Jul 19 '12 at 05:25

score 3 · Accepted Answer · edited May 23 '17 at 12:31

3

At a guess, TransactionScope needs to escalate to a distributed or nested transaction, neither of which is supported by CE.

This may be happening e.g. because more than one connection is being opened concurrently - TransactionScope and SQL Server Compact

CE does however support lightweight transactions, so in theory as long as all your connections use the same connection string, and that you close each connection before opening another, TransactionScope shouldn't escalate to distributed.

edited May 23 '17 at 12:31

Community

1
1

answered Jul 19 '12 at 05:25

StuartLC

104,537
17
209
285

Thank you, I wasn't aware that SQL CE has such limitations. – Ashton H. Jul 19 '12 at 05:32
@Ashton Hearts - I'm afraid I don't use CE much - there are some workarounds here http://stackoverflow.com/questions/6690475/transactionscope-and-sql-server-compact – StuartLC Jul 19 '12 at 05:43
Well, all the connections do actually use the same connection string. I also noticed that I get the error when calling a methods inside `DatabaseController`. Otherwise transaction scope works fine. – Ashton H. Jul 19 '12 at 05:49

The connection object can not be enlisted in transaction scope

1 Answers1

Linked