关于EF6的记录Sql语句 与 EntityFramework.Exten…

2018-06-18 01:57:42来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

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_Sqlite
10 {
11     class EFIntercepterLogging : DbCommandInterceptor
12     {
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             else
27             {
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             else
48             {   
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             else
69             {
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             try
12             {
13                 // get store connection and transaction
14                 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 NET45
63                 int result = async
64                     ? await deleteCommand.ExecuteNonQueryAsync().ConfigureAwait(false)
65                     : deleteCommand.ExecuteNonQuery();
66 #else
67                 int result = deleteCommand.ExecuteNonQuery();
68 #endif
69                 // only commit if created transaction
70                 if (ownTransaction)
71                     deleteTransaction.Commit();
72 
73                 return result;
74             }
75             finally
76             {
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了。)

 

 

 

修改后的DLL文件

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:csharp:VerifyCode in winform or webform

下一篇:简析 .NET Core 构成体系