InvalidOperation: Operation is not valid due to the current state of the object with subquery & SQLite

[albahari]

Exception message: Operation is not valid due to the current state of the object.
Stack trace:
Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)   at Microsoft.EntityFrameworkCore.Query.Pipeline.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)   at Microsoft.EntityFrameworkCore.Query.Pipeline.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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)   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.Query.Internal.EntityQueryable`1.GetEnumerator()   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
--

Steps to reproduce

Create a new project with a reference to Microsoft.EntityFrameworkCore.Sqlite\3.0.0-preview7.19362.6. Note that this doesn't occur with Microsoft.EntityFrameworkCore.SqlServer.

Run the query below.

void Main()
{
    using (var context = new MyContext())
    {
        context.Database.EnsureCreated();
        
        var query =
            from c in context.Customers
            select new
            {
                c.Name,
                Purchases =
                    from p in c.Purchases
                    where p.Price > 1000
                    select new { p.Description, p.Price }
            };

        query.ToArray();
    }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite (@"Data Source=database.db");
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Purchase> Purchases { get; set; }

    protected override void OnModelCreating (ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer> ().ToTable ("Customer");
        modelBuilder.Entity<Purchase> ().ToTable ("Purchase");
    }
}

public class Customer
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Purchase> Purchases { get; set; }
}

public class Purchase
{
    public int ID { get; set; }
    public int? CustomerID { get; set; }
    public DateTime Date { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }

    public virtual Customer Customer { get; set; }
}

Further technical details

EF Core version: 3.0.0-preview7.19362.6
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Win10
IDE: VS2019 16.3.0

[smitpatel]

Price is decimal column so p.Price > 1000 cannot be translated to server.
Refer to https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations

Better exception message throwing is covered by #16133