Posts Tagged 'LINQ'

Precompiling LINQ Queries in .NET Framework 4.5

While it is possible to precompile LINQ queries for Entity Framework either to have your queries run faster (either without parameters or with parameters) or to run your queries on a background thread, in .NET Framework 3.5/4, it’s neither pretty nor fun. The code isn’t particularly ugly but it does require some additional effort for you to have your queries precompiled. Unless you want to run your statement on a background thread, you’ll only benefit from precompiling if you execute the same LINQ statement many times (and, even then, if your queries aren’t very complex then you still may not get much benefit).

However, there is some benefit from precompiling and it would be nice to have it, provided you didn’t have to do any extra work. That’s where .NET Framework 4.5 steps in. While it’s always dangerous to talk about what’s supposed to be in an upcoming version of some software, the intent is for the framework to recycle your compiled LINQ queries (a feature called “auto-compiled LINQ queries”). Every time a LINQ statement is issued, the framework will build the statement’s expression tree (the first step in the compilation process) and generate a unique identifier for the statement. The framework will then search a cache to see if that identifier is already present. If the framework doesn’t find the identifier, the framework finishes the compilation process and adds the compiled version of the statement to the cache before executing it; if the framework finds the identifier, it uses the already compiled version of the statement.

Notice what’s happening here: You still have to pay for the statement’s expression tree to be generated but, once you’ve paid that cost, if your statement or one very much like it already exists in the cache then all subsequent compile steps are skipped and your statement is just executed. If you have the identical LINQ statement in several places in your application then there’s no need—from an efficiency point of view—to rewrite your application to have that LINQ statement appear only once. The cache will store only one version of the statement and use it from all the places it appears in your application (of course, you’d want to have a good reason for scattering an identical LINQ expression throughout your code). As an added benefit, statements are only compiled if they are used. The older precompile method meant that you could precompile a LINQ statement that you might never use.

You don’t have access to the compiled queries in the cache (at least, not yet) so you’ll need to continue using CompiledQuery if you want to execute your LINQ statement on a background thread.

Turning Off Auto-Compile

If you don’t want to have your statements precompiled, you can turn the feature off by setting the DefaultQueryPlanCachingSetting on the ObjectContext’s ContextOptions property to false, like this:

oc.ContextOptions.DefaultQueryPlanCachingSetting = false;

I can only see two scenarios when turning off this option would be a good thing. Turning off auto-compile would eliminate whatever cost is involved in generating the identifier and walking the cache but, I’m willing to assume, that will be more than offset by not compiling a LINQ statement more than once. If you have a LINQ statement which is only executed once (and, furthermore, if this is true of all the LINQ statements associated with this instance of the ObjectContext) then setting this option to false would probably be a good choice. In this scenario, there’s also no benefit in using the CompiledQuery class to precompile your queries.

The second scenario involves sticking with the CompiledQuery class. Because there is a cost associated with maintaining and searching the cache, using the CompiledQuery class should give you better performance than the auto-compile option—no need to generate an identifier or maintain the cache (provided, of course, you don’t precompile statements you never use). If you’re obsessed with performance and don’t mind the extra work (or are paid by the hour) you could stick with the CompiledQuery class, even in .NET Framework 4.5. In this scenario, you may need to turn off DefaultQueryPlanCachingSetting to prevent the framework from also maintaining its cache of auto-compiled statements.

One last note: This does mean that if you’re using the CompiledQuery class in your existing code then, when you upgrade to .NET Framework 4.5, you’ll actually be getting better performance than the programmers who count on the framework’s default behaviour.

Peter Vogel

Calling LINQ Queries in the Background

In my last two posts (here and here), I showed how you can pre-compile a LINQ query to improve your performance. As I noted in those posts, I wouldn’t expect a blinding improvement in response times—your application probably doesn’t have many really complex queries and, as a result, probably doesn’t spend a lot of time converting LINQ into SQL.

What I didn’t say explicitly is that what the pre-compile process does is generate a delegate—which means that, once you’ve pre-compiled your LINQ statement, you can call it asynchronously, as you would with any other delegate. If you have a long running query, you might prefer to have your LINQ statement execute asynchronously so that your user interface isn’t tied up while you wait for results to be returned (or so your application can go and do something else). While simply pre-compiling your LINQ statements won’t save you a lot of time, running your statements asynchronously is a change that could give you a more responsive application.

Assuming you’re familiar with the material in the previous posts, at this point you’ll have a variable called qry that holds a reference to a pre-compiled LINQ statement. To execute the LINQ statement and catch its results (a collection of Northwind Order objects for a specific customer), you’d call the compiled query’s Invoke method, passing whatever parameters are required. For my example, which requires the LINQ statement to be passed its ObjectContect and a customer Id, that code looks like this:

