title: Does Npgsql provider has support for TransactionScope?
body: <p>I'm trying to use a TransactionScope with the Npgsql provider.
  I found in an old question (<a href="https://stackoverflow.com/questions/3819569/provider-for-postgresql-in-net-with-support-for-transactionscope">provider for PostgreSQL in .net with support for TransactionScope</a>) that Npgsql didn't supported it yet.
  Now, after about 5 years, does Npgsql support TransactionScope?
  I made a test for myself, using Npgsql 3.0.3 and using the following code:</p>
  
  <pre><code>using (var scope = new TransactionScope())
  {
      using(var connection = new Npgsql.NpgsqlConnection("server=localhost;user id=*****;password=*****database=test;CommandTimeout=0"))
      {
          connection.Open();
  
          var command = new NpgsqlCommand(@"insert into test.table1 values ('{10,20,30}', 2);");
          command.Connection = connection;
          var result = command.ExecuteNonQuery();
  
          // scope.Complete();  &lt;-- Not committed
      }
  }
  </code></pre>
  
  <p>Anyone can confirm that Npgsql doesn't support TransactionScope?</p>
  
  <p><strong>EDIT #1</strong>
  After the confirmation of the support of Npgsql to the TransactionScope, I found that you need to be sure to haev Distribuited Transaction enabled in your PostgreSQL configuration, checking the <em>max_prepared_transactions</em> parameter in the <em>postgres.conf</em> file (remember to restart your server).</p>
  
  <p><strong>EDIT #2</strong>
  I enabled the Distribuited Transaction on my server but now I got an error using the TransactionScope with Npgsql.
  This is my code:</p>
  
  <pre><code>using (var sourceDbConnection = new NpgsqlConnection(SourceConnectionString))
  using (var destinationDbConnection = new NpgsqlConnection(DestinationConnectionString))
  using (var scope = new TransactionScope())
      {
          sourceDbConnection.Open();
          destinationDbConnection.Open();
  
          Logger.Info("Moving data for the {0} table.", TableName.ToUpper());
          var innerStopWatch = new Stopwatch();
          innerStopWatch.Start();
  
          foreach (var entity in entities)
          {
              var etlEntity = new EtlInfoItem
              {
                  MigratedEntityId = category.RowId,
                  ProjectId = category.ProjectId,
                  ExecutionDatetime = DateTime.Now
              };
  
              // Insert into the destination database
              var isRowMigrated = InsertEntity(entity, DestinationSchema, destinationDbConnection);
  
              if (isRowMigrated)
              {
                  // Update the ETL migration table
                  InsertCompletedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
              }
              else
              {
                  // Update the ETL migration table
                  InsertFailedEtlMigrationEntity(etlEntity, EtlSchema, sourceDbConnection);
              }
          }
  
          Logger.Info("Data moved in {0} sec.", innerStopWatch.Elapsed);
  
          Logger.Info("Committing transaction to the source database");
                  innerStopWatch.Restart();
  
          scope.Complete();
  
          innerStopWatch.Stop();
          Logger.Info("Transaction committed in {0} sec.", innerStopWatch.Elapsed);
      }
  </code></pre>
  
  <p>When the TransactionScope exits from the scope (when exiting the using statement), I get a Null Reference Exception with the following stack trace:
  Server stack trace:
  at Npgsql.NpgsqlConnector.Cleanup()
  at Npgsql.NpgsqlConnector.Break()
  at Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
  at Npgsql.NpgsqlConnector.ReadExpectingT
  .........
  It happens randomly.</p>