Staying DRY with LINQ to Entities


10 February 2012, by

A common problem with LINQ to Entities is that it can often lead to code repetition. This post explains why and when this happens, and how to get around it using LinqKit.

In this article I’m going to assume that you have at least a passing familiarity with the concept of DRY (Don’t Repeat Yourself) and why it’s a good thing, and with LINQ to Entities.

Auto-generated SQL

One of the cleverer features of LINQ to Entities is that you can write code like this:

IQueryable orders = data.Orders;
const decimal VAT_RATE = 1.2m;
const decimal BIG_ORDER_MIN_VALUE = 10000;

var bigOrders =
orders.Where(order => (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE).ToList();

and LINQ to Entities will automatically turn the code in the where clause into SQL for you, in this case something like:

SELECT *
FROM Orders
WHERE (Price * 1.2) > 10000

This is particularly useful if your Orders table contains a lot of rows as only the rows you’re actually interested in get returned from the database.

Express Yourself

Before we get to the problem, a brief refresher course on how this works behind the scenes.

The key is the .Net Expression. If you’re already familiar with LINQ then you are probably also familiar with using lambdas like the one in the code above:

order => (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE

Normally in LINQ this code would be a function delegate with type Func<Order, bool>, however for LINQ to Entities to do its thing the Where method actually needs an Expression<Func<Order, bool>> which the compiler can helpfully generate automatically from the same source code.

The key difference between Func<Order, bool> and Expression<Func<Order, bool>> is that a Func is effectively a delegate to a method that can be executed, whereas an Expression describes a function and even allows that description to be manipulated (it is also possible to compile an Expression to a Func at runtime and then execute it). LINQ to Entities takes the Expression provided and uses it to generate SQL rather than .Net IL.

A Commonising Problem

To get back to our example; let’s say that you wanted to use the same price logic elsewhere in your code but you weren’t aware of the differences between expressions and functions. In accordance with DRY principles, you might try to refactor the logic into a re-usable function:

public bool IsBigOrder(Order order)
{
return (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE;
}

var bigOrders = orders.Where(order => IsBigOrder(order)).ToList();

This code will happily compile, but when you try to run it LINQ to Entities will throw an exception with the message:

LINQ to Entities does not recognize the method ‘Boolean IsBigOrder(ExpressionTreeTests.Models.Order)’ method, and this method cannot be translated into a store expression.

(Note that the ToList() call is important in these examples as without it LINQ to Entities won’t actually try to query the database and so won’t notice the problem).

This makes sense because the IsBigOrder method will have been compiled along with the rest of your code, and one thing that LINQ to Entities can’t do is de-compile IL back to an Expression.

A Partial Solution

So what can we do? How about passing an actual expression to the Where method?

public Expression<Func<Order, bool>> IsBigOrder =
order => (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE;

var bigOrders = orders.Where(IsBigOrder).ToList();

This does actually work as expected and means that we can reuse the IsBigOrder Expression. This may be enough for what you need to do, in which case, great. However, there are a couple of potential issues with this solution.

Potential Issues

1) What if we want to combine a test for big orders with some other logic (e.g. a test for a particular customer type)?

You could combine expressions by judicious use of Where() and Union() to imitate logical ANDs and ORs. However, that quickly leads to rather unwieldy code that is difficult to understand and should probably only be used when the logic is very simple.

2) What if we want to be able to re-use the big order logic on a different table (one that doesn’t map to Order objects)?

This is rather tougher. You might try creating a generic function to return a customised expression:

public Expression<Func<T, bool>>
  GetIsBigOrderExpression<T>(Expression<Func<T, decimal>> priceExpression)
{
  return entity =>
    (priceExpression.Compile().Invoke(entity) * VAT_RATE) > BIG_ORDER_MIN_VALUE;
}

var bigOrders =
  orders.Where(GetIsBigOrderExpression<Order>(order => order.Price)).ToList();

Again this will compile, but unfortunately we’re scuppered by that call to priceExpression.Compile() which inserts compiled code into the expression tree again.

So what to do?

LinqKit

Fortunately the answer (unlike the question!) is fairly simple. There is a free library called LinqKit that includes some helper methods to solve precisely this problem. Using LinqKit you would rewrite the code like this:

public Expression<Func<T, bool>>
  GetIsBigOrderExpression<T>(Expression<Func<T, decimal>> priceExpression)
{
  return entity =>
    (priceExpression.Invoke(entity) * VAT_RATE) > BIG_ORDER_MIN_VALUE;
}

var bigOrders =
  orders.AsExpandable().Where(GetIsBigOrderExpression(order => order.Price)).ToList();

There are two differences to the preceding code:

  1. We’ve got rid of the call to priceExpression.Compile(), LinqKit provides an Invoke() extension method that can be called directly on an expression.
  2. The orders query now has an extra AsExpandable() call made on it.

How Does it Work?

Behind the scenes the call to AsExpandable() adds a cunning wrapper around the query that finds the Invoke() extension method calls and automatically replaces them with the actual expression tree of the expression being invoked (in our case priceExpression). This means that LINQ to Entities ends up with a pure expression tree that it can then turn into SQL as usual.

Now that we have the basic framework in place there’s nothing to stop us passing other parameters into GetIsBigOrderExpression() – as long as they are simple variables or Expressions. Perhaps you want to use the same code on orders from another country with a different VAT rate? No problem, just add a vatRate parameter to GetIsBigOrderExpression() and pass in the rate rather than using the constant.

I should also point out that if all you want to do is combine fixed expressions using logical ANDs and ORs LinqKit already contains various functions to do just that, so you don’t need to write your own. However, that will have to wait for another day!

Tags: , ,

Categories: Technical

«
»

2 Responses to “Staying DRY with LINQ to Entities”

  1. Joe says:

    public Expression<Func> IsBigOrder =
    order => (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE;

    var bigOrders = orders.Where(IsBigOrder).ToList();
    This does actually work ..

    In fact, I am using entity Framework 4.3 code first and the WHERE clause only accept FUNC and not expression. Why?

  2. Dan Corder says:

    I’m not sure which version of Where() you mean. I’ve just tried compiling the code below and it seems to be fine. However, if you find somewhere where only Funcs are valid then that’s probably good. As the article above shows using Expressions is more complicated than using Funcs and not something you want to do unless you have a good reason.

    using System;
    using System.Data.Entity;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Collections.Generic;

    namespace ConsoleApplication1
    {
    class Program
    {
    const decimal VAT_RATE = 1.2m;
    const decimal BIG_ORDER_MIN_VALUE = 10000;

    static void Main(string[] args)
    {
    Expression<Func> IsBigOrder = order => (order.Price * VAT_RATE) > BIG_ORDER_MIN_VALUE;

    using (var dbContext = new OrderContext())
    {
    var bigOrders = dbContext.Orders.Where(IsBigOrder);
    }
    }
    }

    public class Order
    {
    public decimal Price { get; set; }
    }

    public class OrderContext : DbContext
    {
    public DbSet Orders { get; set; }
    }
    }


Leave a Reply

* Mandatory fields


× 5 = thirty five

Submit Comment