Dim oc As New northwndModel.northwndEntities

Dim res = qry.Invoke(oc, “ALFKI”)

For Each ord inres

The problem here is that, even with the SQL statement already generated, you still have to wait for Entity Framework to open a connection to the database, send the SQL, wait for the data to be retrieved, and (finally) for the database connection to be closed. Your application could be doing something else if you executed the statement asynchronously.

Executing Asynchronously

To execute your LINQ statement asynchronously, you call the BeginInvoke method on the variable holding the compiled statement instead of using the Invoke method. The parameters passed to the BeginInvoke method are the same as those passed to the Invoke method with the addition of parameter specifying a method to call when the LINQ query completes (and a fourth parameter set to Nothing).

This example, for instance, specifies that the method ProcessOrders is to be called automatically when the LINQ statement completes:

qry.BeginInvoke(en2, “ALKFI”, AddressOf ProcessOrders, Nothing)

The ProcessOrders method must accept as its only parameter an IAsyncResult object. To retrieve the result of the LINQ statement, you call the EndInvoke method on the variable holding your compiled LINQ query, passing that IAsyncResult object. The EndInvoke method will return the result of your LINQ query. This example extracts the collection of Orders generated by the LINQ statement:

Public Sub ProcessOrders(iar As IAsyncResult)
Dim ords = qry.EndInvoke(iar)

End Sub

A LINQ statement is “lazy loaded”—the actual data isn’t retrieved until you process the results from the LINQ statement. You should probably alter the lambda expression holding your LINQ statement to ensure that the data is retrieved on the background thread. You can do that by converting the results of your compiled statement into a List before returning it. If you’re doing that conversion, you should change the data type of the final data type passed to the Compile method since it represents the type being return from the expression:

Dim qry = System.Data.Objects.CompiledQuery.Compile(
Of northwndModel.northwndEntities,


List(Of northwndModel.Order)) _
(Function(en, custid)
(From o In en.Orders
Where o.CustomerID = custid

Select o).ToList)

There are trade-offs to be made here. If, for instance, you only process some of the Orders retrieved by this statement then whatever benefits you may gain by running asynchronously may be lost by retrieving too much data.

I mentioned in my last post which Learning Tree course had the best coverage of LINQ. I should also have mentioned the course that I think does the best job on Entity Framework: Learning Tree’s Design Patterns and Best Practices. A course, by the way, that I think every .NET developer should take.

Peter Vogel

PreCompiling LINQ Queries with Parameters

In my last post, I looked at precompiling a LINQ query to speed up (at least a little bit) your application. The LINQ statement I used as my example was very simple:

Dim res = From o In en.Orders
          Select o

This post looks at a more realistic example: a LINQ statement that has a Where clause. But I’ll also look at how to declare the variable holding the precompiled LINQ statement so that you can put your compiled statement to good use.

In my last post, I created a LINQ statement that had to be passed only the ObjectContext that it would use. In real life, most LINQ statements will require more than that to be passed to them. For instance, this LINQ statement retrieves only the orders for the customer number that’s specified in the variable called “custID”:

Dim res = From o In en.Orders
          Where o.CustomerID = custId
          Select o

Effectively, that custId variable is a parameter that must be passed to the pre-compiled LINQ statement when the statement is executed.

If your statement does require parameters then, when compiling it, you must specify the parameter in two places:

  • In the data type list used by the Compile method: You must specify the data type of the parameter
  • In the parameter list for the lambda function: You must specify the name that will be used in the LINQ query to refer to the parameter

Enhancing the Compile method example from my last post to precompile my Where-clause query to accept the custId string parameter gives this code:

Dim qry = System.Data.Objects.CompiledQuery.Compile(
                    Of northwndModel.northwndEntities,
                    IQueryable(Of northwndModel.Order)) _
(Function(en, custid) From o In en.Orders
                                Where o.CustomerID = custid
                                Select o)

Calling the LINQ statement and passing both parameters (the ObjectContext and a customer Id) would look like this:

Dim oc As New northwndModel.northwndEntities
Dim res = qry.Invoke(oc, “ALFKI”)

For this technique to be useful, though, you’ll want to set the qry variable to the precompiled query at the start of your application and then invoke the query some other method (or in several other methods). As a result, you’ll probably want to declare the variable holding the compiled query outside of any method. You must declare the variable using the Func type—another generic type that must be told what data types it’s working with. Fortunately, you can just copy declaration you passed to the Compile method to define the qry variable.

For the last example with the string parameter, the declaration of the qry variable would look like this:

Dim qry As Func(Of northwndModel.northwndEntities,
                IQueryable(Of northwndModel.Order))

Now, at the start of your application you can compile your LINQ statements into a set of variables and, later in your application, use the variable’s Inovke method to execute the statement.

Peter Vogel

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


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: