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

Updated for EF Core 2.2

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:

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 serviceProvider = (query as InternalDbSet<TEntity>).GetInfrastructure();
    IQueryCompilationContextFactory queryCompilationContextFactory = serviceProvider.GetService<IQueryCompilationContextFactory>();
    QueryCompilationContext queryCompilationContext = queryCompilationContextFactory.Create(false);
    RelationalQueryModelVisitor modelVisitor = queryCompilationContext.CreateQueryModelVisitor() as RelationalQueryModelVisitor;
    IQueryModelGenerator queryModelGenerator = serviceProvider.GetService<IQueryModelGenerator>();
    QueryModel queryModel = queryModelGenerator.ParseQuery(query.Expression);
 
    modelVisitor.CreateQueryExecutor<TEntity>(queryModel);

    return modelVisitor.Queries.First().ToString();
}

This example, unlike previous versions, does not use reflectoon! 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

                             

15 Comments

  • where is the "QueryCompilerField" package? Could not find in the EntityFramework's Github page, packagesearch.azurewebsites.net or in system.reflection namespace.

  • Erman: What? It's a field in the code I showed!

  • Surely just turn on query logging at the database-server-level and don't worry about application-level handling such details

  • `QueryCompilerTypeInfo` is unknown here.
    Is it?
    private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

  • Hi, Saeid!
    Precisely, thanks! Will update, sorry about that.

  • Thanks. Sometimes we need to `select(x=>x.Id)`, in this case `TEntity` won't be a class. It's a number and `where TEntity : class` should be removed.

  • Saeid: thanks, but, not, it shouldn't! :-)

  • You can't call ToSql on this query (it's result's type is not a class):
    var query = context.BlogPosts.AsNoTracking()
    .Select(x=>x.Id)
    .Skip(0)
    .Take(10);

  • Saeid: true, but that has absolutely nothing to do with the TEntity : class restriction.

  • only `InternalDbSet<TEntity>` needs the `where TEntity : class` restriction and not the rest of the code.

  • Saeid: I'm getting pretty tired of this. True, it is not required, but it has nothing to do with your problem. And, yes, we should have this so that we are coherent with DbSet<T> and the Set<T> method.

  • Excellent post Ricardo. Would you be able to offer guidance on how you would show SQL when Include methods are used?

    var sql3 = ctx.Blogs.Include(b => b.Posts).Where(b => b.CreationDate.Year == 2016).ToSql();

    This currently only shows the SQL for the Blogs query. However, with the include statement there is also a subsequent Posts query which is not shown.

    I was hopeful that is was as simple as changing:
    var sql = modelVisitor.Queries.First().ToString();

    And instead using the following loop:
    foreach (var item in modelVisitor.Queries)

    But this still only produced the single select statement. Any ideas?

  • Just an observation ... leaving in the throw when TEntity isn't in any DbSet prevents generating queries from projections.

  • First to say this is great piece of code.
    Secondly now that EF Core 3.0 is released, how can implementation of .ToSql() method be adapted for it, since it has breaking changes: missing classes are 'QueryModelGenerator' and 'RelationalQueryModelVisitor'.
    More info: github.com/aspnet/EntityFrameworkCore/issues/18029

  • Hi, borisdj!
    I will try to adapt it as soon as I have the time, thanks for spotting it!

Add a Comment

As it will appear on the website

Not displayed

Your website