When using LINQ, you need to be careful to use the right kind of Lambda expression. “What, there is more than one kind?”, I hear you gasp. There sure is! And if you aren’t careful, you’ll get a nice little message at runtime to tell you:

“System.Object DynamicInvoke(System.Object[])’ has no supported translation to SQL.”

What that literally means is “You’re not using lambdas right, you dummy!”

Let me explain.

A Refactoring Problem

Let’s start with my problem. I created a Controller class which uses a DataContext to check for an item in a database, and then, if the item was not found, it inserts one. The class looks like this:

  public class UserController {
    public void CreateIDUser(string ID) {
      var DB = new DBataContext();
      var existingUsers = from user in DB.Users
                          where user.ID == ID
                          select user;

      if (existingUsers.Count() == 0) {
        User newUser = new User();
        newUser.ID = ID;
        DB.Users.InsertOnSubmit(newUser);
        DB.SubmitChanges();
      }
    }
  }

The method uses LINQ C# Comprehension Syntax (“from user in DB.Users …”) to perform a query. It then uses the DataContext to do an Insert when the changes are submitted.

I created a unit test for this method, and it gave me a glowing green light.

Now, I want to add a similar method which uses a different property for comparison. The method would be identical to the first, except for the “where” query and the initialization of the new User object. I don’t want to repeat myself, as that might lead to bugs, so I want to refactor out the common code. I’m going to do it using lamda expressions.

Refactoring using lambda expressions

I could go into my design decisions here, but that’s not the point of this post. I’ll just give you the answer – what I want my class to look like.

Basically, I want to end up with 2 methods which only differentiate on the parts I need them too:

public void CreateIDUser(string ID) {
  CreateUser(
    user => user.ID == ID,
    user => user.ID = ID);
}

public void CreateNamedUser(string name) {
  CreateUser(
    user => user.Name == name,
    user => user.Name = name);
}

I create a private method called CreateUser which does the repetitive work:

private void CreateUser(Func<User, bool> compare, Action<User> initialize) {
  var DB = new DBDataContext();
  var existingUsers = from user in DB.Users
                      where compare(user)
                      select user;

  if (existingUsers.Count() == 0) {
    User newUser = new User();
    initialize(newUser);
    DB.Users.InsertOnSubmit(newUser);
    DB.SubmitChanges();
  }
}

Now, this compiles, but when I run my existing test, I get a nasty red light, and the exception message:

“System.Object DynamicInvoke(System.Object[])’ has no supported translation to SQL.”

What’s wrong? The problem lies in the query itself, which is using expressions, not pure delegates. My Func<User, bool> cannot be used by LINQ to SQL to do its work. Instead, I have to use an Expression.

Using an Expression<Func<T, bool>>

Instead of a simple “Func<User, bool>”, I have to declare my parameter as “Expression<Func<User, bool>”. That is what LINQ to SQL expects to use for deferred queries. The good news is that I can use an expression by defining it in the method accepting the lamda; the C# compiler can translate my existing lamda expressions from delegates to expressions without any extra work.

Once that’s done, there’s one more caveat. You cannot use C# comprehension syntax with an expression. You have to use the standard LINQ query syntax instead.

Here’s what the final class looks like after the refactoring.

public class UserController {
  public void CreateIDUser(string ID) {
    CreateUser(
      user => user.ID == ID,
      user => user.ID = ID);
  }

  public void CreateNamedUser(string name) {
    CreateUser(
      user => user.Name == name,
      user => user.Name = name);
  }

  private void CreateUser(Expression<Func<User, bool>> compare, Action<User> initialize) {
    var MeetUp = new MeetUpDataContext();
    IQueryable<User> existingUsers = MeetUp.Users
      .AsQueryable<User>()
      .Where(compare);

    if (existingUsers.Count() == 0) {
      User newUser = new User();
      initialize(newUser);
      MeetUp.Users.InsertOnSubmit(newUser);
      MeetUp.SubmitChanges();
    }
  }
}

After that, I get my green light back, and we’re all happy.