Entity Framework在使用時，很多時間操縱的是Model，並沒有寫sql語句，有時候為了調試或優化等，又需要追蹤Entity framework自動生成的sql（最好還能記錄起來，方便出錯時排查） 方式一： 通過System.Data.Entity.DataBase.Log屬性指定一個無 ...
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代碼去配置，所以在此就不再贅述
