Query: SubQuery with Aggregates causes unneeded queries in 1.0.0 and throws exception in 1.1.0-preview2-22683

[keflem]

Attached is a project that exhibits the behavior.

In EF Core 1.0.0 extra queries are generated and "warn" messages are logged. These extra queries are unneeded as the primary query generated properly retrieves the data that is needed. In fact, the queries are duplicated over and over again. Attached is the output.
EF1_0_0_output.txt

In EF Core 1.1.0-preview2-22683 the same code causes an exception and no querying is performed.

ArgumentOutOfRangeException
Exception message: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Stack trace:
   at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
   at System.Collections.ObjectModel.Collection`1.RemoveAt(Int32 index)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.RewriteSelectManyNavigationsIntoJoins(QuerySourceReferenceExpression outerQuerySourceReferenceExpression, IEnumerable`1 navigations, AdditionalFromClause additionalFromClauseBeingProcessed)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.<>c__DisplayClass20_0.<VisitMember>b__0(IEnumerable`1 ps, IQuerySource qs)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindPropertyExpressionCore[TResult](Expression propertyExpression, IQuerySource querySource, Func`3 propertyBinder)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindNavigationPathPropertyExpression[TResult](Expression propertyExpression, Func`3 propertyBinder)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitMember(MemberExpression node)
   at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.FromClauseBase.TransformExpressions(Func`2 transformation)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.FromClauseBase.TransformExpressions(Func`2 transformation)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitAdditionalFromClause(AdditionalFromClause fromClause, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
   at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Remotion.Linq.Clauses.SelectClause.TransformExpressions(Func`2 transformation)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.NavigationRewritingQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.NavigationRewritingExpressionVisitor.Rewrite(QueryModel queryModel, QueryModel parentQueryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, ILogger logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileQuery>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source, Int32& length)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at EFBug.Program.Main(String[] args) in C:\onebranch\SecEng\SAM\src\Temp\EFBug\Program.cs:line 26
[EFBug.zip](https://github.com/aspnet/EntityFramework/files/582254/EFBug.zip)

Operating system: Windows 10 Enterprise
Microsoft Visual Studio Enterprise 2015
Version 14.0.25123.00 Update 2
Microsoft .NET Framework
Version 4.6.01586

[keflem]

Forgot to attach the project. Here it is.
EFBug.zip

[anpete]

cc @maumar in case this is already fixed in 1.2.

[maumar]

This exception was fixed in 1.2.0 but the scenario hit a bug later in the pipeline. Here is the simplified standalone repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureCreated();

                var query = ctx.ScopeGroups
                    .Where(x => x.IsDeleted == false)
                    .Select(x => new
                    {
                        Entity = x,
                        IsNonCompliant = x.Hosts.SelectMany(y => y.NonCompliantAsTarget.Where(z => z.ExceptionId == null)).Any(),
                        //IsException = x.Hosts.SelectMany(y => y.NonCompliantAsTarget.Where(z => z.Exception.State == ExceptionState.Approved)).Any()
                    });

                query.ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<HostEntity> Hosts { get; set; }
        public DbSet<NonComplianceLogEntity> NonComplianceLog { get; set; }
        public DbSet<ScopeGroupEntity> ScopeGroups { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ScopeGroupEntity>().HasAlternateKey("ApplicationId", "Name");
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=Repro6982;Integrated Security=SSPI;");
        }
    }

    public class ScopeGroupEntity
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ScopeGroupId { get; set; }
        public int ApplicationId { get; set; }

        public string Name { get; set; }
        public bool IsDeleted { get; set; }

        [InverseProperty(nameof(HostEntity.ScopeGroup))]
        public List<HostEntity> Hosts { get; set; }
    }

    public class NonComplianceLogEntity
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int NonComplianceId { get; set; }

        public int TargetHostId { get; set; }
        public int? ExceptionId { get; set; }

        [ForeignKey(nameof(TargetHostId))]
        public HostEntity TargetHost { get; set; }

        [ForeignKey(nameof(ExceptionId))]
        public ExceptionEntity Exception { get; set; }
    }

    public class HostEntity
    {

        [ForeignKey(nameof(ScopeGroupId))]
        public ScopeGroupEntity ScopeGroup { get; set; }

        [InverseProperty(nameof(NonComplianceLogEntity.TargetHost))]
        public List<NonComplianceLogEntity> NonCompliantAsTarget { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int HostId { get; set; }

        public int? ScopeGroupId { get; set; }
    }

    public class ExceptionEntity
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ExceptionId { get; set; }
        public int TargetHostGroupId { get; set; }

        [ForeignKey(nameof(TargetHostGroupId))]
        public ScopeGroupEntity TargetHostGroup { get; set; }
    }

