Entity Framework 6.X实现记录执行的SQL功能
2023-09-11 14:14:06 时间
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代码去配置,所以在此就不再赘述
相关文章
- mysql数据库,如何在登录mysql之后执行操作系统上的SQL脚本?
- EXEC执行动态的SQL语句
- SQL Server 中 EXEC全称execute 与 SP_EXECUTESQL动态执行sql代码语句 的区别
- Sql Server 函数的操作实例!(执行多条语句,返回Select查询后的临时表)
- SQL建索引提示:联机索引操作只能在 SQL Server Enterprise Edition 中执行 解决方法
- Oracle中模拟SQL中的isnull函数
- [转]SQL注入攻防入门详解
- 使用sqlplus执行sql时,发现有中文有乱码解决方法
- 一文搞懂MySQL中一条SQL语句是如何执行的
- 一条SQL语句执行得很慢的原因有哪些?| MySQL高性能优化规范建议
- sql函数的使用——转换函数
- 转 SQLPLUS中SQL换行执行
- PL/SQL DEVELOPER 导出表数 和 生成 执行快速 插入表的 sql
- node.js连接数据库后执行sql语句怎么样实现呢?
- sql语句的执行顺序
- Sql Server 优化----SQL语句的执行方式与锁以及阻塞的关系
- SQL Server中的SQL语句优化与效率问题
- sql执行顺序
- 两天完成牛客网sql必知必会50题(附链接)
- (4.52)解决sql server动态SQL中对表字段操作时需要频繁 ISNULL的问题
- (4.33)sql server2014内存数据库(内存中OLTP(In-Memory OLTP))
- (4.9)SQL Server如何校验备份文件
- 应用Druid监控SQL语句的执行情况--转载
- SQL SERVER的统计信息
- go(golang)+gin+gorm常用web开发框架中,利用反射reflect实现执行任意原生sql返回map对象不丢失数据类型
- SQL语句多表查询练习