locked
LINQ: Where() using expression tree in compiled query

    Question

  • Hey, I hope someone can help me on this or give me some pointers.

    I have a function that returns an expression tree, like so:

    Code Snippet
    public class PublishableItem
    {
       // ...

       public static Expression<Func<T, bool>> IsPublished<T>()

          where T: PublishableItem
       {
          return i => i.State >= 1;
       }
    }

    When I use this function in a regular LINQ query, it works fine:

    Code Snippet
    IQueryable<Category> categories = (from c in db.Categories select c).Where(PublishableItem.IsPublished<Category>());

    However, when I use the same function in a compiled query, things go wrong:

    Code Snippet
    Func<PageContext, IQueryable<Category>> func = CompiledQuery.Compile((PageContext db) => (from c in db.Categories select c).Where(PublishableItem.IsPublished<Category>()));
    IQueryable<Category> categories = func(db);

    It compiles fine, but on execution it throws a System.NotSupportedException:

    Code Snippet
    "Unsupported overload used for query operator 'Where'."
    Stack trace:
       at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
       at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
       at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
       at System.Data.Linq.SqlClient.QueryConverter.VisitLambda(LambdaExpression lambda)
       at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
       at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)
       at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Compile(Expression query)
       at System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args)
       at System.Data.Linq.CompiledQuery.Invoke[TArg0,TResult](TArg0 arg0)

    I tried using the actual lambda expression in the compiled query and that does work:

    Code Snippet
    Func<PageContext, IQueryable<Category>> func = CompiledQuery.Compile((PageContext db) => (from c in db.Categories where c.State >= 1 select c));
    IQueryable<Category> categories = func(db);

    Even when I store the expression in a variable, thus eliminating the function call in the compiled expression, like so, it goes wrong:


    Code Snippet
    Expression<Func<Category, bool>> expr = PublishableItem.IsPublished<Category>();
    Func<PageContext, IQueryable<Category>> func = CompiledQuery.Compile((PageContext db) => (from c in db.Categories select c).Where(expr));
    IQueryable<Category> categories = func(db);

    I've tried 'simplifying' things by compiling the expression tree into a function:

    Code Snippet
    Expression<Func<Category, bool>> expr = PublishableItem.IsPublished<Category>();
    Func<Category, bool> compiledExpr = expr.Compile();
    Func<PageContext, IQueryable<Category>> func = CompiledQuery.Compile((PageContext db) => (from c in db.Categories where compiledExpr.Invoke(c) select c));
    IQueryable<Category> categories = func(db);

    This throws another System.NotSupportedException:

    Code Snippet
    "Method 'Boolean Invoke(SitePublisherQ.Structure.GenericCategory)' has no supported translation to SQL."
    Stack trace:
       at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitMethodCall(SqlMethodCall mc)
       at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
       at System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp)
       at System.Data.Linq.SqlClient.SqlVisitor.VisitSelectCore(SqlSelect select)
       at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitSelect(SqlSelect select)
       at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
       at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
       at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Compile(Expression query)
       at System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args)
       at System.Data.Linq.CompiledQuery.Invoke[TArg0,TResult](TArg0 arg0)

    I hope one of you guys has a suggestion. I have tried, by the way, Tomas Petricek's Expander functions, but unfortunately they're no good in this situation.
    Monday, February 25, 2008 8:18 PM

Answers

  • When you first built the query the expression called your expression-tree building function and the query was stitched together without a reference to your function in it (just tree that it built).  When you made a compiled query, the entire query expression was captured as an expression tree including the call to your method.  However, LINQ to SQL is not actually executing the expression tree, it is translating it to SQL.  When it gets to the expression node that represents the call to your method it is expecting an inline lambda expression.  That's why its confused. 

    Tuesday, February 26, 2008 12:51 AM
  • The query expander posted on tomasp.net doesn't enable me to use the function in a compiled expression. It did however give me some pointers as to how to implement an expander myself.

    The code is a bit long to be posted here, but similar to tomasp.net's expander, I visit every expression in the expression tree and if the node is a MethodCallExpression which calls a method that returns an expression tree, I replace that MethodCallExpression by the expression tree returned by invoking the method.
    Wednesday, May 28, 2008 11:41 AM

All replies

  • When you first built the query the expression called your expression-tree building function and the query was stitched together without a reference to your function in it (just tree that it built).  When you made a compiled query, the entire query expression was captured as an expression tree including the call to your method.  However, LINQ to SQL is not actually executing the expression tree, it is translating it to SQL.  When it gets to the expression node that represents the call to your method it is expecting an inline lambda expression.  That's why its confused. 

    Tuesday, February 26, 2008 12:51 AM
  • To make you example work you have to "expand" the expression before passing it to LinqToSQL query provider.

    You can look for details and already implemented query provider wrapper here: http://tomasp.net/blog/linq-expand.aspx

     

    Tuesday, February 26, 2008 9:54 AM
  • The query expander posted on tomasp.net doesn't enable me to use the function in a compiled expression. It did however give me some pointers as to how to implement an expander myself.

    The code is a bit long to be posted here, but similar to tomasp.net's expander, I visit every expression in the expression tree and if the node is a MethodCallExpression which calls a method that returns an expression tree, I replace that MethodCallExpression by the expression tree returned by invoking the method.
    Wednesday, May 28, 2008 11:41 AM
  • I'm trying to do something similar with an ExpressionVisitor that's already doing some other translations for me, and would like to discuss the matter with you further. I see that this post is from a while back, but hopefully you're getting notifications of responses. If you have published your ExpressionVisitor somewhere, it would like to see what you've done.

    MG2
    Friday, December 11, 2009 3:44 PM
  • Hi Eric,

      could you please share the solution(with sample code) if you have solved this problem even i am facing the same?

    Sunday, July 04, 2010 1:35 AM