This is going to be the tenth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here ,the seventh one here ,the eighth one here and the ninth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into how to improve performance in EF applications. I will be looking into how to precompile LINQ to Entities queries and see the performance gains from that.

We have to know our application very well and see what queries are executed repeatedly. Those are candidates for precompilation. We will use the public sealed class CompiledQuery.

For more information click here.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name. Add a button and a textbox on the the form.Leave the default names.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

public partial class _Default : System.Web.UI.Page
{
 
private static Func<CompanyEmployeesEntities, string, IQueryable<Employee>> 

theCompiledQuery =
CompiledQuery.Compile<CompanyEmployeesEntities, string, 

IQueryable<Employee>>((context, country) => from emp in context.Employees
                              where emp.Country == country
                              select emp);

As you see here I create a delegate.The Compile() method take 2 arguments. I pass as the first argument the instance of the ObjectContext object.Then I pass as a second argument a "string" because I filter on a string.

Then I have a Lambda expression which encapsulates a Linq to Entities query.The query just returns entities of type (Employee) according to the parameter it takes.

 7) Now we need to instantiate the ObjectContext object. In the Button1_Click() event handling routine type,

        try
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
 
 
            using (var context = new CompanyEmployeesEntities())
            {
 
                string mycountry = TextBox1.Text;
 
                var query = theCompiledQuery(context, mycountry);
 
                foreach (var item in query)
                {
                Response.Write(item.EmpFirstName + " " + item.EmpLastName);
                Response.Write("<br/>");
                }
                
                
            Response.Write("<br/>");
            Response.Write("<hr/>");
Response.Write("Time in milliseconds : " +   stopwatch.ElapsedMilliseconds);               

                stopwatch.Stop();
            }
 
        }
        catch (Exception ex)
        {
 
            Response.Write(ex.InnerException.Message);
        }

We need somehow to find the time ellapsed between consecutive requests.

Obviously the first request will not have any performance gains. Some time will be spent to compile the query.

I use the Stopwatch class.Add the following using statement in the Default.aspx.cs

using System.Diagnostics;

Then I just create an instance of that class and I start the time.I instantiate the context object and i get the parameter (country) from the textbox. Then I pass them to the theCompiledQuery delegate.

var query = theCompiledQuery(context, mycountry);

Then I iterate through the results and display them on the screen. Finally I find the elapsed time, and stop the watch.

8) Run your application. In my case , I enter in the textbox as a parameter-country="Norway" and I hit the button. This is what I get back

Julie Moreno
Mark Bolton

9) Type another value in the textbox e.g Sweden . This is what I get.

Sonya Ryan
Luke Torres
Erin Schmidt
Nakia Zavala
Cassie Dougherty

As you can see there is a significant performance gain even though I have a different value in the paremeter.

10)  Type another value in the textbox e.g Bulgaria . This is what I get.

Frances Caldwell
Dana Heath
Ericka Rivers
Quentin Harmon
Sandra Ware
Bethany Kent

As you can see there is a significant performance gain even though I have a different value in the paremeter.

Email me if you need the source code.

Hope it helps!!!