EF Core version 2.2
We have an extension method ContainsIds with raw SQL and Table-Valued Parameter (implementation below), which we are using to filter only records with specific identifiers.
However we noticed that sometimes query execution fails with the error
System.Data.SqlClient.SqlException: Must declare the table variable "@Id_4d9da9993ecd4917a9a58aa9b06628c9".
Test case which can reproduce the issue (we are running test cases against real SQL Server database)
[Fact]
public async Task Can_execute_multiple_queries()
{
await InsertTestOrdersWithIds(1, 2, 3, 4);
var results = await Task.WhenAll(
LoadOrders("First", 1, 2),
LoadOrders("Second", 3, 4)
);
foreach (var result in results)
{
result.Should().HaveCount(2);
}
}
private async Task<Order[]> LoadOrders(string name, params int[] ids)
{
_output.WriteLine($"LOAD ORDERS {name}");
using var context = contextFactory.Create();
return await context.DeliveryProducts.ContainsIds(ids).ToArrayAsync();
}
Test fails with the error already mentioned above. Logs shows that generated SQL has different names for SQL parameter in the SQL text and parameter passed to the query execution.
Notice different parameter names in "LOAD ORDERS 2"
Parameters=[@Id_3b31368fcc3b4afba6e08c153b2c92ed='?'
vs
SELECT * FROM @Id_c99c5200c7454d4fbb8f5e31ceaf5546
When we compare first and second generated SQL command queries, we can see that second execution using query string from previous execution.
Because we passing same string query to the .FromSql method
"SELECT * FROM [Order] o WHERE EXISTS (SELECT * FROM {0} i WHERE i.Id = o.Id"
I can assume that EF Core somehow cache this query string and use string before "injecting" parameter as a key, but string after "injecting" as cached value.
Notice that every query execution use different instances of DbContext.
However when we execute query once - it works.
Also we found that interpolating parameter name into the command text will work and will use correct parameter and parameter name. See this question for working implmentation: How to use Table-Valued parameter with EF Core
Question:
What happens under the hood in EF Core which uses command text from previous execution?
Logs:
--- LOAD ORDERS First ---
[08:52:07.1462 Information]
Microsoft.EntityFrameworkCore.Infrastructure
Entity Framework Core 2.2.4-servicing-10062 initialized 'MyDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: CommandTimeout=60
[08:52:07.3641 Information] Microsoft.EntityFrameworkCore.Database.Command
Executed DbCommand (41ms) [Parameters=[@Id_c99c5200c7454d4fbb8f5e31ceaf5546='?' (DbType = Object)], CommandType='Text', CommandTimeout='30']
SELECT * FROM [dbo].[Order] o WHERE EXISTS (SELECT * FROM @Id_c99c5200c7454d4fbb8f5e31ceaf5546 i WHERE i.Id = o.Id)
--- LOAD ORDERS Second ---
[08:52:07.3653 Information] Microsoft.EntityFrameworkCore.Infrastructure
Entity Framework Core 2.2.4-servicing-10062 initialized 'MyDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: CommandTimeout=30
[08:52:07.3763 Error] Microsoft.EntityFrameworkCore.Database.Command
Failed executing DbCommand (8ms) [Parameters=[@Id_3b31368fcc3b4afba6e08c153b2c92ed='?' (DbType = Object)], CommandType='Text', CommandTimeout='60']
SELECT * FROM [dbo].[Order] o WHERE EXISTS (SELECT * FROM @Id_c99c5200c7454d4fbb8f5e31ceaf5546 i WHERE i.Id = o.Id)
[08:52:07.3827 Error] Microsoft.EntityFrameworkCore.Query
An exception occurred while iterating over the results of a query for context type 'MyProject.MyDbContext'.
System.Data.SqlClient.SqlException (0x80131904): Must declare the table variable "@Id_c99c5200c7454d4fbb8f5e31ceaf5546".
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__277_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106
at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
Extension method implementation:
public static IQueryable<Order> ContainsIds(this IQueryable<Order> source, params int[] ids)
{
var parameter = ids.ToSqlParameter();
return source.FromSql(
"SELECT * FROM [Order] o WHERE EXISTS (SELECT * FROM {0} i WHERE i.Id = o.Id",
parameter
);
}
public static DataTable ToDataTable(this int[] ids)
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
foreach (var id in ids)
{
var row = table.NewRow();
row.SetField("Id", id);
table.Rows.Add(row);
}
return table;
}
public static SqlParameter ToSqlParameter(this int[] ids)
{
return new SqlParameter
{
ParameterName = $"Id_{Guid.NewGuid():N}", // generate unique parameter name
SqlDbType = SqlDbType.Structured,
TypeName = "dbo.MyIdType",
Value = ids.ToDataTable()
};
}
