How to Retrieve SQL Code from an Entity Framework Core IQueryable?

Obtaining SQL Code from an Entity Framework Core IQueryable

The Entity Framework Core's ToTraceString method, which provided developers with the SQL code generated by an IQueryable object, is not available in the newer versions. This article explores alternative methods for retrieving SQL code in Entity Framework Core, depending on the version being used.

EF Core 5/6 (Net 5/6)

In EF Core 5 and 6 (Net 5 and 6), the ToQueryString method is available to obtain the SQL code for a query:

var query = _context.Widgets.Where(w => w.IsReal && w.Id == 42);  
var sql = query.ToQueryString();

Core 2.1.2

For older versions of Net Core, a custom extension method can be used:

using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.Sql;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

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;
        }
    }

EF Core 3.0

In EF Core 3.0, the following extension method can be used:

        public static string ToSql<TEntity>(this IQueryable<TEntity> query)
        {
            using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
            var enumeratorType = enumerator.GetType();
            var selectFieldInfo = enumeratorType.GetField(&quot;_selectExpression&quot;, BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($&quot;cannot find field _selectExpression on type {enumeratorType.Name}&quot;);
            var sqlGeneratorFieldInfo = enumeratorType.GetField(&quot;_querySqlGeneratorFactory&quot;, BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($&quot;cannot find field _querySqlGeneratorFactory on type {enumeratorType.Name}&quot;);
            var selectExpression = selectFieldInfo.GetValue(enumerator) as SelectExpression ?? throw new InvalidOperationException($&quot;could not get SelectExpression&quot;);
            var factory = sqlGeneratorFieldInfo.GetValue(enumerator) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($&quot;could not get IQuerySqlGeneratorFactory&quot;);
            var sqlGenerator = factory.Create();
            var command = sqlGenerator.GetCommand(selectExpression);
            var sql = command.CommandText;
            return sql;
        }

EF Core 3.1

Finally, in EF Core 3.1, SQL code can be obtained using the following extension method:

using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Query;

public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
    using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
    var relationalCommandCache = enumerator.Private(&quot;_relationalCommandCache&quot;);
    var selectExpression = relationalCommandCache.Private<SelectExpression>(&quot;_selectExpression&quot;);
    var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>(&quot;_querySqlGeneratorFactory&quot;);

    var sqlGenerator = factory.Create();
    var command = sqlGenerator.GetCommand(selectExpression);

    string sql = command.CommandText;
    return sql;
}

private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);

The underlying issue related to exposing ToTraceString in Entity Framework Core has been addressed by the EF team and is expected to be resolved in a future release.

The above is the detailed content of How to Retrieve SQL Code from an Entity Framework Core IQueryable?. For more information, please follow other related articles on the PHP Chinese website!