Damir's Corner.

Get SQL for EF Core Queries

There's no built-in solution in EF Core for getting the SQL query that is going to be sent to the database. I could find an implementation by Nick Craver which worked fine with EF Core 2.0.0, but fails with the current version of EF Core (2.1.4):

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 PropertyInfo DatabaseDependenciesField =
        typeof(Database).GetTypeInfo().DeclaredProperties
            .Single(x => x.Name == "Dependencies");

    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 =
            ((DatabaseDependencies)DatabaseDependenciesField
                .GetValue(database)).QueryCompilationContextFactory;
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor =
            (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        return modelVisitor.Queries.First().ToString();
    }
}

The code failed at run time with the following error:

System.TypeInitializationException : The type initializer for 'EfGetSql.IQueryableExtensions' threw an exception.

----> System.InvalidOperationException : Sequence contains no matching element

With the debugger it was easy to identify the offending line of code:

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

Considering that is is strongly based on reflection, that's not really surprising. Since I really needed it for the latest version, I decided to put the effort in to make it work. I documented my thought process just in case I need to repeat the exercise with a future version.

I find it difficult to read reflection code, therefore I first wrote it out as pseudo C# code without reflection (lines using reflection to access non-public members are marked with comments):

QueryCompiler queryCompiler = query.Provider._queryCompiler; // Reflection
INodeTypeProvider nodeTypeProvider = queryCompiler.NodeTypeProvider; // Reflection
IQueryParser parser = queryCompiler.CreateQueryParser(nodeTypeProvider); // Reflection
QueryModel queryModel = parser.GetParsedQuery(query.Expression);
Database database = queryCompiler._database; // Reflection
IQueryCompilationContextFactory queryCompilationContextFactory =
    database.Dependencies.QueryCompilationContextFactory; // Reflection
QueryCompilationContext queryCompilationContext =
    queryCompilationContextFactory.Create(false);
RelationalQueryModelVisitor modelVisitor =
    queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
return modelVisitor.Queries.First().ToString();

Based on the error, I could conclude that the QueryCompiler class doesn't have the NodeTypeProvider any more. Looking at the code, I could confirm that.

Using GitHub code search I determined that the INodeTypeProvider interface is now only referenced in the QueryModelGenerator class. To get to it, I needed to find a way to access an instance of the QueryModelGenerator class. Fortunately, I could find it in the QueryCompiler class which I already now how to access.

It was time to replace the offending line with the following three:

private static readonly TypeInfo QueryModelGeneratorTypeInfo =
    typeof(QueryModelGenerator).GetTypeInfo();
private static readonly FieldInfo QueryModelGeneratorField =
    QueryCompilerTypeInfo.GetTypeInfo().DeclaredFields
        .First(x => x.Name == "_queryModelGenerator");
private static readonly FieldInfo NodeTypeProviderField =
    QueryModelGeneratorTypeInfo.DeclaredFields
        .Single(x => x.Name == "_nodeTypeProvider");

Of course, the following line accessing the property also had to be changed:

var nodeTypeProvider = (INodeTypeProvider)NodeTypeProviderField.GetValue(queryCompiler);

Here's the replacement:

var queryModelGenerator =
    (IQueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
var nodeTypeProvider =
    (INodeTypeProvider)NodeTypeProviderField.GetValue(queryModelGenerator);

I tried running the code again and it still failed with the same error. The offending line was different, though:

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

Ok, the CreateQueryParser moved. It's now in the QueryModelGenerator class instead of in the QueryCompiler class. Here's the fix for the problematic line of code:

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

The following code used to invoke this method:

var parser = (IQueryParser)CreateQueryParserMethod.Invoke(
    queryCompiler, new object[] { nodeTypeProvider });

The first argument needs to be changed:

var parser = (IQueryParser)CreateQueryParserMethod.Invoke(
    queryModelGenerator, new object[] { nodeTypeProvider });

Time to run the code again... It worked!

Although there seem to be a lot of changes, my pseudo C# code isn't that much different (changed lines are marked with comments):

QueryCompiler queryCompiler = query.Provider._queryCompiler; // Reflection
IQueryModelGenerator queryModelGenerator =
    queryCompiler._queryModelGenerator; // Reflection (added)
INodeTypeProvider nodeTypeProvider =
    queryModelGenerator._nodeTypeProvider; // Reflection (changed)
IQueryParser parser =
    queryModelGenerator.CreateQueryParser(nodeTypeProvider); // Reflection (changed)
QueryModel queryModel = parser.GetParsedQuery(query.Expression);
Database database = queryCompiler._database; // Reflection
IQueryCompilationContextFactory queryCompilationContextFactory =
    database.Dependencies.QueryCompilationContextFactory; // Reflection
QueryCompilationContext queryCompilationContext =
    queryCompilationContextFactory.Create(false);
RelationalQueryModelVisitor modelVisitor =
    queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
return modelVisitor.Queries.First().ToString();

I only needed to compensate for some internal refactoring. It's just that the reflection code is verbose and difficult to maintain. Hopefully, I won't have to repeat the process too often.

For the sake of completeness, here's the final implementation of the ToSql method:

public static class IQueryableExtensions
{
    private static readonly TypeInfo QueryCompilerTypeInfo =
        typeof(QueryCompiler).GetTypeInfo();
    private static readonly TypeInfo QueryModelGeneratorTypeInfo =
        typeof(QueryModelGenerator).GetTypeInfo();
    private static readonly FieldInfo QueryCompilerField =
        typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields
            .First(x => x.Name == "_queryCompiler");
    private static readonly FieldInfo QueryModelGeneratorField =
        QueryCompilerTypeInfo.GetTypeInfo().DeclaredFields
            .First(x => x.Name == "_queryModelGenerator");
    private static readonly FieldInfo NodeTypeProviderField =
        QueryModelGeneratorTypeInfo.DeclaredFields
            .Single(x => x.Name == "_nodeTypeProvider");
    private static readonly MethodInfo CreateQueryParserMethod =
        QueryModelGeneratorTypeInfo.DeclaredMethods
            .First(x => x.Name == "CreateQueryParser");
    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) 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 queryModelGenerator =
            (IQueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
        var nodeTypeProvider =
            (INodeTypeProvider)NodeTypeProviderField.GetValue(queryModelGenerator);
        var parser = (IQueryParser)CreateQueryParserMethod.Invoke(
            queryModelGenerator, new object[] { nodeTypeProvider });
        var queryModel = parser.GetParsedQuery(query.Expression);
        var database = DataBaseField.GetValue(queryCompiler);
        var queryCompilationContextFactory =
            ((DatabaseDependencies)DatabaseDependenciesField
                .GetValue(database)).QueryCompilationContextFactory;
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor =
            (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        return modelVisitor.Queries.First().ToString();
    }
}