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

Normal LINQ Query

Let's write a sample class and use a 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, the total time spent was 190 milliseconds to execute a normal query. Now, go ahead and use a compiled query.

Compiled LINQ Query

Let's write a sample class and use a 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 the compiled query is here, it spent only 30 milliseconds to execute it.

Auto-Compiled LINQ Queries

Now with EF5 you no longer need to choose whether to use a 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 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 the approximate 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.