SqlConnection.GetSchema fails when SqlConnection.Transaction is not null

[arontsang]

Describe the bug

SqlConnection.GetSchema fails when SqlConnection.Transaction is not null

System.InvalidOperationException: Unable to build the 'Columns' collection because execution of the SQL query failed. See the inner exception for details. ---> System.InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at Microsoft.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
   at Microsoft.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String collectionName, String[] restrictions)
   at Microsoft.Data.SqlClient.SqlConnection.GetSchema(String collectionName, String[] restrictionValues)

To reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

	using var connection = new SqlConnection("connectionString");
	connection.Open();
	using var transaction = connection.BeginTransaction();
	var foo = connection.GetSchema("Columns", new[] {"database", "schema", "[table]", null});

Expected behavior

No exception

Further technical details

Microsoft.Data.SqlClient version: 5.1.1
.NET target: Net 5
SQL Server version: N/A
Operating system: Windows 10

[arontsang]

Clearly I can see two solutions:

First. Set the DbCommand.Transaction to connection.Transaction in DbMetaDataFactory.ExecuteCommand().

Secondly. SqlConnection.CreateDbCommand() should initialize the command.Transaction to this.Transaction.

It seems to me that the second option makes more sense (but is a breaking change).

[David-Engel]

Clearly I can see two solutions:

First. Set the DbCommand.Transaction to connection.Transaction in DbMetaDataFactory.ExecuteCommand().

Secondly. SqlConnection.CreateDbCommand() should initialize the command.Transaction to this.Transaction.

It seems to me that the second option makes more sense (but is a breaking change).

We aren't going to make a breaking change, so the appropriate fix would be the first one.

Thanks,
David