How to turn this Func into an expression?

Question

I am playing around with expression trees and trying to better understand how they work. I wrote some sample code that I'm working with and hopefully someone can help me out.

So I have this somewhat messy query:

/// <summary>
/// Retrieves the total number of messages for the user.
/// </summary>
/// <param name="username">The name of the user.</param>
/// <param name="sent">True if retrieving the number of messages sent.</param>
/// <returns>The total number of messages.</returns>
public int GetMessageCountBy_Username(string username, bool sent)
{
    var query = _dataContext.Messages
        .Where(x => (sent ? x.Sender.ToLower() : x.Recipient.ToLower()) == username.ToLower())
        .Count();
    return query;
}

_dataContext is the entity framework data context. This query works beautifully, but it's not easy to read. I decided to factor the inline IF statement out into a Func like this:

public int GetMessageCountBy_Username(string username, bool sent)
{
    Func<Message, string> userSelector = x => sent ? x.Sender : x.Recipient;
    var query = _dataContext.Messages
        .Where(x => userSelector(x).ToLower() == username.ToLower())
        .Count();
    return query;
}

This seems like it would work great, but there is a problem. Because the query is against IQueryable<T> this LINQ expression is being translated into SQL to be executed at the data source. That's great, but because of this it does not know what to do with the call to userSelector(x) and throws an exception. It cannot translate this delegate into an expression.

So now that I understand why it's failing I would like to try and make it work. It's far more work for what I need, but I'm doing it just out of pure interest. How might I turn this Func into an expression that can be translated into SQL?

I tried to do this:

Expression<Func<Message, string>> userSelectorExpression = x => sent ? x.Sender : x.Recipient;
Func<Message, string> userSelector = userSelectorExpression.Compile();

With this however, I get the same error. I think I'm failing to understand expressions. I think all I'm doing with the above code is writing an expression but then turning it into executable code again and then getting the same error. However, if I try to use userSelectorExpression within the LINQ query it can't be called like a method.

Edit

For those interested in the exception, here it is:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

I took this to mean that it could not "invoke" the userSelector delegate. Because, as stated above, it needs to translate it into an expression tree.

When using a real method, you get a slightly more verbose error message:

LINQ to Entities does not recognize the method 'System.String userSelector(Message, Boolean)' method, and this method cannot be translated into a store expression.

Note: Ultimately it will be far simpler to just use my original query, I know. I am just interested in how expressions work for future reference. — CatDadCode, May 26 '11 at 15:14
@FlyingStreudel, same error. It is because the query is being translated into an expression tree. — CatDadCode, May 26 '11 at 15:17
I don't think the problem is that it can't turn it into an expression tree, but instead that it can't turn the resulting expression tree into SQL. — Mike Two, May 26 '11 at 15:38
@Mike, you think so? Because even if I do a simple delegate: `Func addOne = x => x + 1;` I get the same error. A failure to "invoke". Can't really make a simpler delegate than that. But maybe you're right, maybe I'm missing something. — CatDadCode, May 26 '11 at 15:40
Sorry, I should elaborate. QueryProviders work by taking an expression tree and translating it into something else. The LINQ to Entities provider translates expression trees to SQL. But there are expression trees that it can't translate. Not everything you can write in code can be translated to SQL. That's what the exception is telling you. It's failing at the translation to sql step not the translation to expression tree step. In this case it isn't smart enough to dig into `userSelector` and figure out what to do with it. But that doesn't mean you got an invalid expression tree — Mike Two, May 26 '11 at 15:42
Hmmmm, so I wonder why the `addOne` delegate fails? My original delegate wasn't very complicated either, is there a way to structure it better so that it can translate the expression? — CatDadCode, May 26 '11 at 15:44
@Chevex - RE - the simple delegate. That's really the proof. It can't translate invocations to valid sql. It can't look inside them and figure it out. SQL is such a different language than C#. There just isn't exactly the equivalent of "call this method with these arguments". Sure there are stored procs and user defined functions but you're not asking it to call those. You're asking to execute a C# method that does who knows what. — Mike Two, May 26 '11 at 15:48
Right that's what I said originally, which is why it needs to be represented as an expression and not a compiled delegate, which is what I'm trying to learn how to do. It can't invoke a compiled method, it needs an expression. — CatDadCode, May 26 '11 at 15:49
@Chevex. I think it just bails out at invocations. They just didn't try to look inside an invocation during the translation code. Take a look at `ExpressionVisitor` http://msdn.microsoft.com/en-us/library/system.linq.expressions.expressionvisitor.aspx See all the Visit* methods? They just chose to throw an exception in the LINQ to entities implementation of `VisitInvocation`. Design choice. You're stuck with it. Wish there was another way — Mike Two, May 26 '11 at 15:51
Whilst interesting, I'm not sure any of this is making the statement easier to read. — Jodrell, May 26 '11 at 16:02
@Chevex - you may also want to read through this http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx It is an exhaustive but very good walkthrough on building query providers. It won't directly help with this one query but it will help you understand the complexities of expression trees and how providers must try to translate them — Mike Two, May 26 '11 at 16:08
@Jodrell, I specify several times that I am thoroughly aware of that. — CatDadCode, May 26 '11 at 16:30

