Entity Framework在使用时,很多时间操纵的是Model,并没有写sql语句,有时候为了调试或优化等,又需要追踪Entity framework自动生成的sql(最好还能记录起来,方便出错时排查)
方式一:
通过System.Data.Entity.DataBase.Log属性指定一个无返回值的委托,来实现记录日志的功能
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public partial class EFContext<T> : DbContext where T : class { public EFContext(): base("name=MyConnectionString") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { Database.SetInitializer<EFContext<T>> (null); Database.Log = log => File.AppendAllText("ef.log",string.Format("{0}{1}{2}", DateTime.Now, Environment.NewLine, log)); modelBuilder.Configurations.Add(new MemberMap()); modelBuilder.Configurations.Add(new RoleMap()); base.OnModelCreating(modelBuilder); } public DbSet<T> Table { get; set; } public IQueryable<T> GetList(Expression<Func<T,bool>> where) { return this.Table.Where(where); } }
其中:Database.Log = log => File.AppendAllText("ef.log",string.Format("{0}{1}{2}", DateTime.Now, Environment.NewLine, log)); 设置写入日志
控制台代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
EFContext<Member> efMemberContext = new EFContext<Member>();
var memberSet = efMemberContext.Set<Member>().Include("Role"); var memberList = memberSet.OrderBy(m => new { m.RoleId, m.Name }); foreach (Member item in memberList) { Console.WriteLine("{0},Role:{1}",item.Name,item.Role.Name); }
运行程序后,打开ef.log文件,发现记录了日志
方式二:自定义一个类,继承于DbCommandInterceptor,重写下面几个方法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public class EFDbCommandInterceptor : DbCommandInterceptor
{
/// <summary> /// 计时器 /// </summary> public volatile Stopwatch watch = new Stopwatch(); public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { base.NonQueryExecuting(command, interceptionContext); watch.Restart(); } public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { watch.Stop(); if (interceptionContext.Exception != null) { WriteLog(string.Format("Exception:{1} \r\n --> Error executing command: {0}", command.CommandText, interceptionContext.Exception.ToString())); } else { WriteLog(string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", watch.ElapsedMilliseconds, command.CommandText)); } base.NonQueryExecuted(command, interceptionContext); } public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { base.ScalarExecuting(command, interceptionContext); watch.Restart(); } public override void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { watch.Stop(); if (interceptionContext.Exception != null) { WriteLog(string.Format("Exception:{1} \r\n --> Error executing command: {0}", command.CommandText, interceptionContext.Exception.ToString())); } else { WriteLog(string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", watch.ElapsedMilliseconds, command.CommandText)); } base.ScalarExecuted(command, interceptionContext); } public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { base.ReaderExecuting(command, interceptionContext); watch.Restart(); } public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { watch.Stop(); if (interceptionContext.Exception != null) { WriteLog(string.Format("Exception:{1} \r\n --> Error executing command: {0}", command.CommandText, interceptionContext.Exception.ToString())); } else { WriteLog(string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", watch.ElapsedMilliseconds, command.CommandText)); } base.ReaderExecuted(command, interceptionContext); } /// <summary> /// 记录日志 /// </summary> /// <param name="msg">消息</param> private void WriteLog(string msg) { //指定true表示追加 using (TextWriter writer = new StreamWriter("Db.log",true)) { writer.WriteLine(msg); } } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public partial class EFContext<T> : DbContext where T : class { public EFContext(): base("name=MyConnectionString") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { Database.SetInitializer<EFContext<T>> (null); //Database.Log = log => File.AppendAllText("ef.log",string.Format("{0}{1}{2}", DateTime.Now, Environment.NewLine, log)); DbInterception.Add(new EFDbCommandInterceptor()); modelBuilder.Configurations.Add(new MemberMap()); modelBuilder.Configurations.Add(new RoleMap()); base.OnModelCreating(modelBuilder); } public DbSet<T> Table { get; set; } public IQueryable<T> GetList(Expression<Func<T,bool>> where) { return this.Table.Where(where); } }
其中DbInterception.Add(new EFDbCommandInterceptor()); 设置日志记录
还是刚才的控制台代码,运行程序,打开Db.log
另外还有其它方法获取Entity Framework 执行的sql代码,比如SQL Server Profiler工具,不过这个不属于通过Entity Framework代码去配置,所以在此就不再赘述