In a one-to-many query, if AutoInclude is configured, when querying 'many', there will be an extra 'many' in 'one' of 'many'

[z505985755]

I know this is hard to understand. Let me give you an example. There are two entities, factories and areas. One factory corresponds to multiple areas, and one area has one factory. There is a set of data, one factory and one area. When I query the area, I expect it should be a area, a factory under the area, and a area under the factory. But in fact, it is a area, a factory under the area, and two area under the factory, and these two area are the same.

Factory

public class Factory : CommonEntity<Factory>, IEntityTypeBuilder<Factory>
{
    public string? Description { get; set; }
    public UniversalType Type  { get; set; }
    public List<Area> Areas { get; set; }
    public new void Configure(EntityTypeBuilder<Factory> entityBuilder, DbContext dbContext, System.Type dbContextLocator)
    {
        base.Configure(entityBuilder, dbContext, dbContextLocator);
        //entityBuilder.HasMany(x => x.Areas).WithOne(x => x.Factory);
        entityBuilder.Navigation(x => x.Areas).AutoInclude();
        //entityBuilder.HasOne(x => x.Type);
        entityBuilder.Navigation(x => x.Type).AutoInclude();
    }
}

area

public class Area : CommonEntity<Area>, IEntityTypeBuilder<Area>
{
    public string? Description { get; set; }
    public Factory? Factory { get; set; }
    public UniversalType Type { get; set; }
    public new void Configure(EntityTypeBuilder<Area> entityBuilder, DbContext dbContext, Type dbContextLocator)
    {
        base.Configure(entityBuilder, dbContext, dbContextLocator);
        entityBuilder.Navigation(x => x.Factory).AutoInclude();
        entityBuilder.Navigation(x => x.Type).AutoInclude();
    }
}

query

    private TEntity GetOneObject<TEntity>(int Id, string Name)
        where TEntity : CommonEntity<TEntity>, new()
    {
        using (var repository = Db.GetRepository<TEntity>())
        {
            return repository.Context.Set<TEntity>().AsNoTracking().FirstOrDefault(x => x.Id == Id || x.Name == Name);
        }
    }

result

[roji]

Can you please post a fully runnable, minimal console program that shows the problem? Snippets and a screeshot do not allow us to easily understand and reproduce the problem.

[z505985755]

Can you please post a fully runnable, minimal console program that shows the problem? Snippets and a screeshot do not allow us to easily understand and reproduce the problem.

EFTest.zip of course

[ajcvickers]

Notes for team: the issue here is that the auto-Include creates a cycle which is not detected or resolved. Executing the query with the Includes added manually:

var area = db.Area
    .Include(e => e.Factory)
    .ThenInclude(e => e.Areas)
    .AsNoTracking()
    .First();

Results in:

Unhandled exception. System.InvalidOperationException: The Include path 'Factory->Areas' results in a cycle. Cycles are not allowed in no-tracking queries; either use a tracking query or remove the cycle.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.VerifyNoCycles(IncludeTreeNode includeTreeNode)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.ExpandInclude(Expression root, EntityReference entityReference)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandingExpressionVisitor.Expand(Expression expression, Boolean applyIncludes)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.PendingSelectorExpandingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.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 EFTest.Program.Main(String[] args) in D:\code\repros\EFTest\EFTest\EFTest\Program.cs:line 14

Note that the query generated by the single Include is:

      SELECT "a"."Id", "a"."FactoryId", "a"."Name", "f"."Id", "f"."Name"
      FROM "Area" AS "a"
      LEFT JOIN "Factory" AS "f" ON "a"."FactoryId" = "f"."Id"
      LIMIT 1

The query generated by the double auto-Include is:

      SELECT "t"."Id", "t"."FactoryId", "t"."Name", "t"."Id0", "t"."Name0", "a0"."Id", "a0"."FactoryId", "a0"."Name"
      FROM (
          SELECT "a"."Id", "a"."FactoryId", "a"."Name", "f"."Id" AS "Id0", "f"."Name" AS "Name0"
          FROM "Area" AS "a"
          LEFT JOIN "Factory" AS "f" ON "a"."FactoryId" = "f"."Id"
          LIMIT 1
      ) AS "t"
      LEFT JOIN "Area" AS "a0" ON "t"."Id0" = "a0"."FactoryId"
      ORDER BY "t"."Id", "t"."Id0"