score 2 · Answer 1 · answered May 26 '11 at 15:18

2

No need to complicate:

return sent
    ? _dataContext.Messages.Count(x => x.Sender.ToLower() == username.ToLower())
    : _dataContext.Messages.Count(x => x.Recipient.ToLower() == username.ToLower());

answered May 26 '11 at 15:18

artplastika

1,972
2
19
38

CatDadCode · Accepted Answer · 2011-05-26T17:22:04.593

Well after playing around a bit, I got what I wanted.

This didn't save me tons of code in this case, but it does make the base query much easier to look at. For more complicated queries in the future this will be awesome! This query logic never gets repeated, but still gets re-used as many times as I need to.

First I have two methods in my repository. One counts the total number of messages (the one I used as the example in my question) and one that actually gets a collection of messages by page number. Here is how they are structured:

The one that gets a total count of messages:

    /// <summary>
    /// Retrieves the total number of messages for the user.
    /// </summary>
    /// <param name="username">The name of the user.</param>
    /// <param name="sent">True if retrieving the number of messages sent.</param>
    /// <returns>The total number of messages.</returns>
    public int GetMessageCountBy_Username(string username, bool sent)
    {
        var query = _dataContext.Messages
            .Count(UserSelector(username, sent));
        return query;
    }

The one that gets messages and pages them:

    /// <summary>
    /// Retrieves a list of messages from the data context for a user.
    /// </summary>
    /// <param name="username">The name of the user.</param>
    /// <param name="page">The page number.</param>
    /// <param name="itemsPerPage">The number of items to display per page.</param>
    /// <returns>An enumerable list of messages.</returns>
    public IEnumerable<Message> GetMessagesBy_Username(string username, int page, int itemsPerPage, bool sent)
    {
        var query = _dataContext.Messages
            .Where(UserSelector(username, sent))
            .OrderByDescending(x => x.SentDate)
            .Skip(itemsPerPage * (page - 1))
            .Take(itemsPerPage);
        return query;
    }

Obviously it is the call to UserSelector(string, bool) that is the big deal here. Here is what that method looks like:

    /// <summary>
    /// Builds an expression to be reused in a LINQ query.
    /// </summary>
    /// <param name="username">The name of the user.</param>
    /// <param name="sent">True if retrieving sent messages.</param>
    /// <returns>An expression to be used in a LINQ query.</returns>
    private Expression<Func<Message, bool>> UserSelector(string username, bool sent)
    {
        return x => ((sent ? x.FromUser : x.ToUser).Username.ToLower() == username.ToLower()) && (sent ? !x.SenderDeleted : !x.RecipientDeleted);
    }

So this method builds an expression to be evaluated and properly gets translated into it's SQL equivalent. The function in the expression evaluates to true if the username matches the username of either the sender or the recipient and deleted is false for either sender or recipient, based on the supplied boolean sent that gets serialized into the expression.

Here is a version of the above, that is closer to the example in my question. It's not as readable since my expression is grotesque but at lease I understand how it's working now:

    public int GetMessageCountBy_Username(string username, bool sent)
    {
        Expression<Func<Message, bool>> userSelector = x => ((sent ? x.FromUser : x.ToUser).Username.ToLower() == username.ToLower()) && (sent ? !x.SenderDeleted : !x.RecipientDeleted);

        var query = _dataContext.Messages
            .Count(userSelector);
        return query;
    }

This is actually pretty cool stuff. Took a lot of time to figure out but this seems really powerful. I now have a new understanding of how LINQ, lambdas, and expressions work :)

Thanks to everyone who contributed to this question! (including you artplastika, I still love you even if I don't love your answer)

Now I'm trying to clean up the lambda in the expression, if possible. Feel free to contribute: http://stackoverflow.com/questions/6142432/how-might-i-clean-up-this-lambda — CatDadCode, May 26 '11 at 17:14
I'm not sure about this but I don't think you need to have `Expresion<...>` in there. The fundamental difference between this and your original lambda is that this returns a bool (a predicate) and can be passed to the `Where` method directly. In the lambda in the question you called a delegate that returned a string inside your predicate. The fix is that you put all the logic in one lambda without calling another. It has nothing to do with `Expression` — Mike Two, May 26 '11 at 19:15
@Mike Two It is required. If I remove it, it still compiles but it fails at runtime. This is because the `sent` boolean is serialized into the expression, but not when it's a plain function. I think... In any case, it fails if I remove it. — CatDadCode, May 26 '11 at 19:24

score -1 · Answer 3 · answered May 26 '11 at 15:44

-1

Maybe this could be of use to help you abstract away conditions(predicates): http://www.albahari.com/nutshell/predicatebuilder.aspx

answered May 26 '11 at 15:44

Hasanain

925
8
16

He says 'vague references to other code on the web that I must "Google" for' on it)) – artplastika May 26 '11 at 15:48
At least he linked me to something artplastika. What happened to "Enough of me..."? At any rate, art is right. I just want to understand how to turn this `Func` into an `Expression` to be included in the query. Not how to "abstract away". – CatDadCode May 26 '11 at 15:53

How to turn this Func into an expression?

Edit

3 Answers3

Linked