Wednesday, July 17, 2013

I don't always re-use my LINQ-To-EF, but when I do, I prefer LinqKit

I don't always re-use my LINQ-To-Entities, but when I do, I prefer LinqKit Have you ever been working on a project, stopped abruptly, and thought to yourself "what was my work-life like before LINQ and Entity Framework?" I do it all the time. The question tends to bring up hazy memories of temp variables, foreach loops, and dictionary lookups. Off through the clouds there are homegrown SQL solutions...confusing, low level implementations of CSLA...endless folders sitting on my hard drive containing nHibernate test projects. Oh hey, look at that, there's an implementation of IComparable. Weird. It's not that I didn't appreciate all those things, it's just that I much prefer Entity Framework to other mapping tools, and LINQ is freaking awesome. Now that I've established my feelings on LINQ and Entity Framework, I have to tell you that it really grinds my gears when I have to re-write LINQ-To-Entities statements because it can't translate .NET methods into store expressions. Consider an example where we have a UserInfo helper class that we use quite often. In this case we'll have an order that we want to pull the user info off of:

public class UserInfo {

 public int UserId { get; set; }

    public string FirstName { get; set; }

 public string LastName { get; set; }

}



public UserInfo GetFromOrder(int orderId) {

    return (from order in Entities.Orders 

     where order.OrderId = orderId

           let u = order.User

     select new UserInfo() {

        FirstName = u.FirstName,

     LastName = u.LastName,

     Userid = u.UserId

     }).FirstOrDefault();

}

Cool. Looks pretty good. Ok but now you need to load up a slightly more complex object that pulls OrderInfo that has a UserInfo reference.

public OrderInfo GetOrderInfo(int orderId) {

 return (from order in Entities.Orders 

     where order.OrderId = orderId

           let u = order.User

     select new OrderInfo() {

    OrderId = order.OrderId,

    ProductId = order.ProductId,

    User = new UserInfo() {

     FirstName = u.FirstName,

     LastName = u.LastName,

     Userid = u.UserId

    }

     }).FirstOrDefault();         

}

Notice that I have to write this block twice. It's duplicated.

 new UserInfo() {

  FirstName = u.FirstName,

  LastName = u.LastName,

  Userid = u.UserId

 }

 

Sure that's not so bad, but what if my UserInfo object was much more complex, and had its own reference properties? It'd get out of hand quickly. Ok so let's do what most developers will do and add a constructor to UserInfo that takes a User in its constructor

public UserInfo(User u) {

    FirstName = u.FirstName;

 LastName = u.LastName;

 Userid = u.UserId;

}



public OrderInfo GetOrderInfo(int orderId) {

 return (from order in Entities.Orders 

     where order.OrderId = orderId

           let u = order.User

     select new OrderInfo() {

    OrderId = order.OrderId,

    ProductId = order.ProductId,

    User = new UserInfo(u)

   }).FirstOrDefault();         

}

That looks great except LINQ-To-EF doesn't understand how to translate the method call (constructor) into an expression that EF can then translate into SQL. You'll end up with this exception:

Only parameterless constructors and initializers are supported in LINQ to Entities.

or even worse if you try to use another method that isn't supported:

LINQ to Entities does not recognize the method <> method, and this method cannot be translated into a store expression.

Turns out that this is a very common problem with developing software using LINQ-To-EF. I am employed as a software developer. As a general rule I don't like to rewrite things. You understand, right? I'm a little eccentric, but I'm not crazy. There has to be a better way. Luckily for us, it turns out there is. The answer my friends, is LinqKit. At its core, LinqKit is a set of extensions for LINQ. Rather than spend time explaining everything there is to know about LinqKit, I will instead sell you on it with a fix to the previous example. Expressions are the key to re-using LINQ-To-Entities. Rather than write re-usable methods, we'll write re-usable expressions which can be expanded out before EF translates the Linq query to SQL. LinqKit provides a custom implementation of IQueryable that will unwrap all expanded expressions and translate them into something that EF will understand. The only requirement for the expression is that .Expand() is called on it before invocation.

 public static Expression<Func<User, UserInfo>> FnUserToUserInfo  { 

 get {

  return u => new UserInfo() {

   FirstName = u.FirstName,

   LastName = u.LastName,

   UserId = u.UserId

  };

 }

}

Here's our queries re-written:

public UserInfo GetFromOrder(int orderId) {

 var fnSelect = Extensions.FnUserToUserInfo.Expand();

 return (from order in Entities.Orders.AsExpandable()

   where order.OrderId == orderId

   let u = order.User

   select fnSelect.Invoke(u)

 ).FirstOrDefault();

} 





public OrderInfo GetOrderInfo(int orderId) {

  var fnSelectUser = Extensions.FnUserToUserInfo.Expand();

  return (from order in Entities.Orders.AsExpandable()

    where order.OrderId == orderId

    let u = order.User

    select new OrderInfo() {

     OrderId = order.OrderId,

     ProductId = order.ProductId,

     User = fnSelectUser.Invoke(u)

    }).FirstOrDefault();

 }

Notice that we have to call "AsExpandable()" on the dbset, which returns a LinqKit implementation of IQueryable, effectively wrapping the original query so it has a chance to unwrap the expressions before it evaluates. Also notice that in order to use an expression, we need to first Expand() it, and then call Invoke() to execute it. That's all there really is to it. The real beauty here is that you can nest this as much as you want. We can re-write that 2nd query using a re-usable OrderInfo expression.

 public static Expression<Func<Order,User, OrderInfo>> FnOrderToOrderInfo {

 get {

    var fnSelect = FnUserToUserInfo.Expand();

    return  (o,u) => new OrderInfo() {

  OrderId = o.OrderId,

  ProductId = o.ProductId,

  User = fnSelect.Invoke(u)

  //Note: I could just use o.User and foregone the u in the expression but that wouldn't take advantage of the //let keyword, which may result in an extra join in some cases

  };

 }

}   





public static OrderInfo GetOrderInfo(int orderId) {

 var fnSelect = Extensions.FnOrderToOrderInfo.Expand();

 return (from order in Entities.Orders.AsExpandable()

   where order.OrderId == orderId

   let u = order.User

   select fnSelect.Invoke(order, u)

 ).FirstOrDefault();         

}

Clean, Crisp, Re-Usable. What more can you ask for? As long as your expressions are expanded, contain no incompatible methods, and are invoked within an AsExpandable() LinqKit implementation of IQueryable, you're good to go. I highly encourage you to take a peek over at the LinqKit site to see all of the features it has to offer. I hope this example wet your appetite for more. Trust me, you won't regret it.