Duplicate table alias in generated select query (An item with the same key has already been added)

[OldrichDlouhy]

EF Core Query results in argument exception An item with the same key has already been added. Key: c0

caused by generated duplicate table alias

Code

Query causing the problem:

        var query = dbContext.ContainerConfigurations
            .AsSplitQuery()
            .Include(e => e.Container)
                .ThenInclude(e => e.HardwareUnit)
                    .ThenInclude(e => e.CurrentConfiguration)
            .Where(e => e.Fraction == entity);

        query.ToQueryString();

Solution with test failing with duplicate table alias:

EFCoreDuplicateTableAlias.zip

The issue manifested after upgrading from EF Core 6 to EF Core 7. With MySQL Pomelo provider, the issue manifests both for split and non-split query. For SQLite provider (unit test) it fails with split query.

It may be related to global query filters as removing the filters makes the issue go away.

Stack trace

System.ArgumentException
  HResult=0x80070057
  Message=An item with the same key has already been added. Key: c0
  Source=System.Private.CoreLib
  StackTrace:
   at System.ThrowHelper.ThrowAddingDuplicateWithKeyArgumentException[T](T key)
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey](List`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ColumnExpressionReplacingExpressionVisitor..ctor(SelectExpression oldSelectExpression, IEnumerable`1 newTableReferences)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.CloningExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.InnerJoinExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.CloningExpressionVisitor.Visit(Expression expression)
   at System.Linq.Enumerable.SelectListIterator`2.MoveNext()
   at System.Linq.Enumerable.<OfTypeIterator>d__65`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Utilities.EnumerableExtensions.ToList[TSource](IEnumerable source)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.CloningExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at eGateDigi.Web.Services.Containers.Configuration.UpdateFractionHandler.<Handle>d__3.MoveNext() in C:\Code\eGateDigi\eGateDigi.Web\UpdateFraction.cs:line 89

Provider and version information

EF Core version: 7.0.3 Database provider: Microsoft.EntityFrameworkCore.SqLite 7.0.3 Target framework: .NET 7.0 Operating system: Windows 10 19044.2604 IDE: Visual Studio 2022 17.4

[ajcvickers]

/cc @maumar

[maumar]

simplified repro:

    [ConditionalFact]
    public void Test30358()
    {
        using var dbContext = new EGateDigiDbContext();

        dbContext.Database.EnsureDeleted();
        dbContext.Database.EnsureCreated();

        var query = dbContext.ContainerConfigurations
            .Include(e => e.Container)
            .ToList();
    }

    public class Container
    {
        public int Id { get; private set; }

        public int CustomerId { get; private set; }

        public string Name { get; set; }

        public ContainerFillLevelBehavior FillLevelBehavior { get; set; }

        public ContainerConfiguration CurrentConfiguration { get; private set; }
    }

    public class ContainerFillLevelBehavior
    {
        public int ThresholdRed { get; set; }
    }

    public class ContainerConfiguration
    {
        public int Id { get; private set; }

        public int ContainerId { get; private set; }

        public Container Container { get; private set; }
    }

    public class EGateDigiDbContext : DbContext
    {
        public DbSet<Container> Containers { get; set; }

        public DbSet<ContainerConfiguration> ContainerConfigurations { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ContainerConfiguration>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.Id)
                    .IsRequired()
                    .ValueGeneratedOnAdd();

                // Current configuration is 1:1 relationship
                entity.HasOne(e => e.Container)
                    .WithOne(x => x.CurrentConfiguration)
                    // Configuration is the dependent entity
                    .HasForeignKey<ContainerConfiguration>(e => e.ContainerId)
                    .IsRequired()
                    .OnDelete(DeleteBehavior.Cascade);

                entity.HasIndex(c => c.ContainerId).IsUnique();
            });

            modelBuilder.Entity<Container>(entity =>
            {
                entity
                    .ToTable("Containers")
                    .HasKey(e => e.Id);

                entity.Property(e => e.Id)
                    .IsRequired()
                    .ValueGeneratedOnAdd();

                entity.HasIndex(e => e.CustomerId);

                entity.Property(e => e.Name)
                    .HasMaxLength(250)
                    .IsRequired();

                entity.OwnsOne(e => e.FillLevelBehavior,
                    o => o.ToTable("ContainerFillLevelBehavior")
                );
            });

            modelBuilder.Entity<Container>()
                .HasQueryFilter(c => c.CustomerId == 1);
        }
    }

problematic query:

ShapedQueryExpression: 
    QueryExpression: 
        Client Projections:
            0 -> EntityProjectionExpression: ContainerConfiguration
            1 -> EntityProjectionExpression: Container
            2 -> EntityProjectionExpression: ContainerFillLevelBehavior
        SELECT 1
        FROM ContainerConfigurations AS c
        INNER JOIN 
        (
            SELECT c0.Id, c0.CustomerId, c0.Name, c0.ContainerId, c0.ThresholdRed
            FROM Containers AS c0
            LEFT JOIN ContainerFillLevelBehavior AS c0 ON c0.Id == c0.ContainerId
            WHERE c0.CustomerId IN (@__ef_filter__AccessibleCustomers_0)
        ) AS t ON c.ContainerId == t.Id
    ShaperExpression: IncludeExpression(
            EntityExpression:
            EntityShaperExpression: 
                QueryBugsTest+ContainerConfiguration
                ValueBufferExpression: 
                    ProjectionBindingExpression: 0
                IsNullable: False
            , 
            NavigationExpression:
            IncludeExpression(
                EntityExpression:
                EntityShaperExpression: 
                    QueryBugsTest+Container
                    ValueBufferExpression: 
                        ProjectionBindingExpression: 1
                    IsNullable: False
                , 
                NavigationExpression:
                EntityShaperExpression: 
                    QueryBugsTest+ContainerFillLevelBehavior
                    ValueBufferExpression: 
                        ProjectionBindingExpression: 2
                    IsNullable: True
                , FillLevelBehavior)
            , Container)

[maumar]

every SelectExpression has a list of aliases that it uses (so that when new table is added we know if we need to uniqify the alias or is the default one fine). When we combine two select expressions using join, we look at aliases in both and uniqify the duplicate ones in the inner select. However, we don't change the internal list of used aliases, so later when new join is added (due to nav owned type expansion) and it happens to try to use the same alias as before, we generate incorrect unique alias for it.