如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
当前回答
IQueryable query = from x in appEntities
where x.id = 32
select x;
var queryString = query.ToString();
将返回sql查询。使用EntityFramework的数据上下文进行工作
其他回答
在不更改代码的情况下,使查询始终方便 将它添加到你的DbContext中,并在visual studio的输出窗口中检查它。
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.Log = (query)=> Debug.Write(query);
}
类似于@Matt Nibecker的答案,但有了这个答案,你不必每次需要查询时都将它添加到当前代码中。
从EF6.1开始,您可以使用拦截器来注册数据库记录器。 请参见“拦截器”和“记录数据库操作”章节
<configuration>
<entityFramework>
<interceptors>
<interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
<parameters>
<parameter value="C:\Temp\LogOutput.txt"/>
<parameter value="true" type="System.Boolean"/>
</parameters>
</interceptor>
</interceptors>
</entityFramework>
</configuration>
您可以执行以下操作:
IQueryable query = from x in appEntities
where x.id == 32
select x;
var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();
或EF6:
var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
.ToTraceString();
或EF6.3+:
var sql = ((dynamic)flooringStoresProducts).Sql;
这将为您提供生成的SQL。
Necromancing。 当搜索任何。net框架的解决方案时,这个页面是第一个搜索结果,所以这里作为一个公共服务,它是如何在EntityFrameworkCore(用于。net Core 1和2)中完成的:
var someQuery = (
from projects in _context.projects
join issues in _context.issues on projects.Id equals issues.ProjectId into tmpMapp
from issues in tmpMapp.DefaultIfEmpty()
select issues
) //.ToList()
;
// string sql = someQuery.ToString();
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions.ToSql(someQuery);
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions1.ToSql(someQuery);
// using Microsoft.EntityFrameworkCore;
string sql = someQuery.ToSql();
System.Console.WriteLine(sql);
然后这些扩展方法(IQueryableExtensions1用于。net Core 1.0, IQueryableExtensions用于。net Core 2.0):
using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Remotion.Linq.Parsing.Structure;
namespace Microsoft.EntityFrameworkCore
{
// https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework
// http://rion.io/2016/10/19/accessing-entity-framework-core-queries-behind-the-scenes-in-asp-net-core/
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 PropertyInfo NodeTypeProviderField =
QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");
private static readonly MethodInfo CreateQueryParserMethod =
QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");
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
{
if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
{
throw new ArgumentException("Invalid query");
}
var queryCompiler = (QueryCompiler) QueryCompilerField.GetValue(query.Provider);
var nodeTypeProvider = (INodeTypeProvider) NodeTypeProviderField.GetValue(queryCompiler);
var parser = (IQueryParser) CreateQueryParserMethod.Invoke(queryCompiler, new object[] {nodeTypeProvider});
var queryModel = parser.GetParsedQuery(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;
}
}
public class IQueryableExtensions1
{
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 PropertyInfo NodeTypeProviderField =
QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");
private static readonly MethodInfo CreateQueryParserMethod =
QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");
private static readonly FieldInfo DataBaseField =
QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
private static readonly FieldInfo QueryCompilationContextFactoryField = typeof(Database).GetTypeInfo()
.DeclaredFields.Single(x => x.Name == "_queryCompilationContextFactory");
public static string ToSql<TEntity>(IQueryable<TEntity> query) where TEntity : class
{
if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
{
throw new ArgumentException("Invalid query");
}
var queryCompiler = (IQueryCompiler) QueryCompilerField.GetValue(query.Provider);
var nodeTypeProvider = (INodeTypeProvider) NodeTypeProviderField.GetValue(queryCompiler);
var parser =
(IQueryParser) CreateQueryParserMethod.Invoke(queryCompiler, new object[] {nodeTypeProvider});
var queryModel = parser.GetParsedQuery(query.Expression);
var database = DataBaseField.GetValue(queryCompiler);
var queryCompilationContextFactory =
(IQueryCompilationContextFactory) QueryCompilationContextFactoryField.GetValue(database);
var queryCompilationContext = queryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
}
}
在我的EF 6+的情况下,而不是在立即窗口中使用这个来查找查询字符串:
var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query).ToTraceString();
我最终不得不使用这个来获得生成的SQL命令:
var sql = ((System.Data.Entity.Infrastructure.DbQuery<<>f__AnonymousType3<string,string,string,short,string>>)query).ToString();
当然,您的匿名类型签名可能有所不同。
HTH.