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.

A more deterministic approach to TempData in MVC

I just read the article of Greg Shackles on custom ITempDataProvider. I will follow up his blog post with my own slight adjustments and experiences of his implementation of a persistent customized TempDataProvider, using MongoDb.

The original article is here:
MongoDb based TempData provider

The default implementation of ITempDataProvider in MVC is the class SessionStateTempDataProvider. This temp data provider relies on SessionState.
As long as the end-user applies InProc as the SessionState in web.config,
TempData should work as expected. However, as soon as a move towards SqlServer
in SessionState of web.config is selected, things will start to crash..

The following is a sample implementation of how to implement a more deterministic and stable implementation, with the aid of the MongoDb object database.

The custom ITempDataProvider looks like this:

 public class CustomTempDataProvider : ITempDataProvider
    {
        private string _collectionName;
        private string _databaseName; 

        public CustomTempDataProvider(string databaseName, string collectionName)
        {
            _collectionName = collectionName;
            _databaseName = databaseName; 
        }

        #region ITempDataProvider Members

        public IDictionary LoadTempData(ControllerContext controllerContext)
        {
            var tempDataDictionary = new Dictionary();

            using (Mongo mongo = new Mongo("mongodb://127.0.0.1:27017"))
            {
                mongo.Connect();
                IMongoCollection collection = mongo.GetDatabase(_databaseName).
                    GetCollection(_collectionName);
                IEnumerable tempData = collection.Find(item => item.SessionIdentifier == controllerContext.HttpContext.Request.UserHostAddress).Documents;



                foreach (var tempDataItem in tempData)
                {
                    tempDataDictionary.Add(tempDataItem.Key, tempDataItem.Value);
                    collection.Remove(tempDataItem); 
                }
            }

            return tempDataDictionary; 
        }

        public void SaveTempData(ControllerContext controllerContext, IDictionary values)
        {
            string hostName = controllerContext.HttpContext.Request.UserHostName;
            using (Mongo mongo = new Mongo())
            {
                mongo.Connect();

                IMongoCollection collection = mongo.GetDatabase(_databaseName).
                    GetCollection(_collectionName);

                IEnumerable oldItems = collection.Find(item => 
                    (item.SessionIdentifier == hostName)).Documents;

                foreach (var tempDataItem in oldItems)
                {
                    collection.Remove(tempDataItem); 
                }

                if (values != null && values.Count > 0)
                {
                    collection.Insert(values.Select(tempDataValue => new MongoTempData
                    {
                        SessionIdentifier = controllerContext.HttpContext.Request.UserHostAddress,
                        Key = tempDataValue.Key,
                        Value = tempDataValue.Value
                    }));
                }
            }
            
        }

        #endregion
    }

Beware of the code above that the SessionIdentifier is set to the UserHostAddress of the request. Rather, you might want to inject the ASP.NET SessionId or something similar here instead to avoid multiple users accessing potentially the same data. This will both crash for the next User B since User A got to the data first (TempData removes its data after it is read). In addition User A gets to read User B TempData, which could of course include sensitive data. With that word of caution, I will leave to the read to select a good identifier for the TempData.

Next up, we create a base class for setting the TempDataProvider:

 public class CustomTempDataControllerBase : Controller
    {
        public CustomTempDataControllerBase()
        {
            TempDataProvider = new CustomTempDataProvider 
             ("test", "MongoTempData");                 
        }
    }

Our controllers do then only need to inherit from this base class (note that this base class inherits from the MVC class Controller):

  public class HomeController : CustomTempDataControllerBase
    {

        public ActionResult Index()
        {
            TempData["CurrentCity"] = "Trondheim";
            TempData["CurrentTemperature"] = "-7 Centigrade Windchill"; 
            return Content("TempData updated! Read the TempData here"); 
        }

        public ActionResult ReadTempData()
        {
            return View();
        }

    }

The view ReadTempData.cshtml looks like this (Razor code):
Contents of TempData:

