Runtime: 'Unexpected existing transaction' exception occurs when use SqlBulkCopy.

[xin9le]

Overview

'Unexpected existing transaction' exception occurs under following condition.

• Use .NET Core 2.1
• Use System.Data.SqlClient (4.5.1 : currently latest stable package)
• Perform bulk insert record(s) into 2 or more Azure SQL Database using SqlBulkCopy in TransactionScope

This exception doesn't occur under .NET Framework 4.7.1 environment.

Reproduce code

Reproduce code for .NET Core is following. No exception occurs under .NET Framework 4.7.1 using exactly same code.

• TransactionalBulkCopy.zip

```c#
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using System.Transactions;
using FastMember;

namespace NetCoreSample
{
class Program
{
static async Task Main()
{
using (var tx = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
var connectionString1 = "Connection string for a certain Azure SQL Database.";
await BulkCopy(connectionString1); // no problem

            var connectionString2 = "Connection string for another Azure SQL Database.";
            await BulkCopy(connectionString2); // exception occurs under .NET Core!!
        }
    }

    static async Task BulkCopy(string connectionString)
    {
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();  // enlist
            using (var bcp = new SqlBulkCopy(connection))
            {
                var data = new []
                {
                    new Person("xin9le", 33),
                    new Person("yuna", 9),
                };
                var accessor = TypeAccessor.Create(typeof(Person));
                var propertyNames = accessor.GetMembers().Select(x => x.Name).ToArray();
                var reader = ObjectReader.Create(data, propertyNames);

                bcp.DestinationTableName = nameof(Person);
                foreach (var x in propertyNames)
                    bcp.ColumnMappings.Add(x, x);
                await bcp.WriteToServerAsync(reader);  // exception occurs here!!
            }
        }
    }
}

class Person
{
    public string Name { get; }
    public int Age { get; }
    public Person(string name, int age)
    {
        this.Name = name;
        this.Age = age;
    }
}

}

# Posibble cause

The exception occurs at following line. I guess the cause is `_connection.HasLocalTransaction` returns `True`.

```c#
isInTransaction = _connection.HasLocalTransaction;
// Throw if there is a transaction but no flag is set
if (isInTransaction && null == _externalTransaction && null == _internalTransaction && (_connection.Parser != null && _connection.Parser.CurrentTransaction != null && _connection.Parser.CurrentTransaction.IsLocal))
{
    throw SQL.BulkLoadExistingTransaction();
}

• https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlBulkCopy.cs#L542

If chase further deeply under .NET Core, SqlInternalTransaction._transactionType field has TransactionType.LocalFromTSQL. Probably this is wrong. Under .NET Framework, SqlInternalTransaction._transactionType field has TransactionType.Distributed.

There is a difference implementation between .NET Core and .NET Framework like following. I guess this is one of the causes, but I have not actually tried it.

```c#
// .NET Core
TransactionType transactionType = TransactionType.LocalFromTSQL;
_currentTransaction = new SqlInternalTransaction(_connHandler, transactionType, null, env[ii].newLongValue);

// .NET Framework
TransactionType transactionType = (TdsEnums.ENV_BEGINTRAN == env[ii].type) ? TransactionType.LocalFromTSQL : TransactionType.Distributed;
_currentTransaction = new SqlInternalTransaction(_connHandler, transactionType, null, env[ii].newLongValue);
```

• https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs#L1870
• https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/TdsParser.cs,2092

[keeratsingh]

If chase further deeply under .NET Core, SqlInternalTransaction._transactionType field has TransactionType.LocalFromTSQL. Probably this is wrong. Under .NET Framework, SqlInternalTransaction._transactionType field has TransactionType.Distributed.

@xin9le Thank you for the detailed explanation and repro. Your observation was spot on and it was indeed this difference that cause the issue. I was able to successfully repro and issue, PR dotnet/corefx#31211 fixes the issue.

[keeratsingh]

Fixed by PR dotnet/corefx#31211, closing issue.

[xin9le]

@keeratsingh
Thanks you very very much for your quick fix!! 👍
I'm looking forward to use fixed version 😄

[keeratsingh]

Re-Opening for 2.2 servicing:

Description
Unexpected existing transaction exception occurs when using SqlBulkCopy in TransactionScope

Customer Impact
Customers will not be able to use SqlBulkCopy in TransactionScope.

Regression?
No. SqlBulkCopy code path was missed when System.Transactions was ported over from .NET Framework to .NET Core.

Risk
Low. The code change is minimal and has existed in .NET Framework since quite a while.

CC: @Petermarcu @David-Engel

[joshfree]

Updating milestone=2.1.x and adding servicing-consider. @keeratsingh please create a PR against /release/2.1 and add a link to the PR from this tracking issue when you're ready for shiproom to review.

Thanks

[danmosemsft]

@keeratsingh please link a release/2.1 PR and then I can mark it servicing-consider to go to shiproom.

[keeratsingh]

@danmosemsft , I am awaiting dotnet/corefx#31039 to be merged to 2.1/release as part of my changes depend upon files added in the PR above. I will create a release/2.1 PR once dotnet/corefx#31039 is merged

[danmosemsft]

@keeratsingh the branch may not open for 2.1.5 for 2+ weeks. We will need a PR to go to shiproom sooner. What I suggest is that you create a PR that is based on the branch for the PR dotnet/corefx#31039. So it would have two commits, master->#31039 and dotnet/corefx#31039->this fix. Then to review your change, we can diff your last two commits. When ready to merge, it should merge second cleanly, or we can modify the PR then.

[keeratsingh]

@danmosemsft PR https://github.com/dotnet/corefx/pull/31801 created for 2.1 Servicing review without dotnet/corefx#31039 dependencies

[keeratsingh]

Closing issue, as PR dotnet/corefx#31801 has not been considered for 2.1 servicing.

[karelz]

Just to clarify:

• The bug was fixed in master branch in PR dotnet/corefx#31211, it will be part of 3.0 release (updating milestone to 3.0 to reflect that).
• The fix was rejected for 2.1 servicing by shiproom in PR dotnet/corefx#31801.