你的位置:首页 > ASP.net教程

[ASP.net教程]关于EF6的记录Sql语句 与 EntityFramework.Extend 的诟病


1、关于EF6的记录Sql语句,一个老生长谈的问题。 他生成的sql语句实在是烂,大家都这样说

2、EF6 更新删除不方便,没有批量操作。所以,有人出了EF6.Extend  大家用起来也很爽

 

基于以上两点,我也尝试着使用 EF6.Extend 。本以为可以很好的,很美好。没有想到我遇到了一个大问题。

我需要 通过程序记录 EF执行的Sql语句,当然也包括 EF6.Extend 执行的Sql语句。(不是通过SqlProfiler)

在网上查找,发现 了一篇文章,我就这样抄下来了。(太匆忙解决问题,忘记了哪一篇)

继承了 System.Data.Entity.Infrastructure.Interception.DbCommandInterceptor  ,实现相关方法。  然后在Main方法(程序入口)进行添加  DbInterception.Add(new EFIntercepterLogging());   

 

 1 using System; 2 using System.Collections.Generic; 3 using System.Data.Entity.Infrastructure.Interception; 4 using System.Diagnostics; 5 using System.Linq; 6 using System.Text; 7 using System.Threading.Tasks; 8  9 namespace EF_Sqlite10 {11   class EFIntercepterLogging : DbCommandInterceptor12   {13     private readonly Stopwatch _stopwatch = new Stopwatch();14     public override void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)15     {16       base.ScalarExecuting(command, interceptionContext);17       _stopwatch.Restart();18     }19     public override void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)20     {21       _stopwatch.Stop();22       if (interceptionContext.Exception != null)23       {24         Trace.TraceError("Exception:{1} \r\n --> Error executing command: {0}", command.CommandText, interceptionContext.Exception.ToString());25       }26       else27       {28         string txt=string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", _stopwatch.ElapsedMilliseconds, command.CommandText);29         Console.WriteLine(txt);30         Trace.TraceInformation(txt);31       }32       base.ScalarExecuted(command, interceptionContext);33     }34     public override void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)35     {36       base.NonQueryExecuting(command, interceptionContext);37       _stopwatch.Restart();38     }39     public override void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)40     {41       42       _stopwatch.Stop();43       if (interceptionContext.Exception != null)44       {45         Trace.TraceError("Exception:{1} \r\n --> Error executing command:\r\n {0}", command.CommandText, interceptionContext.Exception.ToString());46       }47       else48       {  49         50         string txt = string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", _stopwatch.ElapsedMilliseconds, command.CommandText);51         Console.WriteLine(txt);52         Trace.TraceInformation(txt);53       }54       base.NonQueryExecuted(command, interceptionContext);55     }56     public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)57     {58       base.ReaderExecuting(command, interceptionContext);59       _stopwatch.Restart();60     }61     public override void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)62     {63       _stopwatch.Stop();64       if (interceptionContext.Exception != null)65       {66         Trace.TraceError("Exception:{1} \r\n --> Error executing command:\r\n {0}", command.CommandText, interceptionContext.Exception.ToString());67       }68       else69       {70         string txt = string.Format("\r\n执行时间:{0} 毫秒\r\n-->ScalarExecuted.Command:{1}\r\n", _stopwatch.ElapsedMilliseconds, command.CommandText);71         Console.WriteLine(txt);72         Trace.TraceInformation(txt);73       }74       base.ReaderExecuted(command, interceptionContext);75     }76 77     78   }79 }

日志记录类完整代码

 

通过EF正常的操作是可以记录到SQL语句的,而通过EF.Extend执行的删除操作是无法获取sql的。我想,是不是我写错了,可网上根本没有关于EF.Extend 记录生成SQL的只言片语,可能大家都没有遇到这样的问题。  

只能硬着头皮,翻源码。

