One of the most common questions I get when people see a video or presentation about how I build MVC applications is, “Why don’t you use the repository pattern?”  There are a lot of reasons not to use the repository pattern, and they’ve been well-covered by others.  But, one valid benefit they provide is encapsulation of query logic.  In this post, I’ll show you how you can maintain encapsulation in your applications without having to use the repository pattern.

[more]

Let’s first talk about why we would want to encapsulate our queries.  Let’s assume we’re working with the popular blog domain, so we have blog posts, authors, and comments:

Now let’s assume we want to have a page that shows our posts that were popular enough that someone from Microsoft actually responded to them.  To do that, we need to traverse from our posts down to our comments, and include only those posts where one of the commenters has an ‘@microsoft.com’ address. 

We could do that using LINQ to EF:

var startDate = DateTime.Parse("6/1/2015");
var endDate = DateTime.Parse("7/1/2015");
var topPosts = _context
    .Posts.Where(x => x.PostDate >= startDate && x.PostDate < endDate &&
                  x.Comments.Count() >= 3
                  && x.Comments.Any(c => c.Email.Contains("@microsoft")))
    .Select(x => new TopPostModel
    {
        Title = x.Title,
        PostDate = x.PostDate,
        CommentCount = x.Comments.Count(),
        MicrosoftPosterAddresses =
            x.Comments.Where(c => c.Email.Contains("@microsoft.com")).Select(c => c.Email).ToList()
    })
    .ToArray();

I hope you can agree that this code is hideous. 

Yes, this example is contrived, sure, but I’ve (sadly) written LINQ queries that are far more complex than that.

With a repository, we could encapsulate this and sweep it under the rug, like so:

var topPosts = _postRepo.GetTopPostsForDateRangeWithMSContributors("6/1/2015", "7/1/2015")

But, the repository abstraction isn’t without cost, namely that we lose easy access to all the underlying capabilities that Entity Framework (or NHibernate, if that’s your bag) provides.

What’s a developer to do?

Solution 1: Use AutoMapper

I’m a huge user (abuser?) of AutoMapper.  Jimmy Bogard has done more for my productivity than anyone except for maybe Jeremy Miller.  If you aren’t using it, it’s time to start.

Aside from being great at simply moving data from one object to another, AutoMapper has had the ability to simplify your database access code for quite some time now, thanks to its IQueryable Extensions.  You see, AutoMapper can be used with your favorite LINQ provider, “projecting” your mappings down to LINQ clauses, which are then translated down to actually database queries. 

Using AutoMapper’s queryable extensions, we can simplify the above query into this:

var topPosts = _context
    .Posts.Where(x => x.PostDate >= startDate && x.PostDate < endDate &&
                      x.Comments.Count() >= 3
                      && x.Comments.Any(c => c.Email.Contains("@microsoft")))
    .Project().To<TopPostModel>()
    .ToArray();

We no longer need to do the Select portion of our query.  AutoMapper will do that for us.  We still have an ugly Where clause… more on that in a minute. 🙂

So, where did that Select go?  Into our mapping configuration!  I’m using Heroic.AutoMapper, which enables specifying your mapping in the view model, so my view model now looks like this:

public class TopPostModel : IMapFrom<Post>, IHaveCustomMappings
{
    public string Title { get; set; }

    public DateTime PostDate { get; set; }

    public int CommentCount { get; set; }

    public IList<string> MicrosoftPosterAddresses { get; set; }
    
    public void CreateMappings(IConfiguration configuration)
    {
        configuration.CreateMap<Post, TopPostModel>()
            .ForMember(x => x.CommentCount, opt => opt.MapFrom(x => x.Comments.Count()))
            .ForMember(x => x.MicrosoftPosterAddresses, opt => MapFromMicrosoftPosters(opt));
    }

    private void MapFromMicrosoftPosters(IMemberConfigurationExpression<Post> opt)
    {
        opt.MapFrom(x => x.Comments.Where(c => c.Email.Contains("@microsoft.com")).Select(c => c.Email));
    }
}

Again, this is  a contrived example, but I’ve found this approach to work quite well in practice.

That said, it isn’t perfect.  AutoMapper is limited to things that can be projected down and translated by the underlying LINQ provider.  So, it’s very important that you test your mappings and make sure they actually work at runtime.  And the underlying queries can be hideous to look at.  That isn’t AutoMapper’s fault, but it’s still one of the downsides to using this approach.  

