This is the third in a series of posts on using ASP.NET MVC with Linq to Sql:

• Part 1 - DataContext Per Request
• Part 2 - AutoCommit and the RoutePreParser
• Part 3 - DataLoadOptions per Request

Code for this series is available here.

One common problem when using an ORM is the issue of "select n+1". For example, take the following Linq to Sql query:

var context = new BlogDataContext();
var posts = context.Posts;
 
foreach(var post in posts) {
   foreach(var comment in post.Comments) {
     var commenter = comment.Commenter.Name;
     Console.WriteLine("User {0} commented on post {1}", commenter, post.Title);
   }
}

This might look fine on the surface, but by default Linq to Sql will lazily-load all associations. This means that for each post loaded from the database Linq to Sql will issue a query to load the comments, and for each comment Linq to Sql will issue another query to load the commenter. So if you have 10 posts, and each post has 5 comments then you'll end up making 61 queries to the database (1 for the posts, 1 for each post to get the comments, and 1 for each comment to get the user)

This can be mitigated by using DataLoadOptions to eagerly load all of the data in a single query:

var context = new BlogDataContext();
 
var options = new DataLoadOptions();
options.LoadWith<Post>(p => p.Comments);
options.LoadWith<Comment>(c => c.Commenter);
context.LoadOptions = options;
 
var posts = context.Posts;
foreach(var post in posts) {
 //...
}

The Problem

However, once a query has been executed, the LoadOptions property of a DataContext is frozen - you cannot then specify any additional eager loading paths.*

If you're using a DataContext per request as per my previous post then this can be a problem. For example, imagine you have the following controller action:

public class PostsController : Controller {
  private BlogDataContext context;
 
  public PostsController(BlogDataContext context) {
     this.context = context; //injected via our IoC container
  }
 
  public ActionResult Show(int id) {
    var options = new DataLoadOptions();
    options.LoadWith<Post>(p => p.Comments);
    options.LoadWith<Comment>(c => c.Commenter);
 
    context.LoadOptions = options;
 
    var post = context.Posts.Single(p => p.Id == id);
    return View(post);
  }
}

...then this will work as expected. But now imagine your action is decorated by an ActionFilter:

[LoadCurrentUser]
public ActionResult Show(int id) { 
  //...
}

...and let's assume that our LoadCurrentUser filter attempts to load the details of the current user from the database and store them in ViewData:

public class LoadCurrentUserAttribute : ActionFilterAttribute {
	public override void OnActionExecuting(ActionExecutingContext filterContext) {
		var context = ObjectFactory.GetInstance<BlogDataContext>();
		var identity = filterContext.HttpContext.User.Identity;
 
		if (identity.IsAuthenticated) {
			var currentUser = context.Users.Single(x => x.UserName == identity.Name);
			filterContext.Controller.ViewData["CurrentUser"] = currentUser;
		}
	}
}

Now when the controller is executed we will get the following error:

Setting load options is not allowed after results have been returned from a query.

This happens because we're trying to set the LoadOptions for the Post after the LoadCurrentUser filter already executed a query.

The Solution

The approach I use to work around this problem is to use a custom AuthorizationFilter in conjunction with an eager loading specification.

First, we define an interface for eager loading specifications:

public interface IEagerLoadingSpecification {
  void Build(DataLoadOptions options);
}

Next, we can write some classes that implement IEagerLoadingSpecification that define particular specifications for eager loading. Using the example above, we have PostWithComments and CommentWithCommenter:

public class PostWithComments : IEagerLoadingSpecification {
  public void Build(DataLoadOptions options) {
    options.LoadWith<Post>(p => p.Comments);
  }
}
 
public class CommentWithCommenter : IEagerLoadingSpecification  {
  public void Build(DataLoadOptions options) {
     options.LoadWith<Comment>(c => c.Commenter);
  }
}

Next, we write an EagerlyLoadAttribute which implements IAuthorizationFilter and takes an array of Types in its constructor:

public class EagerlyLoadAttribute : FilterAttribute, IAuthorizationFilter {
  private Type[] types;
 
  public EagerlyLoadAttribute(params Type[] types) {
    this.types = types;
  }
 
  public void OnAuthorization(AuthorizationContext filterContext) {
    var loadOptions = new DataLoadOptions();
    var context = ObjectFactory.GetInstance<BlogDataContext>();
 
    foreach(var type in types) {
       if(! typeof(IEagerLoadingSpecification).IsAssignableFrom(type)) {
          throw new InvalidOperationException(string.Format("Type {0} does not implement IEagerLoadingSpecification", type));
       }
 
      var spec = (IEagerLoadingSpecification)Activator.CreateInstance(type);
      spec.Build(loadOptions);
    }
 
   context.LoadOptions = loadOptions;
  }
}

So what does this filter do? Firstly, because it implements IAuthorizationFilter this means it will be invoked before any action filters that decorate your controller action. When OnAuthorization is invoked, it loops over each of the Types that we've passed to its constructor, instantiates them and casts them to IEagerLoadingSpecification. Next, a single DataLoadOptions instance is passed to each of the specifications in turn so they can build up the required eager loading paths.

The end result is that you can now define your actions like this:

  [EagerlyLoad(typeof(PostWithComments), typeof(CommentWithCommenter))]
  [LoadCurrentUser]
  public ActionResult Show(int id) {
    var post = context.Posts.Single(p => p.Id == id);
    return View(post);
  }

Because the EagerlyLoadAttribute is an IAuthorizationFilter, it will be invoked before anything else meaning that by the time both our LoadCurrentUser filter and the Show action are invoked the DataLoadOptions have already been set on our DataContext.

* Note that other ORMs like NHibernate, LLBLGen and the Entity Framework don't have this problem because they allow eager loading paths to be specified at the query level.