Monitor SQL flow in entityframework you need to know the three ways Log,sqlserverprofile, Efprofile

Everyone in the study entityframework, all know that LINQ write called a cool, no longer have to distinguish between different RDMS SQL version differences, but, high efficiency brings poor flexibility, we

Unable to control the SQL generation strategy, so you must not let yourself lack of good tools to monitor SQL, this article introduces you to the three kinds of monitoring methods log and SQL Server Profile,ef profile ...

One: Log monitoring

This is an action method that comes with the Entity Framework, which gives you a good user experience, and we can put its output in the console, or write it to Notepad ... This

I will generate codefirst through the EDM, you can see the following database's log definition, and then you can give him a string parameter of the action method, such as Console.WriteLine.

 //        //Summary://Set This property to log the SQL generated by the System.Data.Entity.DbContext//To the given delegate. For example, to log to the console, set the//To System.Console.Write (System.String). //        //remark://The format of the log text can is changed by creating a new formatter that derives//From System.Data.Entity.Infrastructure.Interception.DatabaseLogFormatter and//setting it with System.Data.Entity.DbConfiguration.SetDatabaseLogFormatter (system.func{ System.data.entity.dbcontext,system.action{system.string}, System.Data.Entity.Infrastructure.Interception.DatabaseLogFormatter}).//For more low-level control over logging/interception see System.Data.Entity.Infrastructure.Interception.IDbComman Dinterceptor//and System.Data.Entity.Infrastructure.Interception.DbInterception.         Publicaction<string> Log {Get;Set; }

1     Static voidMain (string[] args)2         {3             using(Schooldb2entities DbContext =Newschooldb2entities ())4             {5DbContext.Database.Log =Console.WriteLine;6 7DBCONTEXT.STUDENTS.ADD (NewStudent ()8                 {9Studentname ="jack123"Ten                 }); One  A dbcontext.savechanges (); -             } -}

Because there are too many content at the time of Codefirst initialization, it cannot be displayed ... To facilitate the use of custom method Appendlog, such as pouring it into file ...

II: SQL Server profile

You can see that SQL Server profile is monitored at the SQL Server level and can monitor how various SQL flows into SQL Server, but if you turn it on by default, you'll see a lot of

EF-independent operations, such as the following:

What's the better way to filter it? It's also very simple, we just need to cram an app tag into EF's ConnectionString and then filter on SQL Server profile.

First step: Add ApplicationName to ConnectionString

  < connectionStrings >    <  name= "Schooldb2entities"  connectionString= "Data source=.; Initial catalog=schooldb2;integrated security=true; Multipleactiveresultsets=true; app=entityframework"  providerName=" System.Data.SqlClient "/>   </connectionStrings>

Step Two: Filter all records of Applicationname=entityframework in Sqlprofile

OK, so that we are configured, then we will codefirst run up, we can clearly see that the current profile is only the entityframework tag generated SQL statement.

III: Entity Framework profile

First of all, this is a commercial software, the free trial period is 30 days, but the online still can search for a variety of cracked version, nonsense said, we download through NuGet:

Pm> install-Package Entityframeworkprofiler is trying to collect with the target ". Netframework,version=v4.6"The project" ConsoleApplication37 "about the package" Entityframeworkprofiler.3.0.3103"The related dependency information is trying to resolve the package" Entityframeworkprofiler.3.0.3103"Dependencybehavior for" Lowest "is resolving the operation to install the package" Entityframeworkprofiler.3.0.3103"Resolved operation to install package" Entityframeworkprofiler.3.0.3103The package is being Microsoft.Web.Infrastructure.1.0.0"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Microsoft.Web.Infrastructure.1.0.0"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Microsoft.Web.Infrastructure.1.0.0"added to" Packages.config "has" Microsoft.Web.Infrastructure1.0.0.0"The package was successfully installed to ConsoleApplication37" Webactivatorex.2.0.5"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Webactivatorex.2.0.5"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Webactivatorex.2.0.5"added to" Packages.config "has" Webactivatorex2.0.5"The package was successfully installed to ConsoleApplication37" Entityframeworkprofiler.3.0.3103"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Entityframeworkprofiler.3.0.3103"Add to Folder" C:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages "Package" is Entityframeworkprofiler.3.0.3103"adding to" packages.config "executing script file" c:\users\xchuang\documents\visual Studio -\projects\consoleapplication37\packages\entityframeworkprofiler.3.0.3103.0\tools\install.ps1 "has been" Entityframeworkprofiler3.0.3103.0"successfully installed to consoleapplication37pm>

After downloading, then look at the subfolder in the Packages folder entityframeworkprofiler.3.0.3103.0, find a program called Efprof.exe, this is to open the monitoring.

After opening the software, you can register yourself, generate a licence of XML, and then we can see such a fantastic interface ....

What's more, it will inject an open logic into our main function and generate a CS, a VB file in App_start, as follows:

1     class Program2     {3         Static voidMain (string[] args)4 {App_Start.EntityFrameworkProfilerBootstrapper.PreStart (); 5 6             using(Schooldb2entities DbContext =Newschooldb2entities ())7             {8DbContext.Database.Log =Appendlog;9 TenDBCONTEXT.STUDENTS.ADD (NewStudent () One                 { AStudentname ="jack123" -                 }); -  the dbcontext.savechanges (); -             } -         } -  +         Static voidAppendlog (stringstr) = System.IO.File.AppendAllText (Environment.currentdirectory +"\\1.txt", str, encoding.default); -}

Now that the code is built ... The only thing left for me to do is CTRL + F5.

As you can see, efprofile can capture a variety of CREATE TABLE statements, including various panel information: application statistics,analysis,duration, etc... And the most important thing we can see

"Query plan", for example, I construct a relatively complex SQL statement:

1 class Program2     {3         Static voidMain (string[] args)4         {5 App_Start.EntityFrameworkProfilerBootstrapper.PreStart ();6 7             using(Schooldb2entities DbContext =Newschooldb2entities ())8             {9                 varquery = ( fromNinchdbcontext.studentsTen                               fromMinchdbcontext.studentaddresses One                              whereN.studentid = =M.studentid A group N.studentid by N.studentname into G -                              Select New{G.key, Count =G.count ()}). ToList (); -  the dbcontext.savechanges (); -             } -         } -}

Then take a look at how Efprofile monitors the snapshot of how the query plan is generated in db ...

Okay, so that's basically the case with three ways, are you watching your own SQL flow in EF?

Monitor SQL flow in entityframework you need to know the three ways Log,sqlserverprofile, Efprofile