Even more importantly though, this doesn’t help with our top-level Where clause.  Yes, AutoMapper can help us select out related entities, but we’re still going to have to use LINQ for filtering that top-level portion of our query.

But even with these limitations, it’s still a great way to simplify your data access code, and I find that it is often sufficient for at least 80% of my needs.

Solution 2: Create Extension Methods

One of the easiest ways to encapsulate our query logic is to just write an extension method!  This allows us to sweep the complexity under the rug in the same way we could with a repository.  And we can use this approach with AutoMapper, too!  Check it out:

//In our controller..
var topPosts = _context.Posts
    .TopPostsForDateRangeWithMSContributors(startDate, endDate)
    .Project().To<TopPostModel>()
    .ToArray();

//And over in our extension class...
public static IQueryable<Post> TopPostsForDateRangeWithMSContributors(
    this IQueryable<Post> posts, DateTime startDate, DateTime endDate)
{
    return posts.Where(x => x.PostDate >= startDate && x.PostDate < endDate &&
                            x.Comments.Count() >= 3 && 
                            x.Comments.Any(c => c.Email.Contains("@microsoft")));
}

Even better, we could make composable, easy-to-reuse extension methods that we could chain together:

//In our controller...
var topPosts = _context.Posts
    .Between(startDate, endDate)
    .WithAtLeastXComments(3)
    .WithMicrosoftCommenters()
    .Project().To<TopPostModel>()
    .ToArray();

//And over in our extension class...
public static IQueryable<Post> Between(this IQueryable<Post> posts, DateTime startDate, DateTime endDate)
{
    return posts.Where(x => x.PostDate >= startDate && x.PostDate < endDate);
}

public static IQueryable<Post> WithAtLeastXComments(this IQueryable<Post> posts, int commentCount)
{
    return posts.Where(x => x.Comments.Count() >= commentCount);
}

public static IQueryable<Post> WithMicrosoftCommenters(this IQueryable<Post> posts)
{
    return posts.Where(x => x.Comments.Any(c => c.Email.Contains("@microsoft")));
}

The net result is the same: the exact same SQL will be generated for both our TopPostsForDateRangeWithMSContributors method and when we chain our smaller extension methods together.  Personally, I prefer smaller, composable extension methods, but it is possible to go too far and make your query difficult to read.

Solution 3: Query Objects

Solutions 1 and 2 are almost all I use these days, but there are times when you will need more logic than can be easily encapsulated by either extension methods or handled by AutoMapper.  For these cases, you may want to consider query objects instead.

With the right infrastructure in place, you can execute a query off of your EF context, giving that query has full-access to the entire context.  That makes it possible to do complex joins across DbSets, which isn’t doable with the extension method approach.  You can also layer on additional application logic before your query hands back its result set.  It’s overkill for our trivial example, but here it is for completeness:

//In our controller...
var topPosts = _context.Query<TopPostsQuery>().Execute(startDate, endDate);


//And our TopPostsQuery object...
public class TopPostsQuery : IQuery
{
    public AppDbContext Context { get; set; }

    public TopPostModel[] Execute(DateTime startDate, DateTime endDate)
    {
        return Context.Posts.Where(x =>
            x.PostDate >= startDate &&
            x.PostDate < endDate &&
            x.Comments.Count() >= 3 && 
            x.Comments.Any(c => c.Email.Contains("@microsoft")))
            .Project().To<TopPostModel>()
            .ToArray();
    }
}

//And the infrastructure that makes it all possible!
public static class QueryExtension
{
    public static TQuery Query<TQuery>(this AppDbContext context)
        where TQuery : IQuery, new()
    {
        var query = new TQuery();
        query.Context = context;
        return query;
    }
}

public interface IQuery
{
    AppDbContext Context { get; set; }
}

Conclusion

So, of the three options, which should you choose? In my applications, I always use solution 1 (AutoMapper), and I usually support it with solution 2 (extension methods).  Rarely do I find myself needing query objects anymore, but it’s a pattern I keep in my toolbox just the same.

The code from this article is available with my other ASP.NET sample projects.  Pull it down and check it out if you’re curious, and feel free to borrow any code that you like!