Query: Nested Where->Count query crashes when done in two operations

[SolalPirelli]

My real-life scenario is a DB with users and teams, where users have a reference to the team they are in; I'd like to get all teams T where the number of users in T is less than a certain number.

A simplified (hopefully minimal) repro is below - as you can see, doing this in one operation (.Where(...).Count()) works fine, but doing a Select first (which is convenient so I can get the object itself and not just the count of related objects) and then a Count fails. But only if it's in a Where, using a Select with the same condition works fine. Also, the in-memory and SQLite providers differ in their error messages for one case, but not for a very close query that also returns an error.

Steps to reproduce

Full repro, including queries that do not crash even though they're pretty close:

using System.Linq;
using Microsoft.EntityFrameworkCore;

class Program
{
    class A
    {
        public string Id { get; set; }
    }

    class Context : DbContext
    {
        public DbSet<A> As { get; set; }

        protected override void OnConfiguring( DbContextOptionsBuilder optionsBuilder )
        {
            optionsBuilder.UseInMemoryDatabase("test");
            //optionsBuilder.UseSqlite( "Data Source=test" );
        }
    }

    static void Main( string[] args )
    {
        var context = new Context();
        context.Database.EnsureCreated();

        // OK
        context.As.Select( a => context.As.Where( a2 => a2.Id == "a" ).Count() )
                  .ToArray();

        // OK
        context.As.Where( a => context.As.Where( a2 => a2.Id == "a" ).Count() == 0 )
                  .ToArray();

        // OK
        context.As.Select( a => context.As.Where( a2 => a2.Id == "a" ) )
                  .Select( x => x.Count() )
                  .ToArray();

        // OK
        context.As.Select( a => context.As.Where( a2 => a2.Id == "a" ) )
                  .Select( x => x.Count() == 0 )
                  .ToArray();
        
        // Fails on SQLite: 'must be reducible node'
        // Fails on InMemory: 'variable 'a2' of type 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer' referenced from scope '', but it is not defined'
        context.As.Select( a => context.As.Where( a2 => a2.Id == "a" ) )
                  .Where( x => x.Count() == 0 )
                  .ToArray();

        // Fails on both: 'variable 'a2' of type 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer' referenced from scope '', but it is not defined'
        context.As.Select( a => context.As.Where( a2 => a2 != null ) )
                  .Where( x => x.Count() == 0 )
                  .ToArray();
    }
}

Further technical details

EF Core version: 2.0.0
Database Provider: InMemory and SQLite (I haven't tested with others)
Operating system: Windows 10 (15063.540)
IDE: VS 2017 15.3.3

[smitpatel]

Both the failing queries fail because of #16314
If it is updated like following to update the type

                context.As.Select(a => context.As.Where(a2 => a2 != null))
                          .Where(x => x.Count() == 0)
                          .Select(x => x.ToList())
                          .ToArray();

It works for both the cases correctly. It fails on Sqlite because the query requires usage of OUTER APPLY which is not supported on Sqlite.

Filing additional issues to improve patterns overall.