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

@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?