This is a migrated thread and some comments may be shown as answers.
Parameter in LIKE query
5 Answers 104 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Peter
Top achievements
Rank 1
Peter asked on 24 Mar 2011, 03:39 PM
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

5 Answers, 1 is accepted

Sort by
0
Peter
Top achievements
Rank 1
answered on 25 Mar 2011, 11:46 AM
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

0
Thomas
Telerik team
answered on 26 Mar 2011, 09:30 AM
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
0
Thomas
Telerik team
answered on 20 Jun 2011, 08:53 AM
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.
0
Nikesh
Top achievements
Rank 1
answered on 30 Oct 2012, 06:55 PM
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 
0
Alexander
Telerik team
answered on 01 Nov 2012, 04:08 PM
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.
Tags
LINQ (LINQ specific questions)
Asked by
Peter
Top achievements
Rank 1
Answers by
Peter
Top achievements
Rank 1
Thomas
Telerik team
Nikesh
Top achievements
Rank 1
Alexander
Telerik team
Share this question
or