Entity Framework 6.x implements the SQL function for record execution, entity6.x

Entity Framework 6.x implements the SQL function for record execution, entity6.x

When using Entity Framework, Model is often manipulated for a lot of time, and SQL statements are not written. Sometimes, for debugging or optimization, you also need to track the SQL statements automatically generated by Entity framework (it is best to record them for troubleshooting when an error occurs)

Method 1:

You can use the System. Data. Entity. DataBase. Log attribute to specify a delegate with no return value to record logs.

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

Where:Database. log = log => File. appendAllText ("ef. log ", string. format ("{0} {1} {2}", DateTime. now, Environment. newLine, log ));Set log writing

Console code:

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

After running the program, open the ef. log File and find that the log is recorded.

 

Method 2: Customize a class, inherit from DbCommandInterceptor, and override the following methods:

Public class EFDbCommandInterceptor: DbCommandInterceptor {// <summary> /// timer /// </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, DbComma NdInterceptionContext <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 execution time: {0} millisecond \ r \ n --> ScalarExecuted. command: {1} \ r \ n ", watch. elapsedMilliseconds, command. commandText);} base. nonQueryExecuted (command, interceptionContext);} public override void ScalarExecuting (DbCommand comman D, 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 execution time: {0} millisecond \ 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 execution time: {0} millisecond \ r \ n --> ScalarExecuted. command: {1} \ r \ n ", watch. elapsedMilliseconds, command. commandText);} base. readerExecuted (command, interceptionContext );} /// <summary> // record the log // </summary> /// <param name = "msg"> message </param> private void WriteLog (string msg) {// specify true to append 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

WhereDbInterception. Add (new EFDbCommandInterceptor ());Set Logging

Or the console code, run the program, and open Db. log.

 

 

In addition, there are other methods to obtain the SQL code executed by Entity Framework, such as the SQL Server Profiler tool. However, this is not configured through Entity Framework code, so I will not go into details here.