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.
Thanks sir...
ReplyDelete