While using entity framework, it is very common to write a method and call it multiple times from different modules/sections of the applications. For ex: in a application, we may be fetching the list of States/Cities etc. So when we write a linq query to perform this operation, behind the scenes, this linq query is first checked for any syntax error, then it is converted into its SQL equivalent and finally executed in the database to fetch the results.
So imagine the situation, when this type of query is fired every-time, for each request it receives. The type of data it returns could be a list of thousands of records at a time. To add to the overhead of this operation, linq query will be first parsed, then converted into sql and finally executed in the database. So it can result in performance issues. One thing we could have done is using the stored procedures to handle this situation. Another alternative is to use compiled queries. So let’s discuss the second approach.
What is a Compiled query in LINQ ?
A compiled query is a cached version of the actual query, that we use to get the results. We write the query. For the first time it is parsed or verified for any kind of syntax error in linq, then converted into SQL version and is added into cache. So every subsequent request for this query, cached version of this query is used and thus avoids parsing and conversion of the linq query into its sql version.
Creating a compiled linq query
To create a compiled query, we have a static method named Compile in System.Data.Objects.CompiledQuery namespace. It is a static function with 16 overload versions & it returns a generic Func delegate type. See the list of its definitions.
There are some important points to be noted here :
- In the above definitions list, it specifies that TArg0 must be of type ObjectContext. This means that the first parameter passed to this method should be an instance or context of our entities which we are using.
- TArg1…TArgn is the list of any other input parameters that are used to query the results or we can say, add to the filter condition of the query.
- TResult is the final result or output that we expect from the query. It could be the list of cities or users etc. from the database.
- The delegate variable we use to cache the query, will have the same signature as that of the Compile method, as it will act as a pointer to the function or we can say it is basically the return type of the method. Its like we have INT32 return type of a function and storing it in an INT32 type static variable.
- The delegate variable which this method returns must be declared as static. By making this variable static, the sql version of the query is stored or cached in this variable. Any further requests to this query are then handled using this static variable.
An example to create a compiled LINQ query :
Let’s say we want to get the list of all the users of our application, who have their Id greater than 50. Our first step will be to write the Compile method, with signatures, having 2 input parameter of type context of entities and an integer type variable and returns us the list of the users. So our method will look like the following :
By comparing the above code with the overload definitions of the Compile method(previous image), we can see that we have TArg0 as our object context of entities, TArg1 is a filter condition and TResult is the output of the query or List of the Users, in our example. Like TArg0 and TArg1, we can have up-to TArg15 as input parameters in the query.
Next, we will store the returned values in a delegate variable with same signatures, as that of our Compile method, returning the list of users. Make sure that the variable we declare is of static type. So our code becomes :
This is it. Now we just need to create a method in order to call this query again and again. In our case we will add a method named GetUsersByCompiledQuery. This will invoke our delegate, which will further execute the Compile method.
The point to note here is that for the very first time, this query may also take same amount of time for execution, as it would have done in case of normal linq query. But subsequent calls will take much less time then the first call, as it will be already compiled by then and its cache plan will be used. Another point to be mentioned, as per MSDN, Starting with the .NET Framework 4.5, LINQ queries are cached automatically.
So this was about the concept of Compiled Query in LINQ. Hope you enjoyed reading it.