Agile EF 4 Repositories Part 4: Compiled LINQ Queries

    1. Agile Entity Framework 4 Repository: Part 1- Model and POCO Classes
    2. Agile Entity Framework 4 Repository: Part 2- The Repository
    3. Agile EF4 Repository: Part 3 -Fine Tuning the Repository
    4. Agile EF 4 Repositories Part 4: Compiled LINQ Queries
    5. Agile Entity Framework 4 Repository Part 5: IObjectSet and Include
    6. Agile Entity Framework 4 Repository: Part 6: Mocks & Unit Tests

I lied. In the last installment of this blog series, I said that the next post would show the tests. They are still sitting there waiting to be written about, but something more important came up. I was focused on the repository building and not paying attention to my queries. Even in my EF Tips & Tricks conference presentations, I discuss the importance of using compiled LINQ queries because of their performance benefit. Here is a blog post I wrote early in 2008 about compiled queries for a quick look at what they do and how to use them: Compiled Queries in Entity Framework

It really is a best practice to use them, so after a few people asked via emails and the comments in the blog “but what about compiled queries?” I wanted to see if I could get them to work in the repository.

One of the the big problems with compiled LINQ queries in EF is the fact that they are associated with the ObjectContext. If the context goes out scope, so does the precompiled query and you have to compile the query again.

But I have already dealt with that problem. Here’s another blog post I wrote, Using Pre-Compiled LINQ to Entities Queries in Web Apps and Services, that showed how to solve this. Essentially, I created a static variable to retain the precompiled query across processes in a web app.

I decided to leverage this approach (simply retaining the compiled query) to be used by different ObjectContexts regardless of whether they are in a separate process or not.

I managed to pull it off. I still have more work to do to fine tune this solution, but I wanted to share the basic structure of how I’m doing it.

First you’ll need to refer back to the repositories I created in the previous post, Agile EF4 Repository: Part 3 -Fine Tuning the Repository, – e.g., CustomerRepository.

My conundrum

The repository uses a context to query, update and manage customer entities. However this context is not an ObjectContext — it is an interface that I created called IContext. My EF context, which does the real Entity Framework tasks, inherits from an ObjectContext and implements IContext. So when the repository happens to be using that flavor of IContext, it will be possible to use compiled queries. But when the repository is using some other context that implements IContext, I cannot assume that I’ll have the ObjectContext features. So I’m in between a rock and a hard place. I don’t really want the repository to have to worry about that; yet, I don’t want the ObjectContext to have knowledge of all of the CustomerRepository queries and all of the ReservationRepository queries etc.

So what I did was add another layer.

The additional layer is a new class (actually, one new class per repository) for compiled queries, e.g., CustomerRepositoryCompiledQueries. That’s a working title. :)

This class and it’s siblings ReservationRepositoryCompiledQueries, AddressRepositoryCompiledQueries, etc, will be responsible for precompiling and invoking queries for its relevant repository.

With me so far? :)

Modifying IContext

Here’s the part I’m not 100% satisfied yet, but it gets me going in the right direction (and works).

Back in the repository, I now need to differentiate between contexts that know how to precompile/invoke and contexts that don’t. This is not the repository’s job, so I added a property to the IContext interface:

bool CanPreCompile { get;  }

Contexts that implement the interface will return either true or false (no kidding, sorry ;)). My context that is the real ObjectContext (ModelContext, from the first blog post) will return true. The mock contexts (coming in later posts in the series) will return false.

If you were patient enough to look at my linked post (above) on retaining the precompiled queries across processes, the setup below will be familiar. I’m declaring a static variable to maintain the precompiled query, e.g., _custByID. When the CustsWithReservations method is requested, if the variable is null (hasn’t been precompiled yet) then I go ahead and precompile it and then invoke it. If it was already pre-compiled, then I go right to invoking it using the context and id passed in.

using System.Collections.Generic;
using System.Linq;
using POCOBAGA.Repository.Interface;
using POCOBAGA.Classes;
using System;
using System.Data.Objects;

namespace POCOBAGA.Repository.Queries
{
  class CompiledCustomerQueries
  {
    static Func<ObjectContext, IQueryable<Customer>> _custWithReservations;
    static Func<ObjectContext, int, Customer> _custByID;

    public static IList<Customer> CustsWithReservations(ObjectContext context)
    {
      if (_custWithReservations == null)
      {
        _custWithReservations = CompiledQuery.Compile<ObjectContext, IQueryable<Customer>>
                       (ctx => from cust in ((IContext)ctx).Customers
                               where cust.Reservations.Any()
                               select cust);
      }
      return _custWithReservations.Invoke(context).ToList();
    }
    public static Customer CustByID(ObjectContext context, int ID)
    {
      if (_custByID == null)
      {
        _custByID = CompiledQuery.Compile<ObjectContext, int, Customer>
         ((ctx, id) => ((IContext)ctx).Customers.
             Where(c => c.ContactID == id).Single());

      }
      return _custByID.Invoke(context, ID);
    }
  }
}

