Efcore: 2.1 Preview-1 test failing

[ErikEJ]

Output from Microsoft.EntityFrameworkCore.PropertyEntrySqlCeTest.Property_entry_original_value_is_set:
Entity Framework Core 2.1.0-preview1-28290 initialized 'F1Context' using provider 'EntityFrameworkCore.SqlServerCompact40' with options: SensitiveDataLoggingEnabled MaxBatchSize=1

Beginning transaction with isolation level 'Unspecified'.

  Compiling query model: 
  '(from Engine e in DbSet<Engine>
  order by [e].Id asc
  select [e]).First()'

  Including navigation: '[e].'

  Optimized query model: 
  '(from Engine e in DbSet<Engine>
  join Location e.StorageLocation in DbSet<Location>
  on (Nullable<int>)Property([e], "Id") equals Property([e.StorageLocation], "EngineId") into e.StorageLocation_group
  from Location e.StorageLocation in 
      (from Location e.StorageLocation_groupItem in [e.StorageLocation_group]
      select [e.StorageLocation_groupItem]).DefaultIfEmpty()
  order by [e].Id asc
  select Engine _Include(
      queryContext: queryContext, 
      entity: [e], 
      included: new object[]{ [e.StorageLocation] }, 
      fixup: (QueryContext queryContext | Engine entity | object[] included) => 
      {
          void queryContext.QueryBuffer.StartTracking(
              entity: entity, 
              entityType: EntityType: Engine)
          return !(bool ReferenceEquals(included[0], null)) ? 
          {
              void queryContext.QueryBuffer.StartTracking(
                  entity: included[0], 
                  entityType: EntityType: Location)
              return void SetRelationshipSnapshotValue(
                  stateManager: queryContext.StateManager, 
                  navigation: Engine.StorageLocation, 
                  entity: entity, 
                  value: included[0])
          }
           : default(void)
      }
  )).First()'

  (QueryContext queryContext) => IEnumerable<Engine> _InterceptExceptions(
      source: IEnumerable<Engine> _TrackEntities(
          results: IEnumerable<Engine> _ToSequence(() => Engine First(IEnumerable<Engine> _Select(
                      source: IEnumerable<TransparentIdentifier<Engine, Location>> _ShapedQuery(
                          queryContext: queryContext, 
                          shaperCommandContext: SelectExpression: 
                              SELECT TOP(1) [e].[Id], [e].[EngineSupplierId], [e].[Name], [e].[Id], [e].[StorageLocation_Latitude], [e].[StorageLocation_Longitude]
                              FROM [Engines] AS [e]
                              ORDER BY [e].[Id], 
                          shaper: TypedCompositeShaper<BufferedOffsetEntityShaper<Engine>, Engine, BufferedOffsetEntityShaper<Location>, Location, TransparentIdentifier<Engine, Location>>), 
                      selector: (TransparentIdentifier<Engine, Location> t1) => Engine _Include(
                          queryContext: queryContext, 
                          entity: t1.Outer, 
                          included: new object[]{ t1.Inner }, 
                          fixup: (QueryContext queryContext | Engine entity | object[] included) => 
                          {
                              void queryContext.QueryBuffer.StartTracking(
                                  entity: entity, 
                                  entityType: EntityType: Engine)
                              return !(bool ReferenceEquals(included[0], null)) ? 
                              {
                                  void queryContext.QueryBuffer.StartTracking(
                                      entity: included[0], 
                                      entityType: EntityType: Location)
                                  return void SetRelationshipSnapshotValue(
                                      stateManager: queryContext.StateManager, 
                                      navigation: Engine.StorageLocation, 
                                      entity: entity, 
                                      value: included[0])
                              }
                               : default(void)
                          }
                      )))), 
          queryContext: Unhandled parameter: queryContext, 
          entityTrackingInfos: { itemType: Engine }, 
          entityAccessors: List<Func<Engine, object>> 
          { 
              Func<Engine, Engine>, 
          }
      ), 
      contextType: TestModels.ConcurrencyModel.F1Context, 
      logger: DiagnosticsLogger<Query>, 
      queryContext: Unhandled parameter: queryContext)

  Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='0']
  SELECT TOP(1) [e].[Id], [e].[EngineSupplierId], [e].[Name], [e].[Id], [e].[StorageLocation_Latitude], [e].[StorageLocation_Longitude]
  FROM [Engines] AS [e]
  ORDER BY [e].[Id]

  Failed executing DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
  SELECT TOP(1) [e].[Id], [e].[EngineSupplierId], [e].[Name], [e].[Id], [e].[StorageLocation_Latitude], [e].[StorageLocation_Longitude]
  FROM [Engines] AS [e]
  ORDER BY [e].[Id]

  An exception occurred in the database while iterating the results of a query for context type 'Microsoft.EntityFrameworkCore.TestModels.ConcurrencyModel.F1Context'.
  System.Data.SqlServerCe.SqlCeException (0x80004005): The column name cannot be resolved to a table. Specify the table to which the column belongs. [ Name of ambiguous column = Id ]
     at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
     at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
     at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
     at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
     at System.Data.Common.DbCommand.ExecuteReader()
     at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
     at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
     at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
     at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
     at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
     at lambda_method(Closure )
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

  An exception occurred in the database while iterating the results of a query for context type 'Microsoft.EntityFrameworkCore.TestModels.ConcurrencyModel.F1Context'.
  System.Data.SqlServerCe.SqlCeException (0x80004005): The column name cannot be resolved to a table. Specify the table to which the column belongs. [ Name of ambiguous column = Id ]
     at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
     at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
     at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
     at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
     at System.Data.Common.DbCommand.ExecuteReader()
     at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
     at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
     at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
     at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
     at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
     at lambda_method(Closure )
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
     at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)

  Disposing transaction.

