EntityFramework Multiple logging methods, logging errors and analyzing the reason for the long Execution Time (series 4)

Entity Framework Extension Series Catalog

Today we're going to talk about EF's log records.

A good database operation record can not only help you to record the user's operation,

should also help you get inefficient statements to help you improve your operational efficiency.

Nonsense not much to say that we started

Environmental and related technologies the environment and technology used in this paper

System: WIN7

Database: SQL Server2008

Related technologies: MVC5 ef6.0+

A simple record

First, modify the configuration file

Let's take a look at the most streamlined EF logging, no code changes, and the following configuration is automatically recorded in your configuration file:

Add the following configuration to your EntityFramework node (note that the first parameter is the output address of your log):

<interceptors>      <interceptor type= "System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework ">        <parameters>          <parameter value=" D:\ttt\log.txt "/>          <parameter Value= "true" type= "System.Boolean"/>        </parameters>      </interceptor>    </interceptors >

We can find the relevant log file under the corresponding address as follows:

Second, simple package:

Write a base class of your own DbContext as follows:

public class databasecontext<t>: DbContext where T:class,new () {    //override SaveChanges method public     override int SaveChanges ()     {              String sql = "";                Record Entity action log this                    . Database.Log = (a)                    + = {                        SQL + = A;                    };            Here's the SQL is the operation of the log, you want to remember where to remember it. I'm not going to make            it here. Return base. SaveChanges ();      }}

　

Monitoring via a low-level listening interface

If you just want to record, the above two ways should be able to meet you.

The purpose of our record is, in fact, the most important thing is to analyze the performance and start our plays.

EF monitoring with idbcommandinterceptor interface

First, let's look at what's inside this interface:

People who have written about ADO should be familiar with these words. (because EF eventually accesses the database in a way that is still used by ADO)

Note: Each execution has an ED (listener after execution) and ING (listening on execution)

Now let's step through this interface

First define a class (the name you Feel Free):

The name can be arbitrary, but must inherit our listening interface--, Public    class databaselogger:idbcommandinterceptor{}

And then we go on,

Defining a static read-only concurrentdictionary as our record repository, taking into account the fact that multithreading is common in data access, we adopt a thread-safe concurrentdictionary

The code is as follows:

public class Databaselogger:idbcommandinterceptor {        static readonly Concurrentdictionary<dbcommand, DateTime > mstarttime = new Concurrentdictionary<dbcommand, datetime> (); }

Next, let's implement the two methods we need one for OnStart to record the start time of the SQL statement execution

As follows:

The time when the record started executing private static void OnStart (DbCommand command) {            mstarttime.tryadd (command, DateTime.Now);}

Then implement our log method to record the relevant SQL statements and error messages

        private static void log<t> (DbCommand command, dbcommandinterceptioncontext<t> interceptioncontext)            {DateTime startTime;           TimeSpan duration;            Get the start time of this command mstarttime.tryremove (command, out startTime);            if (startTime! = Default (DateTime)) {duration = Datetime.now-starttime;            } else Duration = TimeSpan.Zero;            var parameters = new StringBuilder (); Loop gets the parameter value of the EXECUTE statement, foreach (dbparameter param in command. Parameters) {Parameters. Appendline (param. ParameterName + "" + param. DbType + "=" + param.            Value); }//To determine if the statement is executing for more than 1 seconds or if there is an error if (duration. TotalSeconds > 1 | | Interceptioncontext.exception!=null) {//write code here that executes an extra long time SQL statement and error message} E LSE {//Here Write your own code for recording Plain SQL statements}} 

Now that we have got what we want, that specific record way, you benevolent see on the casual, so I will not write.

Then we will implement the 6 methods of this interface, as follows:

        public void nonqueryexecuted (DbCommand command, dbcommandinterceptioncontext<int> interceptioncontext)        {Log (command, Interceptioncontext);        } public void nonqueryexecuting (DbCommand command, dbcommandinterceptioncontext<int> interceptioncontext)        {OnStart (command); } public void readerexecuted (DbCommand command, dbcommandinterceptioncontext<dbdatareader> Interceptioncontex        T) {Log (command, Interceptioncontext); } public void readerexecuting (DbCommand command, dbcommandinterceptioncontext<dbdatareader> interceptionconte        XT) {OnStart (command);        } public void scalarexecuted (DbCommand command, dbcommandinterceptioncontext<object> interceptioncontext)        {Log (command, Interceptioncontext); } public void scalarexecuting (DbCommand command, dbcommandinterceptioncontext<object> interceptioncontext) {OnStart (command); }

In fact, it is simply that all ing executes the OnStart method we wrote before, and all Ed executes our log method.

Next, we need to inject this interface:

Here my demo uses MVC so I just injected it directly into the Application_Start (), as follows:

protected void Application_Start () {          //inject your own written listener            Dbinterception.add (new Miniprofiler_efmodel.databaselogger ());}

So we're done with the whole listening process.

The implementation results are as follows:

We got the number of seconds to execute.

The executed SQL statement was obtained:

The parameters corresponding to the SQL statement are obtained:

Done!

Written in the last

Here I just help you through the monitoring to obtain the relevant information, how to optimize, what should be used to record, I do not too much to repeat, this is the benevolent see of things, but interested in the blog can be added to my QQ chat. Welcome.

EntityFramework Multiple logging methods, logging errors and analyzing the reason for the long Execution Time (series 4)