如何從 Entity Framework Core IQueryable 檢索 SQL 程式碼？

從Entity Framework Core IQueryable 取得SQL 程式碼

Entity Framework Core 的ToTraceString 方法，該方法為開發人員提供了由IQueryable 物件產生的SQL 程式碼，在較好新的版本中不可用。本文探討了在 Entity Framework Core 中檢索 SQL 程式碼的替代方法，具體取決於所使用的版本。

EF Core 5/6 (Net 5/6)

在EF Core 5 和6 中（Net 5 和6），ToQueryString 方法可用來取得查詢的SQL 程式碼：

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

Core 2.1.2

對於舊版的Net Core，可使用自訂擴充功能：

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

在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(&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

最後，在EF Core 3.1 中，可以使用以下擴充方法取得SQL程式碼：

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

與在 Entity Framework Core 中公開 ToTraceString 相關的底層問題已由EF 團隊預計將在未來版本中解決。

以上是如何從 Entity Framework Core IQueryable 檢索 SQL 程式碼？的詳細內容。更多資訊請關注PHP中文網其他相關文章！