Speeding up Entity Framework queries with CompiledQuery

If you use Entity Framework, you may have experienced tha EF is quite slow when it comes to more complex queries against the database, because the EF query must be first compiled into SQL before it is executed to populate the EF data objects of the result set. It is possible to speed up these EFqueries, by using Compiled Queries in Entity Framework (EF). I will explain how this is done by following the MSDN page with examples of how to achieve this next, MSDN page with Compiled Queries examples

First off, not every case will benefit from a compiled query. If the query is simple or not easy parametrized, using a compiled query is not adviced. However, more often than not, you will want to compile the query first and store off that query in a static readonly variable (at least this is what is done in the examples), so that we can reuse the compiled query variable by invoking it. Let's investigate an example of how to perform such compiled query in Entity Framework.

The following code is a complete example of how to perform compiled queries:

class Program
    {

        static readonly Func<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>> s_compiledQuery =

 CompiledQuery.Compile<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>>(
             (ctx, myparams) => from sale in ctx.SalesOrderHeader
                             where sale.ShipDate > myparams.startDate && sale.ShipDate < myparams.endDate
                             && sale.TotalDue < myparams.totalDue
                             select sale); 

        static void Main(string[] args)
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                IQueryable<SalesOrderHeader> sales = s_compiledQuery.Invoke(context, new MyParams
                {
                     startDate = new DateTime(2003, 3, 3),
                     endDate = new DateTime(2003, 3, 8),
                     totalDue = 700.00M
                });
                foreach (SalesOrderHeader sale in sales)
                {
                    Console.WriteLine("ID: {0} Order date: {1} Total due: {2}",
                        sale.SalesOrderID,
                        sale.OrderDate,
                        sale.TotalDue); 
                }   
                Console.WriteLine(string.Format("Number of rows found: {0}", sales.Count())); 
            }
        
            Console.WriteLine("Press any key to continue...");
            Console.ReadKey(); 
        }
    }

At the top of this example code, we create a static readonly variable of type Func with the following parametres, first off AdventureWorksEntities (which is a type parameter like the rest). This is the type of the ObjectContext used in the AdventureWorks database, which I guess many of you already know about. It is possible to install the AdventureWorks on your local Sql Server 2008 (Express) R2 database, if you want to try it out. Check out this url for more information about AdventureWorks:
Download AdventureWorks sample database .

Further on, we pass in a MyParams type. It is a struct in this case (it could be a class), which we use in this example for demonstrating purposes. The overload to CompiledQuery.Compile method takes up to 16 type parameters, if you use a parameter object instead, you can provide multiple parametres. The MyParams type looks like this:

    
    struct MyParams
    {

        public DateTime startDate { get; set; }

        public DateTime endDate { get; set; }

        public decimal totalDue { get; set; }
    }

Let's have a look at the declaration of the compiled query again:

   static readonly Func<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>> s_compiledQuery =

 CompiledQuery.Compile<AdventureWorksEntities, MyParams, IQueryable<SalesOrderHeader>>(
             (ctx, myparams) => from sale in ctx.SalesOrderHeader
                             where sale.ShipDate > myparams.startDate && sale.ShipDate < myparams.endDate
                             && sale.TotalDue < myparams.totalDue
                             select sale); 

Make a note that we use the Func type to store the compiled query. The LAST parameter is the return type of the delegate that we use, which is the right hand side of the declaration above. The return type is IQueryable of type SalesOrderHeader. This must of course match the entity framework query that is used in the right hand side. If you use an ordering expression in the EF query (orderby), the return type will be IOrderedQueryable instead. Further on, we use a lamba expression and use two variables on the left of the goes to operator => , which for some will be a bit uncommon, as most are used to x => x.MyValue and so on. It is possible to specify more than two variables also here, if you use 'index', that has a special meaning (index within the result set).. Anyways, the injection of the parameters are defined indirectly on the left hand side and then used in the EF query on the right side of the expression.

The data is fetched from the database by invoking the compiled query with the Invoke method of the Func which is assigned at the top. This will actually fetch the data. The result set is stored into a variable of type IQueryable<SalesOrderHeader> , if you want to use ordering in the EF query, use IOrderedQueryable instead, it is also possible to used grouping in the EF query of a compiled query, use IGroupedQueryable then (example not included..)

       IQueryable<SalesOrderHeader> sales = s_compiledQuery.Invoke(context, new MyParams
                {
                     startDate = new DateTime(2003, 3, 3),
                     endDate = new DateTime(2003, 3, 8),
                     totalDue = 700.00M
                });

The compiled query is invoked and MyParams is specified inline here by using a constructor initializer (this is a struct which is used, however it is possible to use a class instead, if desired), the context is also passed. The invoke method of the Func will use the compiled query and invoke the query to the database and fetch the dataset using the compiled query.

If you use EF to build reports or other more complex queries, the use of a compiled query is highly recommended. It could be possible to use directly SQL, but that will mean that you will loose the support of refactoring (directly querying the database skips the overhead of translating an Entity Framework query into a sql expression).

This is therefore a highly recommended approach to performing faster Entity Framework queries against your database. Check out the MSDN page for more resources, I have not done any performance testing on this feature, but I feel it looks very promising.

Share this article on LinkedIn.