从实体框架核心IQueryable< T>获取SQL代码。 [英] Get SQL code from an Entity Framework Core IQueryable<T>

问题描述

我正在使用Entity Framework Core，需要查看正在生成的SQL代码。在先前版本的Entity Framework中，我可以使用以下命令：

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

查询是一个IQueryable对象，但ToTraceString在EF Core中不可用。

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

如何在EF Core中做类似的事情？

How can I do something similar in EF Core?

推荐答案

此答案适用于EF Core 2.1。对于EF Core 3.0和3.1，请参见@Thom Kiesewetter的答案

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

对于EF Core 5，将内置方法 ToQueryString（）在 IQueryable<>

For EF Core 5 there will be built-in method ToQueryString() used on IQueryable<>

由于EF 7重命名为Entity Framework Core，所以我将进行总结您可以使用EF Core。

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

从 IQueryable<> ：

• 使用内置或自定义日志记录。如本教程。


• 使用 Profiler 。使用类似 MiniProfiler 的SQL事件探查器来监视正在执行的查询。


• 使用疯狂的反思代码。您可以实现一些类似于旧方法的自定义反射代码，以执行相同的基本概念。

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

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

这篇关于从实体框架核心IQueryable< T>获取SQL代码。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！