记录：使用Entity Framework Core 获取SQL语句

使用Entity Framework Core IQueryable操作数据库时，我们有时候想获取一下SQL语句

1. EF Core 5和EF Core 6

   • var ctx=new EFContext();
     ctx.Artists
         .Where(a => a.Name == "Pink Floyd")
         .ToQueryString()
     

   • 参考：https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.entityframeworkqueryableextensions.toquerystring?view=efcore-5.0#Microsoft_EntityFrameworkCore_EntityFrameworkQueryableExtensions_ToQueryString_System_Linq_IQueryable_

   • 参考：https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew

2. EF Core 3.1

   • 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("_relationalCommandCache");
         var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
         var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
     
         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);
     

3. EF Core 3.0

   •  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("_selectExpression", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _selectExpression on type {enumeratorType.Name}");
                 var sqlGeneratorFieldInfo = enumeratorType.GetField("_querySqlGeneratorFactory", BindingFlags.NonPublic | BindingFlags.Instance) ?? throw new InvalidOperationException($"cannot find field _querySqlGeneratorFactory on type {enumeratorType.Name}");
                 var selectExpression = selectFieldInfo.GetValue(enumerator) as SelectExpression ?? throw new InvalidOperationException($"could not get SelectExpression");
                 var factory = sqlGeneratorFieldInfo.GetValue(enumerator) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($"could not get IQuerySqlGeneratorFactory");
                 var sqlGenerator = factory.Create();
                 var command = sqlGenerator.GetCommand(selectExpression);
                 var sql = command.CommandText;
                 return sql;
             }
     

4. EF Core 2.1

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

参考来源：https://stackoverflow.com/questions/37527783/get-sql-code-from-an-entity-framework-core-iqueryablet