locked
Entity SQL LIKE Operator and ObjectParameter Problem RRS feed

  • Question

  • I'm building a query using ObjectParameters to pass values to Entity SQL query like so:

    Code Snippet

    using(Context ctx = new Context())

    {

    string query = "SELECT VALUE s FROM Subscribers AS s WHERE s.FirstName LIKE '%@paramFirstName%'";
    ObjectQuery<Subscribers> subscribersQuery = new ObjectQuery<Subscribers>(query, ctx);
    subscribersQuery.Parameters.Add(new ObjectParameter("paramFirstName", "John");
    List<Subscribers> subs = subscribersQuery.ToList();

    }



    This query is being executed just fine but I'm not receiving any data (And I know I have the data that matches the query in my store). However when I manually replace @paramFirstName with John and stop using ObjectParameters the query returns the expected results.
    I want to use ObjectParameters in all my Entity SQL queries. Any help would be much appreciated.

    Tuesday, May 13, 2008 2:27 PM

Answers

  • Hi,

     

    Please see the small correction inline to make sure that @paramFirstName is used as a parameter not as a string value.


    Code Snippet

    using(Context ctx = new Context())

    {

    string query = "SELECT VALUE s FROM Subscribers AS s WHERE s.FirstName LIKE '%' + @paramFirstName + '%'";
    ObjectQuery<Subscribers> subscribersQuery = new ObjectQuery<Subscribers>(query, ctx);
    subscribersQuery.Parameters.Add(new ObjectParameter("paramFirstName", "John");
    List<Subscribers> subs = subscribersQuery.ToList();

    }



    Hope that helps,

    Kati

    Tuesday, May 13, 2008 5:20 PM

All replies

  • Hi,

     

    Please see the small correction inline to make sure that @paramFirstName is used as a parameter not as a string value.


    Code Snippet

    using(Context ctx = new Context())

    {

    string query = "SELECT VALUE s FROM Subscribers AS s WHERE s.FirstName LIKE '%' + @paramFirstName + '%'";
    ObjectQuery<Subscribers> subscribersQuery = new ObjectQuery<Subscribers>(query, ctx);
    subscribersQuery.Parameters.Add(new ObjectParameter("paramFirstName", "John");
    List<Subscribers> subs = subscribersQuery.ToList();

    }



    Hope that helps,

    Kati

    Tuesday, May 13, 2008 5:20 PM
  • Thanks! that worked. It's strange because I've been using the parameters with out adding '+' and they work with other operators.
    Tuesday, May 13, 2008 5:32 PM
  • The difference really is that the parameter name when quoted is used as a literal. Thus your original query would return results if there were a subscriber whose first name contained “@paramFirstName%” (e.g. “aaaaaaaaaaaa@paramFirstNamebbbbbbbbb”.

    This is also consistent with TSQL.

    Hope that makes it clearer.

    Thanks,

    Kati

     

    Tuesday, May 13, 2008 7:05 PM