@foreach (var tempDataItem in TempData)
{
    @tempDataItem.Key @:: @tempDataItem.Value
} 

That is basically all that is too it. To start up the MongoDb on your computer, download the binaries to e.g. c:\mongodb . The binaries are available from the site:
http://www.mongodb.org/

Start the database like this:
mongod --dbpath c:\mongodb\data

Next up, get the necessary binaries from the original source of this article:
http://www.gregshackles.com/2010/07/asp-net-mvc-do-you-know-where-your-tempdata-is/comment-page-1/#comment-2491

In addition I recommend the tool MongoVUE, a GUI for viewing the data inside your collections of this object oriented database (MongoDb is not a RDBMS).

Screenshot of our sample in MongoVUE:



The source code for the custom ITempDataProvider in this article is here:
Mongo Db based custom Temp Data Provider for MVC

Razor extension method Each

It is possible to augment razor views with not only helpers, but also razor extension methods.

In this example, an extension method which will take a template and from this template generate a rendered result of a list of the IEnumerable of items passed in for the extension method will be presented. It is named Each and was created by Phil Haack.

I will present the source code quickly to just repeat Phil Haack's code. Let's first add a new class called IndexedItemModel:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestActionMethodSelectorAttribute.Extensions
{
    public class IndexedItem<TModel>
    {
        public IndexedItem(int index, TModel item)
        {
            this.Index = index;
            this.Item = item; 
        }

        public int Index { get; private set; }
        public TModel Item { get; private set; }

    }
}

Let's add the extension method next:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.WebPages;

namespace TestActionMethodSelectorAttribute.Extensions
{

    public static class RazorCollectionExtensions
    {

        public static HelperResult Each(this IEnumerable<TItem> items,
            Func<IndexedItem<TItem>, HelperResult> template)
        {
            return new HelperResult(
                writer =>
                {
                    for (int i = 0; i < items.Count(); i++)
                    {
                        template(new IndexedItem<TItem>(i, items.ElementAt(i))).WriteTo(writer);
                    }
                }
            );
        }

    }

}

An example of its usage is shown next in a view:

@{
    ViewBag.Title = "Home Page";

    var cars = new []
    {
       new { name = "Audi", color = "Blue", make = "A4" },
       new { name = "BMW", color = "Black", make = "M5" },
       new { name = "Volvo", color = "Red", make = "240" },
       new { name = "Renault", color = "Black", make = "19" }     
    }; 
    
}

@cars.Each(@<li>Car is an @item.Item.name @item.Item.make of color @item.Item.color</li>)

Take note of the "magic" @item property in use here. What we see here in effect is a convenient way to iterate over a list of objects, in this case an array of anonymous objects which we call "cars" and we just add in some car objects to this array using a collection initializer. Then we output this list with our new Each method which is a razor extension method and specify our template. You need to prefix the first letter in the parenthesis with the @ sign, then use @item to refer to the object. In our Each method we use IndexedModel type for each item in the list. This got both an index and an Item of type TModel (TItem) passing in our item type, in this case the anonymous type that constitutes the "cars" object.

Thanks to Phil Haack for this nice method! I like this Each method a lot. No need to write that long @foreach razor helper any more. This also should fit nicely into linq expressions like:
@Model.MyList.Where(myItem => myItem.Age > 19).Each(@
<li>@item.Name </li>)
to output a list of the name propery for all people above 19 years old in our Model.MyList property, just to take an example.

This nice method was presented by Phil Haack here:



Note for those viewing this video, not everything went smooth for Phil Haack in this demonstration, however there were lots of "hidden gems" he presented there at the MIX11 conference in Las Vegas.

Disclaimer: I added this blog post to extract the source code from his demo into an easily available sample.

It should be relatively easy now to start creating Razor extension methods as an alternative to HTML helpers and Razor html helpers in MVC by taking a look at this example. Note the use of HelperResult and WriteTo (corresponding Response.Write in ASP.NET).