Implementing Missing Features in Entity Framework Core – Part 5: Getting the SQL for a Query

This will be the fifth post in a series of posts about bringing the features that were present in Entity Framework pre-Core into EF Core. The others are:

  • Part 1: Introduction, Find, Getting an Entity’s Id Programmatically, Reload, Local, Evict

  • Part 2: Explicit Loading

  • Part 3: Validations

  • Part 4: Conventions

This time I’m going to talk about something that is often requested: how can I get the SQL string for a LINQ query? If you remember, in the pre-Core days you had to do some reflection in order to get the underlying ObjectQuery and then call its ToTraceString method. Now, things are very different, although I may say, still rather tricky!

So, this is all we need:

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 DependenciesProperty = typeof(Database).GetTypeInfo().GetDeclaredProperty("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 dependencies = (DatabaseDependencies)DependenciesProperty.GetValue(database);
var queryCompilationContextFactory = dependencies.QueryCompilationContextFactory;
var queryCompilationContext = queryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();

return sql;
}

You can see that it needs some reflection, meaning, things *may* break on a future version. I cached all of the fields to make the access faster in subsequent calls. For the time being, it works perfectly:

var sql1 = ctx.Blogs.ToSql();

var sql2 = ctx
.Blogs
.Where(b => b.CreationDate.Year == 2016)
.ToSql();

Hope this is useful to you! Winking smile

Published by

Ricardo Peres

Team Leader at Dixons Carphone. Microsoft MVP.

4 thoughts on “Implementing Missing Features in Entity Framework Core – Part 5: Getting the SQL for a Query”

  1. Getting an error on this line:

    var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);

    System.InvalidOperationException : Sequence contains no matching element
    at DataAccessLayer.EfStructures.Extensions.IQueryableExtensionsForGeneratedSql.ToSql[TEntity](IQueryable`1 query) in….

    .netcore 2.0

    Any idea why that could be?

    Thanks

    Steve

  2. Hi Ricardo- I am getting this error “System.TypeInitializationException: ‘The type initializer for ‘DataAccessLayer.EfStructures.Extensions.IQueryableExtensionsForGeneratedSql’ threw an exception.’
    InvalidOperationException: Sequence contains no matching element”
    with .NETCore 2.1 and EF Core 2.2.4 (same as Steve mentioned above) in this line:
    var queryCompiler = (IQueryCompiler)QueryCompilerField.GetValue(query.Provider);

    Do you have a fix for this error? Thanks.

Leave a Reply to Ricardo Peres Cancel reply

Your email address will not be published. Required fields are marked *