在 DbContext 里 全局 记录 SQL 日志 :
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.Log = (query)=> Debug.Write(query);
}
获取 IQueryable<T>/Commond 产生的SQL:
using (var context = new BlogContext())
{
context.Database.Log = Console.Write;
var blog = context.Blogs.First(b => b.Title == "One Unicorn");
blog.Posts.First().Title = "Green Eggs and Ham";
blog.Posts.Add(new Post { Title = "I do not like them!" });
context.SaveChangesAsync().Wait();
}
WebConfig中配置记录全局ef产生的SQL 日志:
<interceptors>
<interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
<parameters>
<parameter value="C:\Temp\LogOutput.txt"/>
</parameters>
</interceptor>
</interceptors>
参考:
https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx
配置DbConfigration公约,不仅可以记录语句还可以 记录 执行效率
//配置公约
public class EFConfiguration : DbConfiguration
{
public EFConfiguration()
{
//AddInterceptor(new StringTrimmerInterceptor());
或者注册在Global.asax中的Application_Start
//DbInterception.Add(new EFIntercepterLogging());
}
}
#region 使 EntityFrameWork 自动 去除字符串 末尾 空格
/// <summary>
/// 设置 SQL 条件 "123 "与"123"比对的问题
/// 使 EntityFrameWork 自动 去除字符串 末尾 空格
/// SQL 默认去除 最末尾的空格 "123 " 等价于 "123"
/// </summary>
public class StringTrimmerInterceptor : IDbCommandTreeInterceptor
{
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
{
if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
{
var queryCommand = interceptionContext.Result as DbQueryCommandTree;
if (queryCommand != null)
{
var newQuery = queryCommand.Query.Accept(new StringTrimmerQueryVisitor());
interceptionContext.Result = new DbQueryCommandTree(
queryCommand.MetadataWorkspace,
queryCommand.DataSpace,
newQuery);
}
}
}
private class StringTrimmerQueryVisitor : DefaultExpressionVisitor
{
private static readonly string[] _typesToTrim = { "nvarchar", "varchar", "char", "nchar" };
public override DbExpression Visit(DbNewInstanceExpression expression)
{
var arguments = expression.Arguments.Select(a =>
{
var propertyArg = a as DbPropertyExpression;
if (propertyArg != null && _typesToTrim.Contains(propertyArg.Property.TypeUsage.EdmType.Name))
{
return EdmFunctions.Trim(a);
}
return a;
});
return DbExpressionBuilder.New(expression.ResultType, arguments);
}
}
}
#endregion
#region 记录 EntityFrameWork 生成的SQL 以及 性能
/// <summary>
/// 记录 EntityFrameWork 生成的SQL 以及 性能
/// 使用EntityFramework6.1的DbCommandInterceptor拦截生成的SQL语句
/// </summary>
public class EFIntercepterLogging : DbCommandInterceptor
{
private readonly Stopwatch _stopwatch = new Stopwatch();
public override void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
base.ScalarExecuting(command, interceptionContext);
_stopwatch.Restart();
}
public override void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
_stopwatch.Stop();
if (interceptionContext.Exception != null)
{
Trace.TraceError("Exception:{1} \r\n --> Error executing command: {0}", command.CommandText, interceptionContext.Exception.ToString());
}
else
{
Trace.TraceInformation("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", _stopwatch.ElapsedMilliseconds, command.CommandText);
}
base.ScalarExecuted(command, interceptionContext);
}
public override void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
base.NonQueryExecuting(command, interceptionContext);
_stopwatch.Restart();
}
public override void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
_stopwatch.Stop();
if (interceptionContext.Exception != null)
{
Trace.TraceError("Exception:{1} \r\n --> Error executing command:\r\n {0}", command.CommandText, interceptionContext.Exception.ToString());
}
else
{
Trace.TraceInformation("\r\n执行时间:{0} 毫秒\r\n-->NonQueryExecuted.Command:\r\n{1}", _stopwatch.ElapsedMilliseconds, command.CommandText);
}
base.NonQueryExecuted(command, interceptionContext);
}
public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
{
base.ReaderExecuting(command, interceptionContext);
_stopwatch.Restart();
}
public override void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
{
_stopwatch.Stop();
if (interceptionContext.Exception != null)
{
Trace.TraceError("Exception:{1} \r\n --> Error executing command:\r\n {0}", command.CommandText, interceptionContext.Exception.ToString());
}
else
{
Trace.TraceInformation("\r\n执行时间:{0} 毫秒 \r\n -->ReaderExecuted.Command:\r\n{1}", _stopwatch.ElapsedMilliseconds, command.CommandText);
}
base.ReaderExecuted(command, interceptionContext);
}
}
#endregion