Variable names must be unique within a query batch or stored procedure

[sandersaares]

I unexpectedly received the exception:

System.Data.SqlClient.SqlException (0x80131904): The variable name '@___job_0_Id' has already been declared. Variable names must be unique within a query batch or stored procedure.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   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)
ClientConnectionId:c2e0bafa-b323-4e64-809c-64ec7927b4ce
Error Number:134,State:1,Class:15

Exploring logs indicates the following as the problematic query:

Executing DbCommand [Parameters=[@___job_0_Id='586a534e-45e2-4e5f-b9ae-a9f300938aba', @___job_0_Id='586a534e-45e2-4e5f-b9ae-a9f300938aba'], CommandType='Text', CommandTimeout='30']
SELECT [row].[Id], [row].[Created], [row].[CreatedBy], [row].[Error], [row].[FailedAttemptCount], [row].[Finished], [row].[InputDocument], [row].[LastHeartbeat], [row].[NotBefore], [row].[OutputDocument], [row].[ParentId], [row].[PredecessorId], [row].[RowVersion], [row].[State], [row].[Title], [row].[Type], [row].[Id], [row].[Version_Build], [row].[Version_Major], [row].[Version_Minor], [row].[Version_Revision], [row].[Id], [row].[RetryPolicy_MaxAttemptDuration], [row].[RetryPolicy_MaxRetryCount]
FROM [Job] AS [row]
WHERE [row].[Id] IN (
    SELECT [row0].[FromId]
    FROM [JobGraph] AS [row0]
    WHERE ([row0].[ToId] = @___job_0_Id) AND ([row0].[FromId] <> @___job_0_Id)
)

Indeed, two parameters with the same name (and value) are listed.

I paste here more log entries relevant to the case:

2019-02-13T09:20:14.3775936Z|DEBUG|Microsoft.EntityFrameworkCore.Query|Compiling query model: 
'from JobRow row in DbSet<JobRow>
where 
    (from JobGraphRow row in DbSet<JobGraphRow>
    where [row].To == ___job_0 && [row].From != ___job_0
    select [row].FromId).Contains([row].Id)
select [row]'
2019-02-13T09:20:14.3775936Z|DEBUG|Microsoft.EntityFrameworkCore.Query|Including navigation: '[row].'
2019-02-13T09:20:14.3775936Z|DEBUG|Microsoft.EntityFrameworkCore.Query|Including navigation: '[row].'
2019-02-13T09:20:14.3775936Z|DEBUG|Microsoft.EntityFrameworkCore.Query|Optimized query model: 
'from JobRow row in DbSet<JobRow>
join VersionNumber row.Version in DbSet<VersionNumber>
on Property([row], "Id") equals Property([row.Version], "JobRowId") into row.Version_group
from VersionNumber row.Version in 
    (from VersionNumber row.Version_groupItem in [row.Version_group]
    select [row.Version_groupItem]).DefaultIfEmpty()
join JobRetryPolicy row.RetryPolicy in DbSet<JobRetryPolicy>
on Property([row], "Id") equals Property([row.RetryPolicy], "JobRowId") into row.RetryPolicy_group
from JobRetryPolicy row.RetryPolicy in 
    (from JobRetryPolicy row.RetryPolicy_groupItem in [row.RetryPolicy_group]
    select [row.RetryPolicy_groupItem]).DefaultIfEmpty()
where 
    (from JobGraphRow row in DbSet<JobGraphRow>
    where (Nullable<Guid>)Property([row], "ToId") == Property(___job_0, "Id") && (Nullable<Guid>)Property([row], "FromId") != Property(___job_0, "Id")
    select [row].FromId).Contains([row].Id)
select JobRow _Include(
    queryContext: queryContext, 
    entity: [row], 
    included: new object[]
    { 
        [row.RetryPolicy], 
        [row.Version] 
    }, 
    fixup: (QueryContext queryContext | JobRow entity | object[] included) => 
    {
        void queryContext.QueryBuffer.StartTracking(
            entity: entity, 
            entityType: EntityType: JobRow)
        !(bool ReferenceEquals(included[0], null)) ? 
        {
            void queryContext.QueryBuffer.StartTracking(
                entity: included[0], 
                entityType: EntityType: JobRetryPolicy)
            return void SetRelationshipSnapshotValue(
                stateManager: queryContext.StateManager, 
                navigation: JobRow.RetryPolicy, 
                entity: entity, 
                value: included[0])
        } : 
        {
            void SetRelationshipIsLoaded(
                stateManager: queryContext.StateManager, 
                navigation: JobRow.RetryPolicy, 
                entity: entity)
            return default(void)
        }
        return !(bool ReferenceEquals(included[1], null)) ? 
        {
            void queryContext.QueryBuffer.StartTracking(
                entity: included[1], 
                entityType: EntityType: VersionNumber)
            return void SetRelationshipSnapshotValue(
                stateManager: queryContext.StateManager, 
                navigation: JobRow.Version, 
                entity: entity, 
                value: included[1])
        } : 
        {
            void SetRelationshipIsLoaded(
                stateManager: queryContext.StateManager, 
                navigation: JobRow.Version, 
                entity: entity)
            return default(void)
        }
    })'
