SQLServer. Unable to make ALLOW_SNAPSHOT_ISOLATION work in c# code (it works on Management Studio)

Question

Recently I had to resolve lock problems such as Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

After reading several articles and analyzing on the context of my system I ended up accepting the most usual solution: ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE MyDb SET ALLOW_SNAPSHOT_ISOLATION ON;

I want the ALLOW_SNAPSHOT_ISOLATION because that isolation makes sense on my system.

I successfully implemented the flow described in the section "Allow Snapshot Isolation" from https://www.databasejournal.com/features/mssql/article.php/3566746/Controlling-Transactions-and-Locks-Part-5-SQL-2005-Snapshots.htm on two sessions in SQL Server Management Studio.

Pseudo code:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN 1
select value from MyTable where ID=1 --reads original value 

  --On another session:
  BEGIN TRAN 2
  update mytable set value=2 where ID=1
  COMMIT TRAN2

-- back to session 1
select value from MyTable where ID=1 --still reads original value 

The above example works as expected. This tells me that the database is configured correctly.

My problem is on the C# code. While I was able to prevent the lock situations (READ_COMMITTED_SNAPSHOT is working) I was unable to replicate the "Allow Snapshot Isolation" behavior on my c# code. I tried with TransactionScope and without it. My goal is to have it working with TransactionScope.

My test on C# is to start a long transaction: Read value from my table, wait for 20 seconds, read the value again and print both values. When the code is sleeping for the 20 seconds I go to SQL Server Management Studio and update the value to the new value. After 20 seconds it is shown the original value and the new value. I was expecting both original values because of ALLOW_SNAPSHOT_ISOLATION and SET TRANSACTION ISOLATION LEVEL SNAPSHOT

With transaction scope (I am using Dapper):

static TransactionScope CreateTransactionScope()
{
    var transactionOptions = new TransactionOptions();
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    transactionOptions.IsolationLevel = IsolationLevel.Snapshot; //also tried IsolationLevel.ReadCommitted
    return new TransactionScope(TransactionScopeOption.RequiresNew, transactionOptions);
}
...
using (var transactionScope = CreateTransactionScope())
{
    T ret;
    using (var connection = new SqlConnection(_connectionString))
    {
        //connection.Execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT"); this makes no difference
        ret = TestWithTransactionScope(connection);
    }
    transactionScope.Complete();
    return ret;
}
...
public object TestWithTransactionScope(IDbConnection c)
{
    var sql = "select value from MyTable where ID=1";
    var firstRead = c.Query<string>(sql).Single();
    System.Threading.Thread.Sleep(25000);
    var secondRead = c.Query<string>(sql).Single();
    return string.Format("firstRead: {0}, secondRead: {1}", firstRead, secondRead);
}

Without TransactionScope:

...
using (var connection = new SqlConnection("..."))
{
    connection.Open();
    connection.Execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT");
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var ret = TestWithTransactionScope(connection, transaction);
            transaction.Commit();
            return ret;
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}
...

public object TestWithTransactionScope(IDbConnection c, SqlTransaction t)
{
    var sql = "select value from MyTable where ID=1";
    var firstRead = c.Query<string>(q, null, t).Single();
    System.Threading.Thread.Sleep(25000);
    var secondRead = c.Query<string>(q, null, t).Single();
    return string.Format("firstRead: {0}, secondRead: {1}", firstRead, secondRead);
}

Any ideas?

I am using .Net 4.5, Dapper 1.50.2 and SQL Server 2014

UPDATE 1

I was able to use the Snapshot isolation on the non TransactionScope version:

using (var transaction = connection.BeginTransaction(IsolationLevel.Snapshot))

But I still need it to work on the TransactionScope version.