One of the notable parts of this class is that I need the ObjectContext in order to Compile, but I need the IContext in order to get at the Reservations and Customers properties. Since BAGAContext is both ObjectContext and IContext…problem solved with a little casting.

One more change to the IContext (and implementers)

As noted, the Compile method requires an ObjectContext. It will be the repositories calling the methods which compile, but the repositories don’t have an ObjectContext, they have an IContext.

I added the following to the IContext to expose the context as a “compiler”.

     ObjectContext Compiler { get; }

The BAGAContext class which is a true ObjectContext implements this to return it’s ObjectContext:

    public ObjectContext Compiler
    {
      get { return (ObjectContext)this; }
    }

Modifying the Repository

Now let’s see how this all works together in the repository. I’ll modify the GetByID method of the CustomerRepository that was in the previous post in the series.

   public Customer GetById(int id)
    {
      if (_context.CanPreCompile)
      {
        return CompiledCustomerQueries.CustByID(_context.Compiler, id);
      }
      else
        return _context.Customers.Include("Contacts")
            .Where(c => c.ContactID == id).FirstOrDefault();
    }

If the context is one which can leverage precompiled  queries then I have to use the repositories supplemental logic for getting the query from the compiler, otherwise, I just go ahead and execute the query.

Testing the Pre-Compiled Repository

I want to do some testing to verify that the compiled queries work. The goal is to call GetByID with two separate repository instances and watch in the debugger if the query is simply invoked or compiled on the second time. This will be a simple integration test so I’m not going to need the mocks (which I haven’t shown you yet anyway).

The telling part of the test code is:

    var cRep = new CustomerRepository();
    Customer c1 = cRep.GetById(1);
    var cRep2 = new CustomerRepository();
    Customer c2 = cRep2.GetById(16);

When debugging this code the CompiledQuery.Compile is executed as the first GetByID is called. When the second GetById is called against the second repository, the execution recognizes that the query has already been compiled (i.e. _custByID is not null) and just skips right to the invoke.

And in Summary…

For me this is a proof of concept, if perhaps not the perfect implementation. Precompiling LINQ to Entities queries, especially those which get used frequently, is important when you care about performance. It’s great to be able to implement various features, but critical to be able to use them in concert.

Having the ability to truly write agile, persistent ignorant, testable code with EF and at the same time, continue to benefit from other important features of Entity Framework is  pretty important. Otherwise, EF would not really be PI.

There are plenty of other scenarios to run the repositories against, but this was pretty high up on the list and I’m happy that I was able to work out a good starting point.

19 thoughts on “Agile EF 4 Repositories Part 4: Compiled LINQ Queries

  1. I hate to say it, but this looks awfully like "plumbing" code that using a "DAL generator" is supposed to hide……

    Why doesn’t the framework do this with queries automatically?

  2. There are several reasons that I know of why queries are not automatically compiled in LINQ-to-SQL and EF.

    1. They would have to be cached as static variables which takes memory resources.

    2. If you return a compiled query as IQueryable<T> (say from a repository) and then try to add .Take(1).Skip(1) or a "Where" condition, the compiled query will be executed first getting all the results into memory. The additional operations will be done in memory using regular LINQ.

    Compiled queries are only suitable for methods that do a specific operation and do not return IQueryable<T>.

    3. Query compilation does not work properly for some complex queries, at least in LINQ-to-SQL.

  3. ok, I understand.

    The reason why I am asking about the source code is that I don’t know how you can use Include method in _context.Reservations. Its type is IObjectSet and this interface does not have this method (extension method),

  4. I’ve been experimenting with my own POCOs and my own Context with interface… To work with Stored Procedures, I have methods in the Context that execute Function Imports. The Repository then calls the methods on the Context but this seems to be a fair bit of extra code. Is this the best way?

  5. Hello Julie

    Will this list of blogs on EF4 be extended to explain Data transfer Objects, Domain Services too in future?

    regards

    Dhinesh Kumar

  6. The biggest problem I have with this is that it requires you to duplicate the in 2 places… We already have upwards of 500 queries running and I would hate to have to maintain 2 copies of each.

    We solved this problem by have a separate class which the repository or data access class can use. This class exposes 2 properties for each query, one that exposes it as an Expression and the other Func.

    Then internally we do something similar to the below:

    public Func<Context, IActivityParameters, IQueryable<Activity>> GetByActivityParametersQuery { get; protected set; }

    public Expression<Func<Context, IActivityParameters, IQueryable<Activity>>> GetByActivityParametersExpression { get; protected set; }

    public ActivityRepository()

    {

    this.GetByActivityParametersExpression = (ctx, p) => from x in ctx.ActivitySet.Include("Location")

    where x.ActivityId == p.Id

    select x;

    this.GetByActivityParametersQuery = CompiledQuery.Compile(this.GetByActivityParametersExpression.Expand());

    }

  7. Intereseting but I’m really confused by your example. First of all, what was I doing that required you to maintain two sets of queries? Second, are you using dynamic queries here? Passing in teh expression? Are these really getting reused and not recompiled? I’m not challenging you, just having a hard time groking.

Leave a Reply

Your email address will not be published. Required fields are marked *


− four = 5

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>