[maumar]

New exception is: System.ArgumentException: must be reducible node

The query plan is as follows:

TRACKED: True
(QueryContext queryContext) => IEnumerable<<>f__AnonymousType0<ScopeGroupEntity, bool>> _Select(
    source: IEnumerable<ScopeGroupEntity> _ShapedQuery(
        queryContext: queryContext, 
        shaperCommandContext: SelectExpression: 
            SELECT [x].[ScopeGroupId], [x].[ApplicationId], [x].[IsDeleted], [x].[Name]
            FROM [ScopeGroups] AS [x]
            WHERE [x].[IsDeleted] = 0
        , 
        shaper: BufferedEntityShaper<ScopeGroupEntity>
    )
    , 
    selector: (ScopeGroupEntity x) => new <>f__AnonymousType0<ScopeGroupEntity, bool>(
        x, 
        bool Any(
            source: IEnumerable<ValueBuffer> _Select(
                source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>>, ValueBuffer>> _InjectParameters(
                    queryContext: queryContext, 
                    source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>>, ValueBuffer>> _SelectMany(
                        source: IEnumerable<TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>>> _GroupJoin(
                            queryContext: (RelationalQueryContext) queryContext, 
                            source: IEnumerable<ValueBuffer> _Query(
                                queryContext: queryContext, 
                                shaperCommandContext: SelectExpression: 
                                    SELECT 1
                                    FROM [Hosts] AS [y1]
                                    LEFT JOIN [NonComplianceLog] AS [y.NonCompliantAsTarget1] ON [y1].[HostId] = [y.NonCompliantAsTarget1].[TargetHostId]
                                    WHERE @_outer_ScopeGroupId = [y1].[ScopeGroupId]
                                    ORDER BY [y1].[HostId]
                                , 
                                queryIndex: default(System.Nullable`1[System.Int32])
                            )
                            , 
                            outerShaper: ValueBufferShaper, 
                            innerShaper: ValueBufferShaper, 
                            innerKeySelector: (ValueBuffer y.NonCompliantAsTarget) => try { (int) object y.NonCompliantAsTarget.get_Item(0) } catch (Exception) { ... } , 
                            resultSelector: (ValueBuffer y | IEnumerable<ValueBuffer> y.NonCompliantAsTarget_group) => TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>> CreateTransparentIdentifier(
                                outer: y, 
                                inner: y.NonCompliantAsTarget_group
                            )
                            , 
                            outerGroupJoinInclude: default(Internal.GroupJoinInclude), 
                            innerGroupJoinInclude: default(Internal.GroupJoinInclude)
                        )
                        , 
                        collectionSelector: (TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>> t0) => IEnumerable<ValueBuffer> _Where(
                            source: t0.Inner, 
                            predicate: (ValueBuffer y.NonCompliantAsTarget_groupItem) => y.NonCompliantAsTarget_groupItem.ExceptionId == null
                        )
                        , 
                        resultSelector: (TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>> t0 | ValueBuffer y.NonCompliantAsTarget) => TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>>, ValueBuffer> CreateTransparentIdentifier(
                            outer: t0, 
                            inner: y.NonCompliantAsTarget
                        )
                    )
                    , 
                    parameterNames: new string[]{ "_outer_ScopeGroupId" }
                    , 
                    parameterValues: new object[]{ (object) Nullable<int> GetValueFromEntity(
                            clrPropertyGetter: ClrPropertyGetter`2, 
                            entity: x
                        )
                         }
                )
                , 
                selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<ValueBuffer>>, ValueBuffer> t1) => t1.Inner
            )
        )
    )
)

[maumar]

The exception is fixed in the current bits, N+1 queries are generated for this scenario due to #8083