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

2017-09-13 08:14:30来源:cnblogs.com作者:Jichan·Jong人点击

分享

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

其中: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);}
View Code

运行程序后,打开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);               }        }    }
View Code
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);        }    }
View Code

其中DbInterception.Add(new EFDbCommandInterceptor());  设置日志记录

还是刚才的控制台代码,运行程序,打开Db.log

另外还有其它方法获取Entity Framework 执行的sql代码,比如SQL Server Profiler工具,不过这个不属于通过Entity Framework代码去配置,所以在此就不再赘述

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台