76

In my EF later, I'm trying to pass in an anonymous function to be used as part of my Linq query. The function would pass in an INT and return a BOOL (u.RelationTypeId is an INT). Below is a simplified version of my function:

public IEnumerable<UserBandRelation> GetBandRelationsByUser(Func<int, bool> relation)
{
    using (var ctx = new OpenGroovesEntities())
    {
        Expression<Func<UsersBand, bool>> predicate = (u) => relation(u.RelationTypeId);

        var relations = ctx.UsersBands.Where(predicate);

        // mapping, other stuff, back to business layer
        return relations.ToList();
    }
}

However, I get the error stated above. It seems like I'm going everything correct by building a predicate from the function. Any ideas? Thanks.

Ryan Peters
  • 7,608
  • 8
  • 41
  • 57

4 Answers4

127

I was getting this very error and I'm using Entity Framework with PredicateBuilder by Joe Albahari to build dynamic where clauses. If you happen to be in the same condition, you should call the AsExpandable method:

If querying with Entity Framework, change the last line to this:

return objectContext.Products.AsExpandable().Where(predicate);

This method is part of LINQKIT DLL that you can grab here or through a NuGet package here.

Everything works fine now. :)

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
  • 2
    Do you know if `AsExpandable()` causes any performance impact? I am using a generic repository and only use the predicate builder occasionally, is it worth it to create a separate repository method for predicate building? – Cody Apr 25 '14 at 18:02
  • 1
    @Cody... I have no idea. You'd better ask that to Joe Albahary directly. :) This is his twitter: https://twitter.com/linqpad – Leniel Maccaferri Apr 25 '14 at 23:32
  • 1
    @DoctorOreo Have you figured out the performance impact? I'm in the same situation as you – Marc Sep 29 '15 at 12:16
  • @Marc you know what, I've been using it since I guess April of 2014 and I haven't noticed any issues. Going on a year and a half now in a production application, nobody has complained. (this is a system with ~50 users dealing with anywhere from 10,000 to 5,000,000 records) – Cody Sep 29 '15 at 14:13
  • 4
    Looks like with the latest version you don't need to use `AsExpandable()` to pass in the predicate to `Where()`. Possibly because of the new `PredicateBuilder.New()` starting point? – Gup3rSuR4c Dec 11 '17 at 21:59
53

You're trying to pass an arbitrary .NET function in... how could the entity framework hope to translate that into SQL? You can change it to take an Expression<Func<int, bool>> instead, and build the Where clause from that, although it won't be particularly easy, because you'll need to rewrite the expression with a different parameter expression (i.e. replacing whatever parameter expression is in the original expression tree with the expression of calling u.RelationTypeId).

To be honest, for the sake of just specifying u.RelationTypeId in the lambda expression that you use to create the expression tree to pass into the method, you'd be better off just using:

public IEnumerable<UserBandRelation> GetBandRelationsByUser(
    Expression<Func<UsersBand, bool>> predicate)
{
    using (var ctx = new OpenGroovesEntities())
    {
        var relations = ctx.UsersBands.Where(predicate);

        // mapping, other stuff, back to business layer
        return relations.ToList();
    }
}
Hooman Bahreini
  • 14,480
  • 11
  • 70
  • 137
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks. That's sort of what I thought, but my problem is that UserBandRelation is a model while UsersBand is my entity model. I use automapper to map these. Automapper has a problem if I try something crazy like mapping the expressions. Is there any way around this, in order to keep my entities separate? Thanks. – Ryan Peters Mar 12 '11 at 19:48
  • @Ryan: As I say, you could try to go about doing expression tree rewriting. It's just not something I've tried... I also haven't used Automapper, so I'm not quite sure where that comes into it. – Jon Skeet Mar 12 '11 at 19:50
  • 1
    @Ryan somewhere here on SO is a sample I wrote that will happen flatten `Expression.Invoke` for the purpose of allowing EF to work with sub-expressions (something L2S supports out-of-the-box). It still can't work with delegates, of course - In just saying that a rewriter is here somewhere... – Marc Gravell Mar 12 '11 at 19:54
  • 1
    @Ryan [here](http://stackoverflow.com/questions/1717444/combining-two-lamba-expressions-in-c/1720642#1720642), in fact - just enable "inline" – Marc Gravell Mar 12 '11 at 19:57
  • 2
    @Ryan @Marc There is also [LinqKit](http://www.albahari.com/nutshell/linqkit.aspx), which incorporates many features needed for manipulating expressions. – Chris Pitman Mar 14 '11 at 00:04
  • 2
    I second Chris's suggestion of LinqKit => it makes chaining Expression> predicates together extremely easy. – user483679 Nov 19 '13 at 21:07
  • @JonSkeet: That's great, but I have a similar case where the property I need to evaluate is determined at run time by the user. So I tried `Expression> getProperty = h => h.NumberOfDiamonds;` and then `query = query.Where(h => getProperty(h) == 0)` but I get the error `getProperty is a variable but is used like a method`. Is that possible? – Jonathan Wood Apr 18 '14 at 22:10
5

You can call the Expand() method on your predicate before the Where request.

d219
  • 2,707
  • 5
  • 31
  • 36
Zerelli Seifeddine
  • 81
  • 3
  • 9
  • 1
    This works too ! And I don't know what AsExpandable do, so I prefer to just call Expand when I really want to filter the query. – Stephane Mathis Nov 24 '15 at 08:53
-7

I know this answer is really late, but I ran into the same problem and it led me here, so I thought I'd share my solution.

I read Leniel's answer, and it gave me an idea. The default types have the method "AsEnumerable()" which behaves the same way, alleviating the issue.

Yushatak
  • 1
  • 12
    `AsEnumerable()` will cause the `Where()` function to be invoked by Linq to Objects and not Linq to Entities. – haim770 Nov 27 '13 at 19:29