Entity Framework 6.X实现记录执行的SQL功能

Entity Framework在使用时，很多时间操纵的是Model，并没有写sql语句，有时候为了调试或优化等，又需要追踪Entity framework自动生成的sql（最好还能记录起来，方便出错时排查）

方式一：

通过System.Data.Entity.DataBase.Log属性指定一个无返回值的委托，来实现记录日志的功能

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)); 设置写入日志

控制台代码：

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，重写下面几个方法

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);   
            }
        }
    }

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代码去配置，所以在此就不再赘述