2019-02-13T09:20:14.5036223Z|DEBUG|Microsoft.EntityFrameworkCore.Query|(QueryContext queryContext) => IAsyncEnumerable<JobRow> _InterceptExceptions(
|__ source: IAsyncEnumerable<JobRow> _TrackEntities(
|   |__ results: IAsyncEnumerable<JobRow> _Select(
|   |   |__ source: IAsyncEnumerable<TransparentIdentifier<TransparentIdentifier<JobRow, VersionNumber>, JobRetryPolicy>> _ShapedQuery(
|   |   |   |__ queryContext: queryContext, 
|   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |       SELECT [row].[Id], [row].[Created], [row].[CreatedBy], [row].[Error], [row].[FailedAttemptCount], [row].[Finished], [row].[InputDocument], [row].[LastHeartbeat], [row].[NotBefore], [row].[OutputDocument], [row].[ParentId], [row].[PredecessorId], [row].[RowVersion], [row].[State], [row].[Title], [row].[Type], [row].[Id], [row].[Version_Build], [row].[Version_Major], [row].[Version_Minor], [row].[Version_Revision], [row].[Id], [row].[RetryPolicy_MaxAttemptDuration], [row].[RetryPolicy_MaxRetryCount]
|   |   |   |       FROM [Job] AS [row]
|   |   |   |       WHERE [row].[Id] IN (
|   |   |   |           SELECT [row0].[FromId]
|   |   |   |           FROM [JobGraph] AS [row0]
|   |   |   |           WHERE ([row0].[ToId] = @___job_0_Id) AND ([row0].[FromId] <> @___job_0_Id)
|   |   |   |       ), 
|   |   |   |__ shaper: (TransparentIdentifier<JobRow, VersionNumber> t1 | JobRetryPolicy row.RetryPolicy) => TransparentIdentifier<TransparentIdentifier<JobRow, VersionNumber>, JobRetryPolicy> CreateTransparentIdentifier(
|   |   |       |__ outer: t1, 
|   |   |       |__ inner: row.RetryPolicy)), 
|   |   |__ selector: (TransparentIdentifier<TransparentIdentifier<JobRow, VersionNumber>, JobRetryPolicy> t3) => JobRow _Include(
|   |       |__ queryContext: queryContext, 
|   |       |__ entity: t3.Outer.Outer, 
|   |       |__ included: new object[]
|   |       |   { 
|   |       |       t3.Inner, 
|   |       |       t3.Outer.Inner 
|   |       |   }, 
|   |       |__ fixup: (QueryContext queryContext | JobRow entity | object[] included) => 
|   |           {
|   |               void queryContext.QueryBuffer.StartTracking(
|   |                   entity: entity, 
|   |                   entityType: EntityType: JobRow)
|   |               !(bool ReferenceEquals(included[0], null)) ? 
|   |               {
|   |                   void queryContext.QueryBuffer.StartTracking(
|   |                       entity: included[0], 
|   |                       entityType: EntityType: JobRetryPolicy)
|   |                   return void SetRelationshipSnapshotValue(
|   |                       stateManager: queryContext.StateManager, 
|   |                       navigation: JobRow.RetryPolicy, 
|   |                       entity: entity, 
|   |                       value: included[0])
|   |               } : 
|   |               {
|   |                   void SetRelationshipIsLoaded(
|   |                       stateManager: queryContext.StateManager, 
|   |                       navigation: JobRow.RetryPolicy, 
|   |                       entity: entity)
|   |                   return default(void)
|   |               }
|   |               return !(bool ReferenceEquals(included[1], null)) ? 
|   |               {
|   |                   void queryContext.QueryBuffer.StartTracking(
|   |                       entity: included[1], 
|   |                       entityType: EntityType: VersionNumber)
|   |                   return void SetRelationshipSnapshotValue(
|   |                       stateManager: queryContext.StateManager, 
|   |                       navigation: JobRow.Version, 
|   |                       entity: entity, 
|   |                       value: included[1])
|   |               } : 
|   |               {
|   |                   void SetRelationshipIsLoaded(
|   |                       stateManager: queryContext.StateManager, 
|   |                       navigation: JobRow.Version, 
|   |                       entity: entity)
|   |                   return default(void)
|   |               }
|   |           })), 
|   |__ queryContext: Unhandled parameter: queryContext, 
|   |__ entityTrackingInfos: { itemType: JobRow }, 
|   |__ entityAccessors: List<Func<JobRow, object>> 
|       { 
|           Func<JobRow, JobRow>, 
|       }), 
|__ contextType: App.Database.AppDataContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: Unhandled parameter: queryContext)

I believe this is the code in question that I use for my query:

            var jobsNodesToDelete = _dc.JobGraph
                .Where(row => row.To == _job && row.From != _job)
                .Select(row => row.FromId);

            var jobsToDelete = await _dc.Job
                .Where(row => jobsNodesToDelete.Contains(row.Id))
                .ToArrayAsync(_cancel);

What do I need to do to get my query to work?

Possibly related: #12871

Currently unable to prepare a repro app but I hope the log entries paint a picture of what is happening. If necessary, I can try make a repro app next week.

Further technical details

EF Core version: 2.2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 1809
IDE: VS 15.9.5

[sandersaares]

Changing my code to compare by the foreign key instead of the navigation property seems to act as a workaround - the variable duplication disappears in this case.

[ajcvickers]

@smitpatel to find dupe.

[smitpatel]

Duplicate of #14645