You need to know three methods to monitor SQL flow in EntityFramework: Log, SqlServerProfile, EFProfile, entityframework

You need to know three methods to monitor SQL flow in EntityFramework: Log, SqlServerProfile, EFProfile, entityframework

When you are learning entityframework, we all know that writing in linq is a breeze, and you no longer need to differentiate the SQL versions of different RDMS. However, high efficiency leads to poor flexibility.

Unable to control the SQL generation policy, so you must not have good tools to monitor SQL. This article introduces three monitoring methods: Log and SqlServer profile, ef profile...

 

I. Log monitoring

This is an Action method that comes with entity framework. It provides a good user experience. We can store the output in the console or write it to notepad... This

We can use EDM to generate codefirst. We can see the Log definition of the following Database. Then we can add an Action method with the string parameter to it, 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 this property // to System. console. write (System. string ). /// Note: // The format of the log text can be 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. IDbCommandInterceptor // and System. data. entity. infrastructure. interception. dbInterception. public Action <string> Log {get; set ;}

 1     static void Main(string[] args) 2         { 3             using (SchoolDB2Entities dbContext = new SchoolDB2Entities()) 4             { 5                 dbContext.Database.Log = Console.WriteLine; 6  7                 dbContext.Students.Add(new Student() 8                 { 9                     StudentName = "jack123"10                 });11 12                 dbContext.SaveChanges();13             }14         }

 

Because there is too much content during codefirst initialization and generation, it cannot be displayed one by one... To facilitate the injection of the custom method AppendLog, for example, to File...

 

Ii. SqlServer Profile

You can see that SqlServer Profile is monitored at the sqlserver level, and you can monitor how various SQL statements flow into sqlserver, But if you enable it by default, you will see a lot

Ef-independent operations, such as the following:

 

How can we filter out the data in a better way? In fact, it is also very simple. We only need to add an App tag in the connectionstring of ef, and then filter it on sqlserver profile.

 

Step 1: Add applicationName to connectionstring

  <connectionStrings>    <add name="SchoolDB2Entities" connectionString="data source=.;initial catalog=SchoolDB2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />  </connectionStrings>

 

Step 2: filter all ApplicationName = EntityFramework records in SqlProfile

 

OK, so that we can configure it. Next we will run codefirst. We can clearly see that the current profile only has the SQL statement generated by the EntityFramework mark.

 

Iii. Entity Framework Profile

First of all, this is a commercial software. The free trial period is 30 days. However, you can still find various cracked versions on the Internet. If you don't talk about it, download it through nuget:

PM> Install-Package EntityFrameworkProfiler is trying to collect and the target is ". the dependency information of the program "EntityFrameworkProfiler.3.0.3103" related to the program "leleapplication37" of NETFramework, Version = v4.6 "is attempting to parse the dependency of the program" EntityFrameworkProfiler.3.0.3103, dependencyBehavior is "Lowest". parsing operation to install package "EntityFrameworkProfiler.3.0.3103" has been resolved operation to install package "EntityFrameworkProfiler.3.0.3103". The package "Microsoft. web. infrastructure.1.0.0 "is added to the" c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ leleapplication37 \ packages "folder. The package" Microsoft. web. infrastructure.1.0.0 "is added to the" c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ leleapplication37 \ packages "folder. The package" Microsoft. web. add Infrastructure.1.0.0 to packages. config. web. infrastructure 1.0.0.0 "is successfully installed to leleapplication37. Adding the package" WebActivatorEx.2.0.5 "to the folder" c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ ConsoleApplication37 \ packages has added the package "WebActivatorEx.2.0.5" to the folder "c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ ConsoleApplication37 \ packages added the package "WebActivatorEx.2.0.5" to "packages. config has successfully installed "WebActivatorEx 2.0.5" to ConsoleApplication37. Adding the package "EntityFrameworkProfiler.3.0.3103" to the folder "c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ ConsoleApplication37 \ packages added the package "EntityFrameworkProfiler.3.0.3103" to the folder "c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ ConsoleApplication37 \ packages added the package "EntityFrameworkProfiler.3.0.3103" to "packages. config "is executing the script file" c: \ users \ xchuang \ documents \ visual studio 2015 \ Projects \ ConsoleApplication37 \ packages \ EntityFrameworkProfiler.3.0.3103.0 \ tools \ Install. ps1 "has successfully installed" EntityFrameworkProfiler 3.0.3103.0 "to leleapplication37pm>

 

Monitoring Program. This is the monitoring to be enabled.

 

After opening the software, you can register it and generate a licence xml file. Then you can see this awesome interface ....

 

Even more awesome, a section of open logic will be injected into our Main function, and a cs and a vb file will be generated in App_Start, as shown below:

 1     class Program 2     { 3         static void Main(string[] args) 4         {App_Start.EntityFrameworkProfilerBootstrapper.PreStart(); 5  6             using (SchoolDB2Entities dbContext = new SchoolDB2Entities()) 7             { 8                 dbContext.Database.Log = AppendLog; 9 10                 dbContext.Students.Add(new Student()11                 {12                     StudentName = "jack123"13                 });14 15                 dbContext.SaveChanges();16             }17         }18 19         static void AppendLog(string str) => System.IO.File.AppendAllText(Environment.CurrentDirectory + "\\1.txt", str, Encoding.Default);20     }

 

Since all the code has been generated... The only thing I need to do is Ctrl + F5.

Efprofile captures various create table statements, including panel information such as Application Statistics, Analysis, and Duration... The most important thing we can see is

"Query plan". For example, we construct a relatively complex SQL statement below:

 1 class Program 2     { 3         static void Main(string[] args) 4         { 5             App_Start.EntityFrameworkProfilerBootstrapper.PreStart(); 6  7             using (SchoolDB2Entities dbContext = new SchoolDB2Entities()) 8             { 9                 var query = (from n in dbContext.Students10                              from m in dbContext.StudentAddresses11                              where n.StudentID == m.StudentID12                              group n.StudentID by n.StudentName into g13                              select new { g.Key, count = g.Count() }).ToList();14 15                 dbContext.SaveChanges();16             }17         }18     }

 

Run the following command to check how the efprofile monitors the db to generate a snapshot of the query plan...

 

Okay, this is basically the three methods. Will you monitor SQL flow in your ef?