Adding functonality to Linq-to-SQL objects to perform common selections

Question

In a previous question I asked how to make "Computed properties" in a linq to sql object. The answer supplied there was sufficient for that specific case but now I've hit a similar snag in another case.

I have a database with Items that have to pass through a number of Steps. I want to have a function in my database that retrieves the Current step of the item that I can then build on. For example:

var x = db.Items.Where(item => item.Steps.CurrentStep().Completed == null);

The code to get the current step is:

Steps.OrderByDescending(step => step.Created).First();

So I tried to add an extension method to the EntitySet<Step> that returned a single Step like so:

public static OrderFlowItemStep CurrentStep(this EntitySet<OrderFlowItemStep> steps)
{
    return steps.OrderByDescending(o => o.Created).First();
}

But when I try to execute the query at the top I get an error saying that the CurrentStep() function has no translation to SQL. Is there a way to add this functionality to Linq-to-SQL in any way or do I have to manually write the query every time? I tried to write the entire query out first but it's very long and if I ever change the way to get the active step of an item I have to go over all the code again.

I'm guessing that the CurrentStep() method has to return a Linq expression of some kind but I'm stuck as to how to implement it.

Answer 1

Check out my answer to "switch statement in linq" and see if that points you in the right direction...

The technique i demonstrate there is the one that got me past the scary "no translation to SQL" error.

Answer 2

The problem is that CurrentStep is a normal method. Hence, the Expression contains a call to that method, and naturally SQL cannot execute arbitrary .NET methods.

You will need to represent the code as an Expression. I have one in depth example here: http://www.atrevido.net/blog/2007/09/06/Complicated+Functions+In+LINQ+To+SQL.aspx

Unfortunately, the C# 3.0 compiler has a huge omission and you cannot generate calls to Expressions. (i.e., you can't write "x => MyExpression(x)"). Working around it either requires you to write the Expression manually, or to use a delegate as a placeholder. Jomo Fisher has an interesting post about manipulating Expression trees in general.

Without actually having done it, the way I'd probably approach it is by making the CurrentStep function take the predicate you want to add ("Completed == null"). Then you can create a full Expression> predicate to hand off to Where. I'm lazy, so I'm going to do an example using String and Char (String contains Chars, just like Item contains Steps):

using System;
using System.Linq;
using System.Linq.Expressions;

class Program {
    static void Main(string[] args) {
        Console.WriteLine(StringPredicate(c => Char.IsDigit(c)));
        var func = StringPredicate(c => Char.IsDigit(c)).Compile();
        Console.WriteLine(func("h2ello"));
        Console.WriteLine(func("2ello"));
    }

    public static Expression<Func<string,bool>> StringPredicate(Expression<Func<char,bool>> pred) {
        Expression<Func<string, char>> get = s => s.First();
        var p = Expression.Parameter(typeof(string), "s");
        return Expression.Lambda<Func<string, bool>>(
            Expression.Invoke(pred, Expression.Invoke(get, p)),
            p);
    }
}

So "func" is created by using StringPredicate to create an Expression. For the example, we compile it to execute it locally. In your case, you'd pass the whole predicate to "Where" so it gets translated to SQL.

The "get" expression is where you put your "extension" stuff (OrderByWhatever, First, etc.). This is then passed in to the predicate that's given to you.

Don't worry if it looks complicated; it sorta is at first. If you haven't done this kinda stuff before, it'll take a bit of time (the first time I did this kinda stuff, it took hours to get it right :|.. now it comes slightly easier). Also, as I mentioned, you can write a helper method to do this re-writing for you (so you don't directly need to use the Expression.Whatever methods), but I haven't seen any examples and haven't really needed it yet.