Efcore: EF.CompileQuery() parse failure with Count() == 1

[Epic-Santiago]

See sample code below. I have a simple query that works fine when run uncompiled. It however fails when running compiled.

Exception message:
Cannot parse expression '({value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Erp.Tables.GLSyst]) => Count()} == 1)' as it has an unsupported type. Only query sources (that is, expressions that implement IEnumerable) and query operators can be parsed.

Stack trace:
System.NotSupportedException: Cannot parse expression '({value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Erp.Tables.GLSyst]) => Count()} == 1)' as it has an unsupported type. Only query sources (that is, expressions that implement IEnumerable) and query operators can be parsed. ---> System.ArgumentException: Parameter 'expression.Type' is a 'System.Boolean', which cannot be assigned to type 'System.Collections.IEnumerable'.
Parameter name: expression.Type
   at Remotion.Utilities.ArgumentUtility.CheckTypeIsAssignableFrom(String argumentName, Type actualType, Type expectedType)
   at Remotion.Linq.Parsing.Structure.IntermediateModel.MainSourceExpressionNode..ctor(String associatedIdentifier, Expression expression)
   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseNonQueryOperatorExpression(Expression expression, String associatedIdentifier)
   --- End of inner exception stack trace ---
   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseNonQueryOperatorExpression(Expression expression, String associatedIdentifier)
   at Remotion.Linq.Parsing.Structure.ExpressionTreeParser.ParseTree(Expression expressionTree)
   at Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CreateCompiledQuery[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam1,TParam2,TValue](TValue& target, TParam1 param1, TParam2 param2, Func`3 valueFactory)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.EnsureExecutor(TContext context)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.ExecuteCore(TContext context, CancellationToken cancellationToken, Object[] parameters)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryBase`2.ExecuteCore(TContext context, Object[] parameters)
   at Erp.Services.BO.CompanySvc.Repro(ErpContext dataContext) in C:\_Projects\ERPStaging\EFCore\Source\Server\Services\BO\Company\Company.cs:line 931
   at Erp.Services.BO.CompanySvc.ARSystAfterGetRows() in C:\_Projects\ERPStaging\EFCore\Source\Server\Services\BO\Company\Company.cs:line 864

Steps to reproduce

Execute the code below.

```c#
private static void Repro(ErpContext dataContext)
{
// Non-compiled query works fine.
var result1 = (from row in dataContext.GLSyst
select row).Count() == 1;

// Compiled query blows up.
Expression<Func<ErpContext, bool>> expression =
    (dataContext_ex) =>
    (from row in dataContext_ex.GLSyst
        select row).Count() == 1;

var query = EF.CompileQuery(expression);

var result2 = query(dataContext);

}
```

Further technical details

EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.8.9

[Epic-Santiago]

Should have mentioned that it works fine in EF6 with CompiledQuery.Compile().

[ajcvickers]

@smitpatel @maumar Can you please look into workarounds for this one.

[maumar]

Workaround is to do compile query on the entity count but perform comparison on the client:

                Expression<Func<MyContext, int>> expression =
                    (dataContext_ex) =>
                    (from row in dataContext_ex.GLSyst
                     select row).Count();

                var query = EF.CompileQuery(expression);

                var result2 = query(ctx) == 1;

[Epic-Santiago]

I can confirm that the suggested workaround addresses the issue. However we have hundreds of such queries and reworking them all is quite the undertaking.

[ajcvickers]

Note from triage: @smitpatel to look into different workarounds, such as a replacement for CompileQuery that will handle this case.

[Epic-Santiago]

We found other cases that EF.CompileQuery(query) couldn't handle. For now we've disabled this and are instead using a standard expression compile: query.Compile(). There's likely a performance hit in that but CompileQuery() was holding up our migration so we had to pull the plug. We will switch back to it once the issues are addressed.

[divega]

@Epic-Santiago can you remind me if you tested that you are actually getting a significant performance boost from using explicitly compiled queries with EF Core?

[smitpatel]

```C#
public static Func CompileQuery(Expression> expression)
where TContext : DbContext
{
var body = expression.Body;
if (body is BinaryExpression binaryExpression
&& binaryExpression.NodeType == ExpressionType.Equal
&& binaryExpression.Right is ConstantExpression constantExpression)
{
var queryExpression = Expression.Lambda(binaryExpression.Left, expression.Parameters);
var method = typeof(EF).GetMethods()
.Where(mi => string.Equals(mi.Name, nameof(EF.CompileQuery))
&& mi.GetGenericArguments().Length == 2).Last();

            var compileQueryCall = Expression.Call(
                null,
                method.MakeGenericMethod(new[] { typeof(TContext), binaryExpression.Left.Type }),
                queryExpression);

            return Expression.Lambda<Func<TContext, TResult>>(
                Expression.MakeBinary(ExpressionType.Equal, Expression.Invoke(compileQueryCall, expression.Parameters), binaryExpression.Right),
                expression.Parameters).Compile();
        }

        return EF.CompileQuery(expression);
    }

// Following works using above function
Expression> expression = (ctx) => (from b in ctx.Blogs select b).Count() == 1;
var query = CompileQuery(expression);

            var result = query(db);

```

Basically, the issue occurs when you are passing in an expression which is neither IQueryable or an operator over IQueryable. You can identify such case using pattern match and call EF.CompileQuery on part of it and use that func to generate a larger func which gives you result you want. It is somewhat advanced scenario but if all the occurrences of failures are limited to few pattern this could be easy to work-out.

[Epic-Santiago]

@Epic-Santiago can you remind me if you tested that you are actually getting a significant performance boost from using explicitly compiled queries with EF Core?

Apologies for the delayed reply.
We have not tested this. Our plan was to have a full build with and without compilation and compare that. However, the build with compilation didn't work out due to runtime issues like the one reported here so that test didn't happen. We are now running with compilation off only (actually, we're running with expression compiles as described above because the structure of our code expects compiled delegates).
That means I will have to create an artificial test to compare a few scenarios with and without compilation. This is planned but still a few weeks out.

[divega]

@Epic-Santiago thanks for your answer. Have you had a chance to try @smitpatel’s workaround?

[Epic-Santiago]

I haven't yet had a chance to try out that workaround. We are running a scale lab this week and last and have been focused on that. Once that settles down I'll get a chance to review.

[Epic-Santiago]

I see that this issue has been fixed. Thanks all for your efforts!
Looking forward to testing it. Is there a way to tell on GitHub what release this will ship with?

[smitpatel]

@Epic-Santiago - Milestone the issue is assigned to correspond to the first release when this was fixed. This issue would be working in 3.1 release.