0 passed, 1 failed, 0 skipped, took 3,52 seconds (xUnit.net 2.3.1 build 3858).

Further technical details

IDE: Visual Studio 2017 15.6

[ErikEJ]

Looks like it is the duplicate e.Id in the query that is the issue

[ajcvickers]

@ErikEJ We discussed this in triage. The same query is generated on SQL Server and doesn't run into issues, and we haven't seen issues for other providers either. Based on this, for now we think the best thing to do is to fix it in the CE provider. @smitpatel will provide some guidance to that end, so assigning to him for now to give the guidance.

[smitpatel]

From my understanding the offending query is

  SELECT TOP(1) [e].[Id], [e].[EngineSupplierId], [e].[Name], [e].[Id], [e].[StorageLocation_Latitude], [e].[StorageLocation_Longitude]
  FROM [Engines] AS [e]
  ORDER BY [e].[Id]

Assuming having same column multiple times in projection and also used by order by clause, it is invalid on SQL CE,
In EF Core, SelectExpression assign unique names to projection only in subqueries. At top level 2 columns of same name (coming from same or different table) would remain without aliasing.

The easiest way to solve is to give alias in SqlCE provider to avoid ambiguity.
https://github.com/aspnet/EntityFrameworkCore/blob/dc4ed044fcf9b2e7f23dbf7cb7921edfdd00b67f/src/EFCore.Relational/Query/RelationalQueryModelVisitor.cs#L287-L301

Pattern would be like above in SqlCeQueryModelVisitor overriding the method. Iterate over all selectExpressions in the QMV. And call a method which inspect projection of the SelectExpression. Keep track of already used names. If you encounter a duplicate name then call selectExpression.CreateUniqueProjection method, It will assign unique name to column and update projection & Order by automatically.

[ErikEJ]

@smitpatel Thanks, but quite over my head - in particular: "a method which inspect projection of the SelectExpression"

[smitpatel]

Something like this
C# var names = new HashSet<string>(); foreach (var projectionExpression in SelectExpression.Projection) { var name = GetColumnName(projectionExpression); if (names.Contains(names)) { SelectExpression.CreateUniqueProjection(projectionExpression); // optionally save new name in names but probably won't be needed. } else { names.Add(name); } }
https://github.com/aspnet/EntityFrameworkCore/blob/957524efe15cfff76f8face2b9a3d1ef840b50d1/src/EFCore.Relational/Query/Expressions/SelectExpression.cs#L773-L781

[ErikEJ]

@smitpatel Thanks so much for all your help!