Using tools to track SQL generated by the Entity Framework

Learn articles collected during the Entity Framework, transferred from Http://www.cnblogs.com/hiteddy/archive/2011/10/01/Difference_among_IQueryable_ Ienumeralb_ilist_in_entity_framework.html

When using an ORM framework such as the Entity Framework, SQL is transparent to the user, and often many people do not care about the SQL generated by the ORM, but when the system has performance problems, it must focus on the generated SQL to find the problem.

Friends who have used TopLink know very much as long as you set the log print level =fine You can configure the generated SQL to print on the server, the Entiry framework is not so fortunate that the only way to detect the generation of SQL before is SQL Server Profiler , but it is not convenient to use, and the results cannot be saved automatically to the file.

The tracing and Caching Provider Wrappers for Entity Framework is the new open source SQL tracing and level two cache solution for the Entity Framework team. The principle is to insert a layer of wrappingprovider on top of the data provider (SqlClient or other client) responsible for executing the specific SQL statement to monitor Dbcommand.executereader (), ExecuteScalar () and ExecuteNonQuery (), outputs the SQL command to the specified media, or caches the query results for reuse.

Using the method is very simple, download the source code after compiling the DLL added to the project, the new addition of a class wrappednorthwindentities inherit the original entities, see the source code in the example.

Test the difference between IQueryable, IEnumerable, IList

Below we use EF wrapper to monitor the SQL generated by IQueryable, IEnumerable, and IList in the Entify framework.

Testiqueryable

private static void Testiqueryable ()
{
using (var ctx = new Wrappednorthwindentities ())
{
iqueryable<product> expression = ctx. Products.take (5);
Iqueryable<product> products = expression. Take (2); A does not execute SQL
Console.WriteLine (Products.　　　　　　　　　　Count ()); B Select COUNT (1) from (select Top (2) * FROM (select TOP (5) * FROM [dbo].[ Products]))
Console.WriteLine (Products.　　　　　　　　　　Count ()); C Select COUNT (1) from (select Top (2) * FROM (select TOP (5) * FROM [dbo].[ Products]))
foreach (Product p in products)//D Select TOP (2) * FROM (SELECT top (5) * FROM [dbo].[ Products]
{
Console.WriteLine (P.productname);
}
foreach (Product p in products)//E Select TOP (2) * FROM (SELECT top (5) * FROM [dbo].[ Products])
{
Console.WriteLine (P.productname);
}
}
}

Testienumerable

private static void Testienumerable ()
{
using (var ctx = new Wrappednorthwindentities ())
{
ienumerable<product> expression = ctx. Products.take (5). AsEnumerable ();
Ienumerable<product> products = expression. Take (2); A does not execute SQL
Console.WriteLine (Products.　　　　　　　　　　Count ()); B SELECT TOP (5) * FROM [dbo]. [Products]
Console.WriteLine (Products.　　　　　　　　　　Count ()); C SELECT TOP (5) * FROM [dbo]. [Products]
foreach (Product p in products)//D SELECT TOP (5) * FROM [dbo]. [Products]
{
Console.WriteLine (P.productname);
}
foreach (Product p in products)//E SELECT TOP (5) * FROM [dbo]. [Products]
{
Console.WriteLine (P.productname);
}
}
}

private static void Testienumerable ()
{
    using (var ctx = new Wrappednorthwindentities ())
    {
        ienumerable<product> expression = ctx. Products.take (5). AsEnumerable ();
        Ienumerable<product> products = expression. Take (2);  A  does not execute SQL
        Console.WriteLine (Products.　　　　　　　　　　Count ()); B SELECT TOP (5) * FROM [dbo]. [Products]
        Console.WriteLine (Products.　　　　　　　　　　Count ()); C  SELECT TOP (5) * FROM [dbo].[ Products]
        foreach (Product p in products)//D  
        {
            Console.WriteLine (P.productname);
        }
        foreach (Product p in products)  //E  SELECT TOP (5) * FROM [dbo].[ Products]
        {
            Console.WriteLine (P.productname);
        }
    }
}

Testilist

private static void Testilist ()
{
using (var ctx = new Wrappednorthwindentities ())
{
var expression = ctx. Products.take (5);
Ilist<product> products = expression. Take (2). ToList (); A Select TOP (2) * FROM (SELECT top (5) * FROM [dbo].[ Products]

Console.WriteLine (Products.　　　　　　　　　　 Count ()); B do not execute SQL
Console.WriteLine (Products.　　　　　　　　　　 Count ()); C do not execute SQL
foreach (Product p in products)//D does not execute SQL
{
Console.WriteLine (P.productname);
}
foreach (Product p in products)//E does not execute SQL
{
Console.WriteLine (P.productname);
}
}
}

private static void Testilist ()
{
    using (var ctx = new Wrappednorthwindentities ())
    {
        var expression = ctx. Products.take (5);
        Ilist<product> products = expression. Take (2). ToList (); A  Select TOP (2) * FROM (SELECT top (5) * FROM [dbo].[ Products]

        Console.WriteLine (Products.　　　　　　　　　　Count ());  //B do not execute SQL
        Console.WriteLine (Products.　　　　　　　　　　Count ());  //C  do not execute SQL
        foreach (Product p in products)  //D  does not execute SQL
        {
            Console.WriteLine (P.productname);
        }
        foreach (Product p in products)    //E  does not execute SQL
        {
            Console.WriteLine (P.productname);
        }
    }
}

Test results

1. IQueryable and IEnumerable are both deferred execution (Deferred execution), while IList is instant execution (Eager execution)
2. IQueryable and IEnumerable must connect to the database for each execution, and the IList reads once and does not need to connect to the database at a later time. The first two are prone to duplicate reads, low performance, and may cause data inconsistencies
3. The difference between IQueryable and IEnumerable: Ienumberalb uses the LINQ to object method, which loads all the records corresponding to AsEnumerable () into memory first, and then executes the subsequent query on that basis. So the SQL executed in the testienumerable example above is "select Top (5) ..." and then select the first two records in memory to return.

The following is an example of a IQueryable data inconsistency: The total number of records and the details of the records should be consistent, but since the IQueryable two times read the database, the result is a realistic 10 records, but output 11 details.

IQueryable Data inconsistancy

iqueryable<product> products = ctx. Products.all ();
At first, there were 10 records in the database product table, Count = 10
int count = products. Count ();
Console.WriteLine ("Count of Products:" +count);

At this point another process adds a product into the database
Will reread the database and output 11 product names
foreach (Product p in products)
{
Console.WriteLine (P.productname);
}

    iqueryable<product> products = ctx. Products.all ();
    At first, there were 10 records in the database product table, Count = 10
    int count = products. Count ();
    Console.WriteLine ("Count of Products:" +count);  

        
    At this point another process adds a product into the database

    Will reread the database and output 11 product names
    foreach (Product p in products)       
    {
    Console.WriteLine (P.productname);    
    }

Conclusion

Based on the two points of performance and data consistency, we must be cautious when using IQueryable, and in most cases we should use IList.

• When you intend to use the results immediately after the query (such as looping for logic processing or filling in a table/grid), and you do not mind that the query will be executed immediately, using ToList ()
• When you want the results of the query to be available to the caller (Consummer) for subsequent queries (such as this is a "GetAll" method), or if you want the query to be deferred, use AsQueryable ()

Using tools to track SQL generated by the Entity Framework