LINQ Exchange

If you are using Linq to SQL, instead of writing regular Linq Queries, you should be using Compiled Queries.
if you are building an ASP.NET web application that’s going to get
thousands of hits per hour, the execution overhead of Linq queries is
going to consume too much CPU and make your site slow. There’s a
runtime cost associated with each and every Linq Query you write. The
queries are parsed and converted to a nice SQL Statement on *every*
hit. It’s not done at compile time because there’s no way to figure out
what you might be sending as the parameters in the queries during
runtime. So, if you have common Linq to Sql statements like the
following one throughout your growing web application, you are soon
going to have scalability nightmares:

var query = from widget in dc.Widgets
where widget.ID == id && widget.PageID == pageId
select widget;
var widget = query.SingleOrDefault();

There’s a nice blog post by JD Conley that shows how evil Linq to Sql queries are:

You see how many times SqlVisitor.Visit is called to convert
a Linq Query to its SQL representation? The runtime cost to convert a
Linq query to its SQL Command representation is just way too high.

Rico Mariani has a very informative performance comparison of regular Linq queries vs Compiled Linq queries performance:

Compiled Query wins on every case.

So, now you know about the benefits of compiled queries. If you are
building ASP.NET web application that is going to get high traffic and
you have a lot of Linq to Sql queries throughout your project, you have
to go for compiled queries. Compiled Queries are built for this
specific scenario.

In this article, I will show you some steps to convert regular Linq
to Sql queries to their Compiled representation and how to avoid the
dreaded exception “Compiled queries across DataContexts with different LoadOptions not supported.”

Here are some step by step instruction on converting a Linq to Sql query to its compiled form:

First we need to find out all the external decision factors in a
query. It mostly means parameters in the WHERE clause. Say, we are
trying to get a user from aspnet_users table using Username and Application ID:

Here, we have two external decision factor – one is the Username and
another is the Application ID. So, first think this way, if you were to
wrap this query in a function that will just return this query as it
is, what would you do? You would create a function that takes the DataContext (dc named here), then two parameters named userName and applicationID, right?

So, be it. We create one function that returns just this query:

Next step is to replace this function with a Func<> representation that returns the query. This is the hard part. If you haven’t dealt with Func<> and Lambda expression before, then I suggest you read this and this and then continue.

So, here’s the delegate representation of the above function:

Couple of things to note here. I have declared the delegate as static readonly
because a compiled query is declared only once and reused by all
threads. If you don’t declare Compiled Queries as static, then you
don’t get the performance gain because compiling queries everytime when
needed is even worse than regular Linq queries.

Then there’s the complex Func<DropthingsDataContext, string, Guid, IQueryable<aspnet_User>> thing. Basically the generic Func<> is declared to have three parameters from the GetQuery function and a return type of IQueryable<aspnet_User>. Here the parameter types are specified so that the delegate is created strongly typed. Func<> allows up to 4 parameters and 1 return type.

Next comes the real business, compiling the query. Now that we have the query in delegate form, we can pass this to CompiledQuery.Compile
function which compiles the delegate and returns a handle to us.
Instead of directly assigning the lambda expression to the func, we
will pass the expression through the CompiledQuery.Compile function.

Here’s where head starts to spin. This is so hard to read and
maintain. Bear with me. I just wrapped the lambda expression on the
right side inside the CompiledQuery.Compile function. Basically that’s the only change. Also, when you call CompiledQuery.Compile<>, the generic types must match and be in exactly the same order as the Func<> declaration.

Fortunately, calling a compiled query is as simple as calling a function:

There you have it, a lot faster Linq Query execution. The hard work
of converting all your queries into Compiled Query pays off when you
see the performance difference.

Now, there are some challenges to Compiled Queries. Most common one
is, what do you do when you have more than 4 parameters to supply to a
Compiled Query? You can’t declare a Func<> with more than 4 types. Solution is to use a struct to encapsulate all the parameters. Here’s an example:

Calling the query is quite simple:

Now to the dreaded challenge of using LoadOptions with Compiled Query. You will notice that the following code results in an exception:

The above DataLoadOption runs perfectly when you use regular
Linq Queries. But it does not work with compiled queries. When you run
this code and the query hits the second time, it produces an exception:

Compiled queries across DataContexts with different LoadOptions not supported

A compiled query remembers the DataLoadOption once its called. It does not allow executing the same compiled query with a different DataLoadOption again. Although you are creating the same DataLoadOption with the same LoadWith<>
calls, it still produces exception because it remembers the exact
instance that was used when the compiled query was called for the first
time. Since next call creates a new instance of DataLoadOptions, it does not match and the exception is thrown. You can read details about the problem in this forum post.

The solution is to use a static DataLoadOption. You cannot create a local DataLoadOption instance and use in compiled queries. It needs to be static. Here’s how you can do it:

Basically the idea is to construct a static instance of DataLoadOptions using a static function. As writing function for every single DataLoadOptions
combination is painful, I created a static delegate here and executed
it right on the declaration line. This is in interesting way to declare
a variable that requires more than one statement to prepare it.

Using this option is very simple:

Now you can use DataLoadOptions with compiled queries.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.