Regression in 6.0.2: The variable name '@p0' has already been declared.

[PawelGerr]

Similar as in https://github.com/dotnet/efcore/issues/26754 and https://github.com/dotnet/efcore/issues/26632 but during reading data.

The issue seem to affect Microsoft.EntityFrameworkCore.SqlServer v6.0.2 only. No issues with Microsoft.EntityFrameworkCore.SqlServer v6.0.1 and Microsoft.EntityFrameworkCore.Sqlite v6.0.2.

Repro

csproj file

<Project Sdk="Microsoft.NET.Sdk">

   <PropertyGroup>
      <OutputType>Exe</OutputType>
      <TargetFramework>net6.0</TargetFramework>
      <ImplicitUsings>enable</ImplicitUsings>
      <Nullable>enable</Nullable>
   </PropertyGroup>

   <ItemGroup>
      <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.2" />
      <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
   </ItemGroup>

</Project>

DbContext and Entity

using Microsoft.EntityFrameworkCore;

namespace EfCoreVariableIssue;

public class DemoDbContext : DbContext
{
   public DbSet<DemoEntity> DemoEntities { get; set; }

   public DemoDbContext(DbContextOptions<DemoDbContext> options)
      : base(options)
   {
   }
}

public class DemoEntity
{
   public Guid Id { get; set; }
}

Program.cs

using EfCoreVariableIssue;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

var loggerFactory = LoggerFactory.Create(builder => builder
                                                    .SetMinimumLevel(LogLevel.Trace)
                                                    .AddConsole());

var options = new DbContextOptionsBuilder<DemoDbContext>()
              .UseSqlServer("server=localhost;database=VariableIssue;integrated security=true;")
              .UseLoggerFactory(loggerFactory)
              .Options;

using var dbContext = new DemoDbContext(options);
dbContext.Database.OpenConnection();
dbContext.Database.EnsureCreated();

The issue comes only when using FromSqlRaw + SqlParameter + GroupBy + Select, which has an aggregate like g.Count().

No issues when using

• .FromSqlInterpolated($"SELECT * FROM DemoEntities WHERE Id = {Guid.Empty}") or
• .FromSqlRaw("SELECT * FROM DemoEntities WHERE Id = {0}", Guid.Empty)

var query = dbContext.DemoEntities
                     .FromSqlRaw("SELECT * FROM DemoEntities WHERE Id = {0}", new SqlParameter { Value = Guid.Empty })
                     .Select(e => e.Id);

dbContext.DemoEntities
         .Where(e => query.Contains(e.Id))
         .GroupBy(e => e.Id)
         .Select(g => new { g.Key, Aggregate = g.Count() })
         .ToList();

Logs

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (24ms) [Parameters=[p0='?' (DbType = Guid), p0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id] AS [Key], (
          SELECT COUNT(*)
          FROM [DemoEntities] AS [d0]
          WHERE EXISTS (
              SELECT 1
              FROM (
                  SELECT * FROM DemoEntities WHERE Id = @p0
              ) AS [e0]
              WHERE [e0].[Id] = [d0].[Id]) AND ([d].[Id] = [d0].[Id])) AS [Aggregate]
      FROM [DemoEntities] AS [d]
      WHERE EXISTS (
          SELECT 1
          FROM (
              SELECT * FROM DemoEntities WHERE Id = @p0
          ) AS [e]
          WHERE [e].[Id] = [d].[Id])
      GROUP BY [d].[Id]
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'EfCoreVariableIssue.DemoDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): The variable name '@p0' has already been declared. Variable names must be unique within a query batch or stored procedure.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLUnhandled exception. ock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:d7b84c89-aa05-45e7-9eb2-dfaa349e20ec
      Error Number:134,State:1,Class:15
      Microsoft.Data.SqlClient.SqlException (0x80131904): The variable name '@p0' has already been declared. Variable names must be unique within a query batch or stored procedure.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:d7b84c89-aa05-45e7-9eb2-dfaa349e20ec
      Error Number:134,State:1,Class:15
Microsoft.Data.SqlClient.SqlException (0x80131904): The variable name '@p0' has already been declared. Variable names must be unique within a query batch or stored procedure.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<Main>$(String[] args) in E:\Projects\Test\Solution1\EfCoreVariableIssue\Program.cs:line 23

provider and version information

EF Core version: 6.0.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: win10 x64 IDE: JetBrains Rider 2021.3.3

[AndriySvyryd]

This is a regression caused by the fix to https://github.com/dotnet/efcore/issues/27102

Unfortunately the complete fix for both issues would be too risky for a patch. You can disable the fix for #27102 by calling this on startup:

AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue27102", true);

[KAJOOSH]

Hello I have the same problem,I can not disable these #27102 because I need it. Also, some other problems have been fixed in 6.0.2 and I need them too and I can not stay in version 6.0.1. Is not there a better solution until the release of version 7.0.0?

[AndriySvyryd]

We are going to investigate whether there is a less risky way to fix this, but it will take us some time and might not make it to 6.0.4

[ajcvickers]

@roji to do some research.

[roji]

@smitpatel if I understood correctly, you needed a way to find out if a parameter is already present in a DbParameterCollection - there's Contains(string).

Note Dapper doing what may be a similar thing here: first check if a parameter with a given name is in the collection, and add if not. If it's good enough for them it's probably good enough for us.

For reference, some providers have perf issues with this pattern (in Npgsql 6 we optimized this in https://github.com/npgsql/npgsql/issues/3978). But this only shows up with a lot of parameters, and it's the ADO.NET provider's problem, not EF's.

Note that at some point we probably want to support unnamed/positional parameters (#27377), at which point we'll have to reexamine this (but other parts already don't support unnamed, so we can forget about that for now...).

Let me know if you need something else!

[smitpatel]

I tried using Contains(DbParameter) (or rather Contains(SqlParameter) since I was running test against SqlServer provider) and it failed to find it even though it was same parameter reference. I will try to use string version and hope for the best.

[roji]

Let me know if it doesn't work somehow, I can investigate.