如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
当前回答
我正在做集成测试,需要在实体框架核心2.1中调试生成的SQL语句,所以我使用DebugLoggerProvider或ConsoleLoggerProvider,如下所示:
[Fact]
public async Task MyAwesomeTest
{
//setup log to debug sql queries
var loggerFactory = new LoggerFactory();
loggerFactory.AddProvider(new DebugLoggerProvider());
loggerFactory.AddProvider(new ConsoleLoggerProvider(new ConsoleLoggerSettings()));
var builder = new DbContextOptionsBuilder<DbContext>();
builder
.UseSqlServer("my connection string") //"Server=.;Initial Catalog=TestDb;Integrated Security=True"
.UseLoggerFactory(loggerFactory);
var dbContext = new DbContext(builder.Options);
........
下面是Visual Studio控制台的输出示例:
其他回答
使用实体框架核心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
}
如果您还想拥有参数值(不仅是@p_linq_0,而且还有它们的值),您可以使用IDbCommandInterceptor并向ReaderExecuted方法添加一些日志记录。
IQueryable query = from x in appEntities
where x.id = 32
select x;
var queryString = query.ToString();
将返回sql查询。使用EntityFramework的数据上下文进行工作
如果您正在使用DbContext,您可以执行以下操作来获取SQL:
var result = from i in myContext.appEntities
select new Model
{
field = i.stuff,
};
var sql = result.ToString();
在EF 4.1中可以执行以下操作:
var result = from x in appEntities
where x.id = 32
select x;
System.Diagnostics.Trace.WriteLine(result .ToString());
这将为您提供生成的SQL。