Compiled Query in Entity Framework 4.0

There are times when we want to make optimizations on some piece of code. If we want to reduce the cost of executing a query in Entity Framework we can use a Compiled Query.

If we are using similar query frequently, we can increase its performance by Compiling it through Compiled Query. It’s always recommended to use Compiled Query if you happen to see the query is getting executed many times. Compiled Queries are a delegate which store a compiled LINQ query that we have built in advance. When we use them we can reduce the cost of executing a LINQ query. This can be very helpful when we have a query that we execute multiple times.

Let’s take an example, in North wind database. If you are getting Customer based on City, you may follow the below approach. Also a very important point to observe is to see how it runs faster in subsequent calls even though the parameter values differ.

Compiled Query:

private static readonly Func<NorthwindEntities, string, IQueryable<Customer>> myCompiledQuery =

CompiledQuery.Compile<NorthwindEntities, string, IQueryable<Customer>>(

(ctx, city) => from c in ctx.Customers

where c.City == city

select c);

Now, instead of writing raw LINQ use this Compiled Query

Calling It :

private static void CallCompiledQuery(string myCity)

{

using (NorthwindEntities ctx = new NorthwindEntities())

{

var q = myCompiledQuery(ctx, myCity);

foreach (var k in q)

{

Console.WriteLine(k.CompanyName);

}

}

}

We also wanted to capture the time.

static void CallIt(string sCity)

Stopwatch sw = new Stopwatch();

sw.Start();

CallCompiledQuery(sCity);

Console.WriteLine(“+++++++++++++++++++++”);

Console.WriteLine(“Elapsed Time in Milliseconds : {0}”, sw.ElapsedMilliseconds);

sw.Stop();

}

Just call twice to capture the time,

static void Main(string[] args)

{

Console.WriteLine();

Console.WriteLine(“++++++++++++++++”);

Console.WriteLine(“Call No. 1”);

Console.WriteLine(“++++++++++++++++”);

CallIt(“Mumbai”);

Thread.Sleep(2000);

Console.WriteLine();

//

Console.WriteLine();

Console.WriteLine(“++++++++++++++++”);

Console.WriteLine(“Call No. 2”);

Console.WriteLine(“++++++++++++++++”);

CallIt(“Cedar Rapids”);

Thread.Sleep(2000);

Console.WriteLine();

}

In my machine the first call took 322 milliseconds and the second one took 4 milliseconds. This may differ time to time and machine to machine. But this is faster indeed.

See Video : http://www.youtube.com/watch?v=gH8DlcFsrvc

For more Details : http://msdn.microsoft.com/en-us/library/cc853327.aspx