如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
当前回答
我刚刚做了这个:
IQueryable<Product> query = EntitySet.Where(p => p.Id == id);
Debug.WriteLine(query);
结果显示在Output:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name],
[Extent2].[Id] AS [Id1],
[Extent2].[FileName] AS [FileName],
FROM [dbo].[Products] AS [Extent1]
INNER JOIN [dbo].[PersistedFiles] AS [Extent2] ON [Extent1].[PersistedFileId] = [Extent2].[Id]
WHERE [Extent1].[Id] = @p__linq__0
其他回答
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;
}
}
}
在不更改代码的情况下,使查询始终方便 将它添加到你的DbContext中,并在visual studio的输出窗口中检查它。
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.Log = (query)=> Debug.Write(query);
}
类似于@Matt Nibecker的答案,但有了这个答案,你不必每次需要查询时都将它添加到当前代码中。
使用实体框架核心3.x进行日志记录
实体框架核心通过日志系统发出SQL。只有一些小技巧。您必须指定一个ILoggerFactory和一个筛选器。下面是本文中的一个例子
创建工厂:
var loggerFactory = LoggerFactory.Create(builder =>
{
builder
.AddConsole((options) => { })
.AddFilter((category, level) =>
category == DbLoggerCategory.Database.Command.Name
&& level == LogLevel.Information);
});
告诉DbContext在onconfigure方法中使用工厂:
optionsBuilder.UseLoggerFactory(_loggerFactory);
从这里,您可以获得更复杂的信息,并与Log方法挂钩以提取有关执行SQL的详细信息。有关完整讨论,请参阅文章。
public class EntityFrameworkSqlLogger : ILogger
{
#region Fields
Action<EntityFrameworkSqlLogMessage> _logMessage;
#endregion
#region Constructor
public EntityFrameworkSqlLogger(Action<EntityFrameworkSqlLogMessage> logMessage)
{
_logMessage = logMessage;
}
#endregion
#region Implementation
public IDisposable BeginScope<TState>(TState state)
{
return default;
}
public bool IsEnabled(LogLevel logLevel)
{
return true;
}
public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
{
if (eventId.Id != 20101)
{
//Filter messages that aren't relevant.
//There may be other types of messages that are relevant for other database platforms...
return;
}
if (state is IReadOnlyList<KeyValuePair<string, object>> keyValuePairList)
{
var entityFrameworkSqlLogMessage = new EntityFrameworkSqlLogMessage
(
eventId,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "commandText").Value,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "parameters").Value,
(CommandType)keyValuePairList.FirstOrDefault(k => k.Key == "commandType").Value,
(int)keyValuePairList.FirstOrDefault(k => k.Key == "commandTimeout").Value,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "elapsed").Value
);
_logMessage(entityFrameworkSqlLogMessage);
}
}
#endregion
}
好吧,目前我正在使用Express分析器,缺点是它只适用于MS SQL Server。你可以在这里找到这个工具:https://expressprofiler.codeplex.com/
对我来说,使用EF6和Visual Studio 2015,我在即时窗口中输入查询,它给了我生成的SQL语句