Parameter in LIKE query

6 posts, 0 answers
  1. Peter
    Peter avatar
    4 posts
    Member since:
    May 2009

    Posted 24 Mar 2011 Link to this post

    Hi,

    I've got a problem with parameters in a LIKE query, generated by OpenAccess for a LINQ query.
    The underlying database is SQL CE.

    Here the LINQ query:
    string projectQuery = "<ProjectNumber>001</ProjectNumber>"
      
    return (from data in this.ObjectScope.Extent<ExchangeData>()
            where data.Description.Contains(projectQuery)
            select data).SingleOrDefault();

    This generates the following SQL (from OpenAcces-Log)
    SELECT a.[ID] COL1, a.[Description] COL2 FROM [ExchangeData] a WHERE a.[Description] LIKE '%' + ? + '%' ESCAPE '\'

    Executing the query throws an exception at runtime:
    Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Der Datentyp ist für die arithmetische Operation ungültig. [ Data type (if known) = nvarchar ]
       bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
       bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
       bei OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

    The description field in the sql ce database is of type "ntext".

    If I change the LINQ query to
    return (from data in this.ObjectScope.Extent<ExchangeData>()
            where data.Description.Contains("<ProjectNumber>001</ProjectNumber>")
            select data).SingleOrDefault();


    The generated SQL looks different and runs throught with out any exception.
    SELECT a.[ID] COL1, a.[Description] COL2 FROM [ExchangeData] a WHERE a.[Description] LIKE '%<ProjectNumber>001</ProjectNumber>%' ESCAPE '\'

    So the problem seems to be that concatenating strings and a parameter in the LIKE query is not possible. A question on stackoverflow.com concerning sql like queries on a sql ce database in general states the same: http://stackoverflow.com/questions/1916248/how-to-use-parameter-with-like-in-sql-server-compact-edition#1920702

    The question is: how can I tell OpenAccess to either don't wrap the parameter in "%" (adding "%" on start and end of the string does not help) or include the parameter value directly in the query?

    Best regards,
    Peter
  2. Peter
    Peter avatar
    4 posts
    Member since:
    May 2009

    Posted 25 Mar 2011 Link to this post

    I found kind of a workaround by using dynamic linq (http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx):

    .Where("Description.Contains(@0)", projectQuery)

    The generated SQL does not use parameters:
    a.[Description] LIKE '%<ProjectNumber>001</ProjectNumber>%' ESCAPE '\'

    This prevents OpenAccess from using parameters in the LIKE clause of the sql query. But it would be better, if OpenAccess would generate valid SQL for queries containing "LIKE" on SQL CE, so you do not have to use this detour.

    Peter

  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 26 Mar 2011 Link to this post

    Hi Peter,

    nice you found a workaround for your issue. I think you could also use our new SQL extension method, that
    provides a way to incorporate an SQL island within a LINQ expression.

    return
     (from data in this.ObjectScope.Extent<ExchangeData>()
            where "{0} LIKE {1}".SQL<bool>(data.Description, projectQuery)
            select data).SingleOrDefault();

    You would then have to add the % signs by yourself.
    We will look into this issue to find out a way to avoid such detours.
     
    Regards,
    Thomas
    the Telerik team
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 20 Jun 2011 Link to this post

    Hello Peter,

    this issue will no longer be present in the upcoming Q2 2011 version. 

    Kind regards,
    Thomas
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  6. Nikesh
    Nikesh avatar
    12 posts
    Member since:
    Nov 2011

    Posted 30 Oct 2012 Link to this post

    Hi Thomas

    I'm using 2012 Q3 version. How do I use the like command in linq. I want to find all users in my user table that match an emailstring in the email column. this is how I do it in entity framework:

            var userList = from u in context.Users.OrderBy(u => u.UserName)
                           where u.ApplicationName == applicationName && SqlMethods.Like(u.Email, emailToMatch)
                           select u;


    But looks like we can use But looks like we can use SqlMethods.Like. How do we do this in OpenAccess?

    Regards Nikesh 
  7. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 01 Nov 2012 Link to this post

    Hi Nikesh,

    OpenAccess translates the String.Contains method to a LIKE statement, so you should be able to use the following code:
    var userList = from u in context.Users.OrderBy(u => u.UserName)
                   where u.ApplicationName == applicationName && u.Email.Contains(emailToMatch)
                   select u;

    Kind regards,
    Alexander
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top
DevCraft banner