Posts Tagged 'CompiledQuery'

Pre-Compile LINQ to Entities Queries for Faster Processing

When you issue a LINQ statement against an Entity Framework model, LINQ and Entity Framework conspire together to generate an SQL statement that’s sent to the database engine. The problem is that, if you execute that the same LINQ statement twice, that conversion is performed twice. For a statement executed more than once, especially if it’s complicated, you’d prefer to do the conversion only once.

Starting with the .NET Framework 3.5 you can precompile your query by passing it to the Compile method of the CompiledQuery class. While this technique will save you time, you shouldn’t expect a blindingly fast improvement in your application’s response time—Entity Framework probably isn’t spending a lot of time converting your LINQ statements to SQL. This technique will, however, make possible some additional opportunities that I’ll discuss in later posts.

Here’s how to work this magic, starting with a LINQ statement that uses no parameters. This statement retrieves every Order in the Northwind database:

A simple LINQ query

Even in a statement this simple, you’ll need to pass your pre-compiled LINQ statement the ObjectContext object it uses (the northwndEntities object in this example). And, of course, after the statement executes, you’d like to get access to the Orders collection the statement returns.

You pre-compile your LINQ statement by passing it to the Compile method of the CompiledQuery object. The Compile method is a generic method so you have to tell it the data types of the objects it will be dealing with. In this case, that’s the inbound parameter (the ObjectContext) and the outbound collection returned by the LINQ statement. That collection will implement the IQueryable interface which is, itself, a generic type, so you’ll also need to specify the data type of the Entity Framework object being returned in the collection. So the initial part of calling the Compile method, which specifies the data types, looks like this:


Passing data types to the Compile method

After specifying the data types of the parameters the Compile method must deal with, you actually only pass the method one parameter: A lambda function that accepts the inbound ObjectContext and holds your LINQ statement:

Passing the LINQ statement to the Compile method

Putting it all together, this example compiles a LINQ statement that is passed a nortwndEntities object (the ObjectContext object) and returns a collection of northwndModel.Order objects. The lambda method that holds the LINQ statement accepts, as a parameter, the ObjectContext required by the LINQ query. The compiled LINQ statement is returned to be held in a variable (in this case, a variable called “qry”):

The full Compile method, setting a variable

You can now retrieve the query by calling the Invoke method on the variable, passing any required parameters. This example passes in the ObjectContext and catches the result:

Using the variable's invoke method, passing the ObjectContext

Of course that doesn’t addressing handling statements with multiple parameters (e.g. with a Where clause) but I’ll come back to that in my next post (along with how to declare that qry variable). After that, I’ll look at leveraging this feature to call LINQ queries on a background thread and wrap up by looking at what’s coming in .NET 4.5.

Peter Vogel


Learning Tree International

.NET & Visual Studio Courses

Learning Tree offers over 210 IT training and Management courses, including a full curriculum of .NET training courses.

Free White Papers

Questions on current IT or Management topics? Access our Complete Online Resource Library of over 65 White Papers, Articles and Podcasts

Enter your email address to subscribe to this blog and receive notifications of new posts by e-mail.

Join 29 other followers

Follow Learning Tree on Twitter

Archives

Do you need a customized .NET training solution delivered at your facility?

Last year Learning Tree held nearly 2,500 on-site training events worldwide. To find out more about hosting one at your location, click here for a free consultation.
Live, online training

%d bloggers like this: