Specification Pattern In Entity Framework 4 Revisited

After the post Entity Framework 4 POCO, Repository and Specification Pattern was published for a while, there have been quite a few of positive comments from readers. At first, I thought that this piece of code should have been used as a prototype to demonstrate the implementation of EF POCO, the Repository and Specification pattern. I known it is not the optimized piece of code to everyone and that if there was suggestion for improvement, I would leave to reader as I thought once they understood the design idea, they could extend/change/use the API in anyway they want.

However, there are also some comments concerning about the way the Specification pattern is applied which might cause the BIG performance problem when used that I was not aware of. Even I am using this API in my current work but it’s a shame that I rarely use Specification to query the data but the other methods are enough for me.

For your information, here are the extracted comments from Jon & Buu (many thanks :)) which pointed out the problem:

“Jon: Linq to Entities uses expressions to build the SQL that will be executed on the database server. If you use the specification pattern as designed above, i.e., without expressions, the generated SQL is never impacted. Instead, EF will generate “SELECT * FROM Table,” returning all rows. From there the specification pattern kicks in and filters the data in memory. Your test passed because you got the right result, but not in the right way. it would never work in a real would scenario.”

“Buu:  …the cause of the issue as Jon observed is because the method (IsSatisfiedBy) of Specification is fed into the Where method of the query object. That results in LINQ-2-EF loading all records to memory objects and then filter those objects using the passed in method. That’s a huge performance hit.

To fix the issue, you should feed into the Where method an instance of Expression so that the Where overload in IQueryable is invoked (instead of the one in IEnumerable). The Specification already stores an instance of Expression, so the code change should be straightforward. However, since not all expression operations are supported by LINQ-2-EF, you might end up breaking some existing code. Be warned…”

I verified the problem with Entity Framework Profiler and I have to say the problem is there. Here are some tests I made to retrieve a product by name, and also the results from the profiler:

Use standard lambda expression:

private void FindProductByName()
{
    IEnumerable<Product> products = productRepository.Find<Product>(p => p.Name == "Windows XP Professional");
    Assert.AreEqual(1, products.Count());
}

The output from the profiler

EF generated the correct sql and returned the expected result, no thing wrong with this.

Use specification

private void FindBySpecification()
{
    Specification<Product> specification = new Specification<Product>(p => p.Name == "Windows XP Professional");
    IEnumerable<Product> products = productRepository.Find<Product>(specification);
    Assert.AreEqual(1, products.Count());
}

The output from the profiler

As you can see there is a problem with using specification to retrieve a product in which EF loads all the products from the database, then perform filtering a product against the whole set of products in-memory. If the number of products is huge, it really is an issue since the query is not efficient at all.

This is because the specification pattern is implemented incorrectly. To fix it, I change the specification contract as the following:

public interface ISpecification<TEntity>
{
    TEntity SatisfyingEntityFrom(IQueryable<TEntity> query);

    IQueryable<TEntity> SatisfyingEntitiesFrom(IQueryable<TEntity> query);
}

And the implementation of some generic repository’s methods which accepts a specification are as following:

public class GenericRepository : IRepository
{
    // other code...

    public IQueryable<TEntity> GetQuery<TEntity>() where TEntity : class
    {
        var entityName = GetEntityName<TEntity>();
        return ObjectContext.CreateQuery<TEntity>(entityName);
    }

    public TEntity FindOne<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
    {
        return criteria.SatisfyingEntityFrom(GetQuery<TEntity>());
    }

    public IEnumerable<TEntity> Find<TEntity>(ISpecification<TEntity> criteria) where TEntity : class
    {
        return criteria.SatisfyingEntitiesFrom(GetQuery<TEntity>());
    }

    // other code...
}

The implementation of a simple specification is straightforward.

public class Specification<TEntity> : ISpecification<TEntity>
{
    public Specification(Expression<Func<TEntity, bool>> predicate)
    {
        Predicate = predicate;
    }

    public TEntity SatisfyingEntityFrom(IQueryable<TEntity> query)
    {
        return query.Where(Predicate).SingleOrDefault();
    }

    public IQueryable<TEntity> SatisfyingEntitiesFrom(IQueryable<TEntity> query)
    {
        return query.Where(Predicate);
    }

    public Expression<Func<TEntity, bool>> Predicate;
}

Here is the output of the profiler with the new specification implementation:

As you can see EF now generates the expected sql and returns expected result as well, very efficient.

What about the composite specification?

If you already read the previous post, you should have known that the composite specification was being used to chain the specifications. With the new implementation of specification, the composite specification implementation also needs to change. The technique being applied here is to combine the lambda expression (or predicate) of each specification (left and right side of a composite specification) to create a new lambda expression then this new lambda expression is fed to the IQuerable object for querying.

Here is the code of the extension method of lambda expression which is mostly inspired from here:

public static class ExpressionExtension
{
    public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // build parameter map (from parameters of second to parameters of first)
        var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with parameters from the first
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

        // apply composition of lambda expression bodies to parameters from the first expression
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.And);
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.Or);
    }
}

And here is the implementation of the composite specification:

public class AndSpecification<TEntity> : CompositeSpecification<TEntity>
{
    public AndSpecification(Specification<TEntity> leftSide, Specification<TEntity> rightSide)
        : base(leftSide, rightSide)
    {
    }

    public override TEntity SatisfyingEntityFrom(IQueryable<TEntity> query)
    {
        return SatisfyingEntitiesFrom(query).FirstOrDefault();
    }

    public override IQueryable<TEntity> SatisfyingEntitiesFrom(IQueryable<TEntity> query)
    {
        return query.Where(_leftSide.Predicate.And(_rightSide.Predicate));
    }
}

public class OrSpecification<TEntity> : CompositeSpecification<TEntity>
{
    public OrSpecification(Specification<TEntity> leftSide, Specification<TEntity> rightSide)
        : base(leftSide, rightSide)
    {
    }

    public override TEntity SatisfyingEntityFrom(IQueryable<TEntity> query)
    {
        return SatisfyingEntitiesFrom(query).FirstOrDefault();
    }

    public override IQueryable<TEntity> SatisfyingEntitiesFrom(IQueryable<TEntity> query)
    {
        return query.Where(_leftSide.Predicate.Or(_rightSide.Predicate));
    }
}

The test code to find product by name and price which uses the AndSpecification

private void FindByAndCompositeSpecification()
{
    IEnumerable<Product> products = productRepository.Find<Product>(
        new Specification<Product>(p => p.Price < 100).And(new Specification<Product>(p => p.Name == "Windows XP Professional")));
    Assert.AreEqual(1, products.Count());
}

The output from the profiler:

The test code to find product which applies the OrSpecification:

private void FindByOrCompositeSpecification()
{
    IEnumerable<Product> products = productRepository.Find<Product>(
        new Specification<Product>(p => p.Price < 100).Or(new Specification<Product>(p => p.Name == "Windows XP Professional")));
    Assert.AreEqual(2, products.Count());
}

The output from the profiler:

The problem is solved!

The updated source code can be downloaded here.

Once again, comments are welcome.