Get SQL code from an Entity Framework Core IQueryable

entity-framework-core

Question

I am using Entity Framework Core and I need to see which SQL code is being generated. In previous versions of Entity Framework I could use the following:

string sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

Where query is an IQueryable object ... But ToTraceString is not available in EF Core.

How can I do something similar in EF Core?

1
68
12/5/2019 10:21:33 AM

Popular Answer

This answer is for EF Core 2.1. For EF Core 3.0 and 3.1 see the @Thom Kiesewetter's answer

Since EF 7 is renamed to Entity Framework Core I will summarize you the options for EF Core.

There are 3 approaches for logging SQL statements from IQueryable<>:

  • Using Built-in or Custom Logging. Logging the executing query using your logger of choice or the built-in Logger in .NET Core as mentioned in this tutorial.
  • Using a Profiler. Using an SQL Profiler like MiniProfiler to monitor the executing query.
  • Using Crazy Reflection Code. You can implement some custom reflection code similar to the older approach to perform the same basic concept.

Here is the crazy reflection code (extension method):

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 FieldInfo QueryModelGeneratorField = QueryCompilerTypeInfo.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) where TEntity : class
    {
        var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
        var modelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
        var queryModel = modelGenerator.ParseQuery(query.Expression);
        var database = (IDatabase)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;
    }
}

After adding this extension method to your code, you can use the method as follows:

// Build a query using Entity Framework
var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42);  
// Get the generated SQL
var sql = query.ToSql();  

Referral: http://rion.io/2016/10/19/accessing-entity-framework-core-queries-behind-the-scenes-in-asp-net-core/ and https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a

68
2/7/2020 12:47:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow