Get SQL Query in Entity Framework Core

View the generated SQL query by entity framework is important. The translated SQL query may not what you expected. Sometimes it leads to significant performance issue.

Print the generated query for an IQueryable<T> in EF Core is different with EF. Finally I found a worked solution which is listed below.

The following code is tested in MySql.Data.EntityFrameworkCore 8.0.16. Just put the following class into your project:

public static class QueryableExtensions
{
    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 FieldInfo QueryModelGeneratorField = typeof(QueryCompiler).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryModelGenerator");
    private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
    private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

    public static string ToSql<TEntity>(this IQueryable<TEntity> query)
    {
        var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
        var queryModelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
        var queryModel = queryModelGenerator.ParseQuery(query.Expression);
        var database = DataBaseField.GetValue(queryCompiler);
        var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
        var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
        var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        var sql = modelVisitor.Queries.First().ToString();

        return sql;
    }
}

The usage is straightforward, just append a .ToSql() after your IQueryable<T>:

IQueryable<PriceTable> query = ...;
string generatedQuery = query.ToSql();
Console.WriteLine(generatedQuery);

Then you can get the query to check if it's expected:

SELECT `x`.`Id`, `x`.`Price`
FROM `XXX`.`PriceTable`
WHERE `x`.`Id` = 'xxx'
ORDER BY `x`.`CreateTime` DESC

Tips: the approach is easy to use with little dependency. I'm not an EF expert, but consider the "crazy" reflection and type casting, DO NOT use it in production environment, ONLY for debug purpose.

Reference: # Get SQL code from an Entity Framework Core IQueryable