Count() and CountAsync() not working when called after FromSqlRaw & FromSqlInterpolated

[aleksandar-smilevski]

We are calling a stored procedure with parameters (the set is a keyless entity type) and after that we are calling CountAsync to only get the number of results retrieved. However, it throws an exception.

Using SQL Server profiler I was able to see that it generates an invalid SQL query

Steps to reproduce

The query looks like this:

var resultCount = await _applicationDbContext.Set<SearchResultDto>().FromSqlRaw("GetSearchResults {0}", searchQuery).CountAsync();

The generated query looks like this

exec sp_executesql N'SELECT COUNT(*)
FROM (
    GetSearchResults @p0
) AS [s]',N'@p0 nvarchar(4000)',@p0=N'dotnet core'

This used to work in ASP.NET Core 2.1, so I am looking for instructions on how to refactor my existing code

Exception message: Incorrect syntax near '@p0'.
Stack Trace:
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__164_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
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.TaskAwaiter1.GetResult() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__17.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__17.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__17.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable1.AsyncEnumerator.d__17.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.ValueTaskAwaiter1.GetResult() at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleAsync>d__191.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.d__191.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.TaskAwaiter1.GetResult()

Further technical details

EF Core version: 3.0
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.0)
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.3)

[smitpatel]

See https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#fromsqlsproc