none
Convert lambda expression to SQL Query RRS feed

  • Question

  • Hello Guys,

    I've got a hard problem to solve..

    I've I run a query with linq like this:

    Person person = (from p in ctx.Persons  
                                where p.name.Contains("smith")  
                                select p).First() 


    The sql query executed against my DB is like select * from Persons where name like @p1 ... @p1 = '%smith%'

    However if I try the other way that is:

    IQueriable<Person> myQuery = ctx.Persons.Where(p => p.name.Contains("smith"));  
     
    List<Person> myList = myQuery.ToList(); 


    Then the sql executed against my DB is like "select * from Persons" with NO where clause, it means, it loads the whole table to only then process the where clause.

    Problem is that I'm constrained to the second case and the condition comes as a Func<Person,bool> paramenter from another code. I would like LINQ to convert this lambda expression into the correct sql where clause before running against my DB.

    Do you see the problem? Ideas?

    Thank you very much!
    Lucas
    Wednesday, March 4, 2009 6:45 PM

Answers

  • Hi Lucas,

    The parameter must be of type Expression<Func<Person, bool>> in order for LINQ to SQL to process it in the SQL query.
    • Marked as answer by HGG42 Thursday, March 5, 2009 7:34 PM
    Wednesday, March 4, 2009 9:06 PM
    Answerer

All replies

  • Hi Lucas,

    The parameter must be of type Expression<Func<Person, bool>> in order for LINQ to SQL to process it in the SQL query.
    • Marked as answer by HGG42 Thursday, March 5, 2009 7:34 PM
    Wednesday, March 4, 2009 9:06 PM
    Answerer
  • Great, I tested and that worked.
    Now.. intead of changing my method signature it would be much easier for me if I could just convert the
    Func<T,bool> into a Expression<Func<T,bool>> expression.

    I tried for searching internet but didn't find it. Do you know?

    Thanks!
    Lucas
    Thursday, March 5, 2009 12:08 AM
  • The techinque that I've seen is to define the Expression and then define a Func variable as well and assign the result of your expression.Compile() method to the Func variable. This allows you to have both and you still only have to define the function once in your code.

    public static Expression<Func<string,string>> ToUpperExpression = (myValue) => myValue.ToUpper();
    
    public static Func<string,string> ToUpperFunc = ToUpperExpression.Compile();
    • Proposed as answer by kainhart Monday, July 27, 2009 4:15 PM
    Monday, July 27, 2009 4:15 PM