Resolve the SQL being executed behind the scenes in Entity Framework Core

IQueryableExtensions

public static class IQueryableExtensions
{
    private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

    private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryCompiler");

    private static readonly PropertyInfo NodeTypeProviderField = QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");

    private static readonly MethodInfo CreateQueryParserMethod = QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");

    private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

    private static readonly FieldInfo QueryCompilationContextFactoryField = typeof(Database).GetTypeInfo().DeclaredFields.Single(x => x.Name == "_queryCompilationContextFactory");

    public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
    {
        if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
        {
            throw new ArgumentException("Invalid query");
        }

        var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);
        var nodeTypeProvider = (INodeTypeProvider)NodeTypeProviderField.GetValue(queryCompiler);
        var parser = (IQueryParser)CreateQueryParserMethod.Invoke(queryCompiler, new object[] { nodeTypeProvider });
        var queryModel = parser.GetParsedQuery(query.Expression);
        var database = DataBaseField.GetValue(queryCompiler);
        var queryCompilationContextFactory = (IQueryCompilationContextFactory)QueryCompilationContextFactoryField.GetValue(database);
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        var sql = modelVisitor.Queries.First().ToString();

        return sql;
    }
}

@chrsas

@RosiOli It does not usable in EF 3.1 with params. https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3059688
My unit test:

[Fact]
public void ToSql_Parameters_HasParameter() {
    using var dbContext = CreateDbContext();
    var names = new[] {"John", "Bob"};
    // Act
    var query = dbContext.Students.Where(s => names.Contains(s.Name));
    var (result, paramList) = query.ToSqlWithParams();
    // Assert
    _output.WriteLine(result);
    paramList.Count.ShouldBe(2);
    result.ShouldContain("WHERE [s].[Name] In");
}

I tested it and this works:

public static readonly string[] Names = { "John", "Bob" };

[Fact]
public void ToSql_Parameters_HasParameter()
{
    using var dbContext = CreateDbContext();

    // Act
    var query = dbContext.Students.Where(s => Names.Contains(s.Name));
    var result = query.ToQueryString();
    // Assert
    Console.WriteLine(result);

    var sql = RemoveWhitespace(result);

    var pattern = @$".*.*SELECT.* FROM \[dbo\].\[Students\] AS \[[a-z0-9]+\] WHERE \[[a-z0-9]+\].\[Name\] IN \('{Names[0]}', '{Names[1]}'\)";
    var regex = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline);
    var match = regex.Match(sql);

    match.Success.Should().BeTrue();
}

private static string RemoveWhitespace(string sqlWithNewLines)
{
    var noNewLines = sqlWithNewLines.Replace(Environment.NewLine, " ");
    return Regex.Replace(noNewLines, @"\s +", " ", RegexOptions.Compiled);
}

I use my own version of the ToSql (renamed it ToQueryString to match EF Core 5 upcoming support for this)

I try to construct a dynamic expression work with filter in set such as "x => set.Contains(x.attr)" and get the generated SQL using below "ToSQL" method in EF Core 3.1.But it throw an InvalidCastException just 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)

Here is my code constructing filter set dynamic expression:

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;

It seems related to the VisitIn method of QuerySqlGenerator in EF core:
https://github.com/dotnet/efcore/blob/2e8ef3516d2bed2f934eea6e2cb92f7a9ff40ab3/src/EFCore.Relational/Query/QuerySqlGenerator.cs#L624
For example, when I try to construct the filter set dynamic expression using CreateSetFilterExpression<SomeEntity, int>(SomePropertyName, values), what the values' type is List<int>. it will throw InvalidCastException when executing to the line "sqlGenerator.GetCommand" in ToSQL():

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)
...

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

Is that anyone has encountered the same problem?
Please help me if there is any clue about this problem?

@RosiOli I updated your code to work with EFCore 3.1

public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
    var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
    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);

    string sql = command.CommandText;
    return sql;
}

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);

@wadee I'm having the same issue. Did you ever figure out a way around this problem?

Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

@wadee I'm having the same issue. Did you ever figure out a way around this problem?

Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

https://github.com/borisdj/EFCore.BulkExtensions/blob/master/EFCore.BulkExtensions/IQueryableExtensions.cs
I use ToParametrizedSql function from EFCore.BulkExtensions, it's work fine.

@wadee I'm having the same issue. Did you ever figure out a way around this problem?
Thanks for finding the exact spot where this blows up - I don't see an obvious way around it, but at least I know what the problem is.

https://github.com/borisdj/EFCore.BulkExtensions/blob/master/EFCore.BulkExtensions/IQueryableExtensions.cs
I use ToParametrizedSql function from EFCore.BulkExtensions, it's work fine.

it's work fine too. thanks.

@rionmonster, what are the using statements for this code ? I am copy pasting that to a .NET Standard 2.0 and it is missing INodeTypeProvider, IQueryParser, and RelationalQueryModelVisitor

Here are my usings. They are incomplete for .NET Standard 2.0

using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;

Update 1.
This one below works for .NET Standard 2.0 . Thanks to this stackoverflow

using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Storage;

namespace MyNamespace
{
    public static class EfUtility
    {
        public static string ToSql<TEntity>(IQueryable<TEntity> query) where TEntity : class
        {
            using (IEnumerator<TEntity> enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator())
            {
                object relationalCommandCache = enumerator.Private("_relationalCommandCache");
                SelectExpression selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
                IQuerySqlGeneratorFactory factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");

                QuerySqlGenerator sqlGenerator = factory.Create();
                IRelationalCommand command = sqlGenerator.GetCommand(selectExpression);

                string sql = command.CommandText;
                return sql;
            }
        }

        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);
    }
}

In EF Core 5+ we have ToQueryString which easily gives us the sql query. But we still don't have access to parameters.

Does anyone have an approach that includes parameters?