InvalidCastException thrown when try to get generated SQL out in EFCore 3.1

[wadee]

Hi guys,
I have encounter an issue when I try to get the generated SQL in EF Core 3.1.
In my scenario for some reasons, I need to using the dynamic expression to construct the criteria in where clause for filtering with set, for example: "x => someSet.Contains(x.attribute)".
After that I have to get the generated SQL out and do some raw string operation with it to get the final SQL I want to execute.
But it thrown an InvalidCastException when executing at the line "var command = sqlGenerator.GetCommand(selectExpression);" in ToSQL method, for example:

var expression = CreateSetFilterExpression<SomeEntity, int>(AttributeNameInSomeEntity, List<int>{1,2,3});
SomeQueryable = SomeQueryable.Where(expression);
SomeQueryable.ToSQL();

The detail of InvalidCastException would be like:

Exception: System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List`1[System.Int32]' to type 'System.Collections.Generic.IEnumerable`1[System.Object]'.
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitIn(InExpression inExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
   at Microsoft.EcoManager.Domain.Core.QueryableExtensions.ToSql[TEntity](IQueryable`1 query)

It seems related to the VisitIn method of the QuerySqlGenerator:
https://github.com/dotnet/efcore/blob/release/3.1/src/EFCore.Relational/Query/QuerySqlGenerator.cs#L578

I searched about "int cast to object", and check the boxing and unboxing concept in C#, but I still have no idea how to fix it.

What even more funny is, if I try not get the generated SQL out and even I enable the logger in DbContextOptionsBuilder, it was work well.

     return new DbContextOptionsBuilder()
                .UseLoggerFactory(MyLoggerFactory)
                .EnableSensitiveDataLogging()

It seems there is another QuerySQLGenerator used with the normal usage and logger in EFCore?

There are too many doubts I have not figure it out.
Could me please help me figure out what is going on with it?
Please let me know if more information is needed.

Here are the code snippet:
CreateSetFilterExpression():

private static Expression<Func<TData, bool>> CreateSetFilterExpression<TData, TProperty>(string property, IEnumerable<TProperty> values)
{
    var type = typeof(TData);
    var arg = Expression.Parameter(type, "x");

    var propertyInfo = type.GetProperty(property);
    Expression exp = Expression.Property(arg, propertyInfo);
    exp = Expression.Convert(exp, typeof(TProperty));

    var methodInfo = typeof(Enumerable)
                .GetMethods()
                .Single(x => x.Name == nameof(Enumerable.Contains) && 
                             x.IsGenericMethodDefinition && 
                             x.GetGenericArguments().Length == 1 && 
                             x.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(TProperty));
    var valuesExpr = Expression.Constant(values);

    exp = Expression.Call(null, methodInfo, valuesExpr, exp);

    var resultLambda = Expression.Lambda<Func<TData, bool>>(exp, arg);
    return resultLambda;
}

ToSQL() as QueryableExtension:

public static class QueryableExtensions
    {
        public static (string Sql, IEnumerable<SqlParameter> Parameters) ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
        {
            var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
            var relationalQueryContext = enumerator.Private<RelationalQueryContext>("_relationalQueryContext");
            var relationalCommandCache = enumerator.Private("_relationalCommandCache");
            var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
            var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");

            var sqlGenerator = factory.Create();
            var command = sqlGenerator.GetCommand(selectExpression);

            var sql = command.CommandText;
            var parameters = relationalQueryContext.ParameterValues.Select(kvp => new SqlParameter(kvp.Key, kvp.Value));
            return (sql, parameters);
        }

        private static object Private(this object obj, string privateField) =>
            obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);

        private static T Private<T>(this object obj, string privateField) =>
            (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
    }

Steps to reproduce

Some thing code like that:

var expression = CreateSetFilterExpression<SomeEntity, int>(AttributeNameInSomeEntity, List<int>{1,2,3});
SomeQueryable = SomeQueryable.Where(expression);
SomeQueryable.ToSQL();

Here is the Exception:

-->
Exception: System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List1[System.Int32]' to type 'System.Collections.Generic.IEnumerable1[System.Object]'.
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitIn(InExpression inExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
at Microsoft.EcoManager.Domain.Core.QueryableExtensions.ToSql[TEntity](IQueryable`1 query)

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: 3.1.100
Operating system: win10
IDE:Microsoft Visual Studio Enterprise 2019 Version 16.5.4

[ajcvickers]

@wadee It looks like there is something wrong with your ToSQL method. This method is using lots of internal code and looks very fragile. I would suggest that you stop using this, or debug it in detail to figure out where it is going wrong--it's not something we are going to debug. Note that EF Core 5.0, which is available in preview, include the ToQueryString method for doing this in a safe way--see #6482.

[wadee]

Thx.