[LINQ-to-SQL] Does SQL Server Compact (CE) support a RequiresNew transaction scope inside another one?

Question

Here's a very simple example using SQL Server CE 3.5 SP1 where I attempt to use a new transaction inside an existing one.

using (var db = new MyDataContext(<connection string>))
using (var ts = new TransactionScope())
{
    db.Thing.InsertOnSubmit(new Thing());
    db.SubmitChanges();
    using (var ts2 = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        db.Thing.InsertOnSubmit(new Thing());
        db.SubmitChanges();   // exception here
        ts2.Complete();
    }
    [... do more stuff ...]
    ts.Complete();
}

This causes the error "The connection object can not be enlisted in transaction scope." on the second call to "db.SubmitChanges()"

This article states that SQL CE does not support two connections inside one transaction, but I only have one here (unless SubmitChanges() creates another one).

The idea above is that if the sub-transaction commits then it stays committed even if the outer transaction fails. To the best of my understanding, this is the intended use of RequiresNew.

This MSDN article claims that SQL CE supports nested transactions as such, hence my thinking that the above should be possible. Is it? If so, how do I modify the code to make this work?

Edit: While this MSDN article contradicts the other MSDN article and says nested transactions are not supported. Perhaps that is the problem?

Answer 1

Nope, you cannot use nested transactions. Run this code on your SQL CE database and you will see the error message.

BEGIN TRANSACTION;
SELECT 'GOT 1';
BEGIN TRANSACTION;
SELECT 'GOT 2';

Major Error 0x80004005, Minor Error 27994 Microsoft SQL Server Compact does not support nested transactions.

Answer 2

In your case, you should not use TransactionScope.

You will have to call DataContext.Transaction = DataContext.Connection.BeginTransaction and control your own transaction.