CloseAfterUse does not appear to work with stored procedures

[trimeloni]

Describe your issue

According to the Documentation, to ensure the DataConnection is always associated with TransactionScope: "Although, DataContext appears to be the right class to choose, it is strongly recommended to use DataConnection instead. It's default behaviour might be changed with setting CloseAfterUse property to true:"

This appears to only partially work. If this advice is followed, but a stored procedure is executed prior to starting the TransactionScope, none of the subsequent queries will be part of the transaction scope until a the query after a non-stored procedure query is run.

Steps to reproduce

I wasn't sure if I should take code, create a branch directly in this solution, push it and create a PR or if I would need to fork the solution to create a PR - but I do have sample code that can be added as a Test in the Playground and run against SqlServer.2017.

PLEASE NOTE THAT THIS WILL LEAVE THE PERSON DATA IN A DIFFERENT STATE SINCE THE TRANSACTION DOES NOT WORK

	[TestFixture]
	public class SqlServerCloseAfterUseSamples : TestBase
	{

		[Test]
		public async Task CloseAfterUse_StoredProcedure_DbShouldNotChange([IncludeDataSources("SqlServer.2017")] string context)
		{
                        // THIS ONE DOES NOT WORK

			using (var db = GetDataConnection(context))
			{
				(db as IDataContext).CloseAfterUse = true;

				// Issue Description:  With Close after Use - this query should still "reset/close" the connection
				var people  = (await db.QueryProcAsync<Person>("Person_SelectAll")).ToList();

				using (var scope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
				{
					// Issue Description:  Even though this is created after an earlier query is run - since CloseAfterUse
					//                     is turned on - this should pick up and associated to the scope

					// Existing update SP needs all fields - but really only need FirstName to show issue
					var dp1 = new[]
					{
						new DataParameter("@PersonID", 1),
						new DataParameter("@FirstName", "1"),
						new DataParameter("@LastName", "1"),
						new DataParameter("@MiddleName", "1"),
						new DataParameter("@Gender", "M")
					};
					await db.ExecuteProcAsync("[Person_Update]", dp1);

					// Do an update via Linq to SQL
					await db.Person
						.Where(p => p.ID == 2)
						.Set(c => c.FirstName, "2")
						.Set(c => c.LastName, "2")
						.UpdateAsync();


					var dp3 = new[]
					{
						new DataParameter("@PersonID", 3),
						new DataParameter("@FirstName", "3"),
						new DataParameter("@LastName", "3"),
						new DataParameter("@MiddleName", "3"),
						new DataParameter("@Gender", "M")
					};
					await db.ExecuteProcAsync("[Person_Update]", dp3);

					// DO NOT COMPLETE THE TRANSACTION
				}

				// Issue Description:  At this point, all the names should be rolled back to their original value
				//                     is turned in - this should allow the scope to be picked up and associated

				var person1 = await db.Person.SingleOrDefaultAsync(p => p.ID == 1);
				var person2 = await db.Person.SingleOrDefaultAsync(p => p.ID == 2);
				var person3 = await db.Person.SingleOrDefaultAsync(p => p.ID == 3);


				// Based on the fact that 1 and 2 are wrong - it appears that:
				//     StoreProcedure (does not reset?)
                                //     TransactionScope
                                //      Stored Procedure update (does not associate, does not reset connection?)
                                //      Linq Query Update (does not associate, does reset connection)
                                //      Stored Procedure update (does associate)
				Assert.AreEqual(people.Single(q => q.ID == 1).FirstName, person1?.FirstName );	// WRONG
				Assert.AreEqual(people.Single(q => q.ID == 2).FirstName, person2?.FirstName);	// WRONG
				Assert.AreEqual(people.Single(q => q.ID == 3).FirstName, person3?.FirstName );	// CORRECT

			}

		}


		[Test]
		public async Task CloseAfterUse_Linq_DbShouldNotChange([IncludeDataSources("SqlServer.2017")] string context)
		{
                        // THIS ONE WORKS

			using (var db = GetDataConnection(context))
			{
				(db as IDataContext).CloseAfterUse = true;

				// With Close after Use - this query should resets/closees the DB
				var people  = await db.Person.Select(p => p).ToListAsync();

				using (var scope = new TransactionScope(asyncFlowOption: TransactionScopeAsyncFlowOption.Enabled))
				{
					// Issue Description:  Even though this is created after an earlier query is run - since CloseAfterUse
					//                     is turned on - this should pick up and associate the scope

					// Existing update SP needs all fields - but really only need FirstName to show issue
					var dp1 = new[]
					{
						new DataParameter("@PersonID", 1),
						new DataParameter("@FirstName", "FUpdate"),
						new DataParameter("@LastName", "X"),
						new DataParameter("@MiddleName", "X"),
						new DataParameter("@Gender", "M")
					};
					await db.ExecuteProcAsync("[Person_Update]", dp1);

					// Do an update via Linq to SQL
					await db.Person
						.Where(p => p.ID == 2)
						.Set(c => c.FirstName, "FUpdate")
						.UpdateAsync();


					var dp3 = new[]
					{
						new DataParameter("@PersonID", 3),
						new DataParameter("@FirstName", "FUpdate"),
						new DataParameter("@LastName", "Y"),
						new DataParameter("@MiddleName", "Y"),
						new DataParameter("@Gender", "M")
					};
					await db.ExecuteProcAsync("[Person_Update]", dp3);

					// DO NOT COMPLETE THE TRANSACTION
				}

                                // Greab the people again - they should not have changed at all
				var person1 = await db.Person.SingleOrDefaultAsync(p => p.ID == 1);
				var person2 = await db.Person.SingleOrDefaultAsync(p => p.ID == 2);
				var person3 = await db.Person.SingleOrDefaultAsync(p => p.ID == 3);

				// Compare against the data prior to the canceled transaction
				Assert.AreEqual(people.Single(q => q.ID == 1).FirstName, person1?.FirstName);
				Assert.AreEqual(people.Single(q => q.ID == 2).FirstName, person2?.FirstName);
				Assert.AreEqual(people.Single(q => q.ID == 3).FirstName, person3?.FirstName);

			}

		}

	}

Environment details

Linq To DB version: Ran against the latest Release branch ( 2024-02-20 )

Database (with version): SQL Server 2017 (maybe others)

ADO.NET Provider (with version): Whatever the default is for release branch Test Playground

Operating system: Run on Windows, Run within Ubuntu docker containers

.NET Version: 6,7 (maybe others)