Ask your question here : ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK ASP.NET JQUERY C# VIDEOS MVC EBOOK ARCHIVE

26 Sep 2012

Normal LINQ Query to Compiled LINQ Query to Auto-Compiled LINQ Queries in Entity Framework 5

Entity Framework 5 brings number of improvements and query performance improvement is one of them. In this article I’ll be talking all on query performances (EF1 to EF5) so that you can understand the improvements better.

Normal LINQ Query

Let’s write a sample class and use timer to get the actual time spent on query execution.

//Normal Query Method
private static void NormalQuery()
{
    using (DataClasses1DataContext db = new DataClasses1DataContext())
    {
        var myNormalQuery = from i in db.Customers
                            select i.Country;

        foreach (var i in myNormalQuery)
        {
            Console.WriteLine(i);
        }
    }
}

And trigger it using:

Stopwatch sw = new Stopwatch();
sw.Start();
NormalQuery();
Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw.ElapsedMilliseconds);
sw.Stop();

You will get output and time spent like:

Germany
Mexico
Mexico
UK
:::::::::
Elapsed Time in Milliseconds : 190

So, total time spent 190 milliseconds to execute normal query. Now, go ahead and use compiled query.

Compiled LINQ Query

Let’s write a sample class and use timer to get the actual time spent on query execution.

//Compiled Query Method
private static void CompiledQueryEF()
{
    using (DataClasses1DataContext db = new DataClasses1DataContext())
    {
        var cq = myCompiledQuery(db);

        foreach (var k in cq)
        {
            Console.WriteLine(k);
        }
    }
}

//Compiled Query
private static readonly Func<DataClasses1DataContext, IEnumerable<string>> myCompiledQuery =
    CompiledQuery.Compile<DataClasses1DataContext, IEnumerable<string>>(
    (db) => from i in db.Customers
                select i.Country);

And trigger it using:

Stopwatch sw1 = new Stopwatch();
sw1.Start();
CompiledQueryEF();
Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw1.ElapsedMilliseconds);
sw1.Stop();

You will get output and time spent like:

Germany
Mexico
Mexico
UK
::::
Elapsed Time in Milliseconds : 30

And the magic of compiled query is here, it spent only 30 milliseconds to execute it.

Auto-Compiled LINQ Queries

Now with EF5 you no longer need to make choice which one to use normal query or compiled query. And as a bonus, you don't have to explicitly create CompiledQuery objects in code to benefit from the precompilation.

EF5 (Entity Framework June 2011 CTP) brings us auto-compiled queries, which work very differently than CompiledQuery. Instead of your writing code to compile each query and then invoking each as needed, Entity Framework caches the generated SQL for you as a background process, then searches the cache for already compiled queries when you execute any query.

Within the Visual Studio 2012 IDE, the Entity Framework Designer has also gained some great new capabilities. EF5 incorporates all of the work distributed via NuGet starting with the EF 4.1 release: Code First, the DbContext API, and Code First Migrations.

Complete Code

Run it twice and thrice to get approx. time spent.

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Diagnostics;
using System.Linq;

namespace Auto_Compiled_LINQ_Query
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            NormalQuery();
            Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw.ElapsedMilliseconds);
            sw.Stop();

            Stopwatch sw1 = new Stopwatch();
            sw1.Start();
            CompiledQueryEF();
            Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw1.ElapsedMilliseconds);
            sw1.Stop();

            Console.ReadKey();
        }

        //Normal Query Method
        private static void NormalQuery()
        {
            using (DataClasses1DataContext db = new DataClasses1DataContext())
            {
                var myNormalQuery = from i in db.Customers
                                    select i.Country;

                foreach (var i in myNormalQuery)
                {
                    Console.WriteLine(i);
                }
            }
        }

        //Compiled Query Method
        private static void CompiledQueryEF()
        {
            using (DataClasses1DataContext db = new DataClasses1DataContext())
            {
                var cq = myCompiledQuery(db);

                foreach (var k in cq)
                {
                    Console.WriteLine(k);
                }
            }
        }

        //Compiled Query
        private static readonly Func<DataClasses1DataContext, IEnumerable<string>> myCompiledQuery =
            CompiledQuery.Compile<DataClasses1DataContext, IEnumerable<string>>(
            (db) => from i in db.Customers
                        select i.Country);
    }
}

I hope you like it. Thanks.

Comment using Google Services (1 comment):