Monitor how EF generates SQL (log, sqlserverprofile)

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.

   static void Main (string[] args)

        {            using (schooldb2entities dbContext = new Schooldb2entities ())            {                DbContext.Database.Log = Console.WriteLine;                DBCONTEXT.STUDENTS.ADD (New Student ()                 {                     studentname = "jack123"                 });                  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>    <add 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.

Monitor how EF generates SQL (log, sqlserverprofile)