经过翻看EF.Extend的源码,发现他是直接用Command执行的sql,再翻 EF的源码发现,EF是绕了很大一圈来执行的SQL 

 找到EF的这里,我明白了

 1 public virtual int NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) 2 { 3 Check.NotNull(command, "command"); 4 Check.NotNull(interceptionContext, "interceptionContext"); 5  6 return _internalDispatcher.Dispatch( 7 command, 8 (t, c) => t.ExecuteNonQuery(), 9 new DbCommandInterceptionContext<int>(interceptionContext),10 (i, t, c) => i.NonQueryExecuting(t, c),11 (i, t, c) => i.NonQueryExecuted(t, c));12 }

EF的执行代码

 

 而,EF.Extend的代码是这样写的

 1 private int InternalDelete<TEntity>(ObjectContext objectContext, EntityMap entityMap, ObjectQuery<TEntity> query) 2       where TEntity : class 3 #endif 4     { 5       DbConnection deleteConnection = null; 6       DbTransaction deleteTransaction = null; 7       DbCommand deleteCommand = null; 8       bool ownConnection = false; 9       bool ownTransaction = false;10 11       try12       {13         // get store connection and transaction14         var store = GetStore(objectContext);15         deleteConnection = store.Item1;16         deleteTransaction = store.Item2;17 18         if (deleteConnection.State != ConnectionState.Open)19         {20           deleteConnection.Open();21           ownConnection = true;22         }23 24         if (deleteTransaction == null)25         {26           deleteTransaction = deleteConnection.BeginTransaction();27           ownTransaction = true;28         }29 30 31         deleteCommand = deleteConnection.CreateCommand();32         deleteCommand.Transaction = deleteTransaction;33         if (objectContext.CommandTimeout.HasValue)34           deleteCommand.CommandTimeout = objectContext.CommandTimeout.Value;35 36         var innerSelect = GetSelectSql(query, entityMap, deleteCommand);37 38         var sqlBuilder = new StringBuilder(innerSelect.Length * 2);39 40         sqlBuilder.Append("DELETE ");41         sqlBuilder.Append(entityMap.TableName);42         sqlBuilder.AppendLine();43 44         sqlBuilder.AppendFormat("FROM {0} AS j0 INNER JOIN (", entityMap.TableName);45         sqlBuilder.AppendLine();46         sqlBuilder.AppendLine(innerSelect);47         sqlBuilder.Append(") AS j1 ON (");48 49         bool wroteKey = false;50         foreach (var keyMap in entityMap.KeyMaps)51         {52           if (wroteKey)53             sqlBuilder.Append(" AND ");54 55           sqlBuilder.AppendFormat("j0.[{0}] = j1.[{0}]", keyMap.ColumnName);56           wroteKey = true;57         }58         sqlBuilder.Append(")");59 60         deleteCommand.CommandText = sqlBuilder.ToString();61 62 #if NET4563         int result = async64           ? await deleteCommand.ExecuteNonQueryAsync().ConfigureAwait(false)65           : deleteCommand.ExecuteNonQuery();66 #else67         int result = deleteCommand.ExecuteNonQuery();68 #endif69         // only commit if created transaction70         if (ownTransaction)71           deleteTransaction.Commit();72 73         return result;74       }75       finally76       {77         if (deleteCommand != null)78           deleteCommand.Dispose();79 80         if (deleteTransaction != null && ownTransaction)81           deleteTransaction.Dispose();82 83         if (deleteConnection != null && ownConnection)84           deleteConnection.Close();85       }86     }

EF.Extend的执行代码

 

 经过分析,是这个道理,按照这个逻辑,EF.Extend没有按照EF的逻辑写,所以,他不能通过这种方式记录Sql。

恍然大悟后,我这样执行的Sql

System.Data.Common.DbConnection con = t.Database.Connection;System.Data.Common.DbCommand command = con.CreateCommand();con.Open();command.CommandText = "delete from area where 1=2 and 4=9";DbInterception.Dispatch.Command.NonQuery(command, new DbCommandInterceptionContext());

EF 自定义的SQL执行

 就这样,我 通过统一的方式,获取到了我自己执行的Sql语句,和EF执行的Sql语句

 

本来是打算用EF.Extend的,看到这里,我决定不用了,有点杀鸡用牛刀。(其实,EF.Extend 不仅扩展了修改和删除的方法,还扩展了 EF没有的二级缓存,等等。如果只是用到修改删除的扩展方法,那可以放弃Extend了。)