[SQL server] DeleteWithOutputIntoAsync does generates incorrect SQL for temp tables

[MihailsKuzmins]

Describe your issue.
DeleteWithOutputIntoAsync does not correctly handle temp tables for SQL server. It generates SQL where a temp table is xconsidered to be a normal table. Please see details below.

Exception message: Invalid object name 'tblMyTable'.
Stack trace:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnection.<ExecuteNonQueryAsync>d__9.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnection.<ExecuteNonQueryDataAsync>d__10.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at LinqToDB.Data.DataConnection.<ExecuteNonQueryDataAsync>d__10.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnection.QueryRunner.<ExecuteNonQueryAsync>d__43.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Linq.QueryRunner.<NonQueryQueryAsync>d__33.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at LinqToDB.Linq.QueryRunner.<NonQueryQueryAsync>d__33.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Linq.ExpressionQuery`1.<LinqToDB-Async-IQueryProviderAsync-ExecuteAsync>d__15`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Program.<<Main>$>d__0.MoveNext() in C:\tfs_mku\__github\tests\Linq2DbIncorrectSql\Program.cs:line 17
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Program.<<Main>$>d__0.MoveNext() in C:\tfs_mku\__github\tests\Linq2DbIncorrectSql\Program.cs:line 17
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Program.<<Main>$>d__0.MoveNext() in C:\tfs_mku\__github\tests\Linq2DbIncorrectSql\Program.cs:line 17

Steps to reproduce

I have created a sample console app here: https://github.com/MihailsKuzmins/Linq2DbIncorrectSql
Please check out the solution and just run. The only requirement is to have an empty database. No need to create any tables.

Generated SQL.
The error is here INTO [tmpTblName]. The problem is that [tmpTblName] is not prefixed with the temp table prefix #, therefore, the server thinks that it is a normal table and of course it does not exist - since it is a temp table

CREATE TABLE [tempdb]..[#tmpTblName]
(
	[colPK1] Int     NOT NULL,
	[colPK2] TinyInt NOT NULL,
	[number] Int     NOT NULL,

	PRIMARY KEY CLUSTERED ([colPK1], [colPK2])
)

--  SqlServer.2019 (asynchronously)

DELETE [x]
OUTPUT
	[DELETED].[colPK1],
	[DELETED].[colPK2],
	[DELETED].[number]
INTO [tmpTblName]
(
	[colPK1],
	[colPK2],
	[number]
)
FROM
	[tblMyTable] [x]
WHERE
	[x].[colPK1] = 1

// Database
internal sealed class Database : DataConnection
{
	public Database(string connectionString)
		: base("Microsoft.Data.SqlClient", connectionString)
	{
	}

	public ITable<DatabaseEntryRecord> Entries => this.GetTable<DatabaseEntryRecord>();
}

// Dummy entry
[Table("tblMyTable")]
public sealed record DatabaseEntryRecord
{
	[Column("colPK1", IsPrimaryKey = true, PrimaryKeyOrder = 1)]
	public int Id { get; init; }

	[Column("colPK2", IsPrimaryKey = true, PrimaryKeyOrder = 2)]
	public byte AnotherId { get; init; }

	[Column("number")]
	public int Number { get; init; }
}

// Reproducing
LinqToDB.Data.DataConnection.TurnTraceSwitchOn();
LinqToDB.Data.DataConnection.WriteTraceLine = static (s1, s2, _) =>
{
	System.Diagnostics.Debug.WriteLine(s1, s2);
};

const string sqlServer = @"", database = "";
const string connectionString = @$"Data Source={sqlServer};Initial Catalog={database};Integrated Security=True;Trust Server Certificate=True";

await using var connection = new Database(connectionString);

await using var tmpTable = await connection.CreateTempTableAsync<DatabaseEntryRecord>("tmpTblName")
	.ConfigureAwait(false);

await connection.Entries
	.Where(x => x.Id == 1)
	.DeleteWithOutputIntoAsync(tmpTable)
	.ConfigureAwait(false);

Environment details

linq2db version: 4.1.0
Database Server: SQL Server
Database Provider: Microsoft.Data.SqlClient
Operating system: Windows 10
.NET Framework: net6