Parameter in LIKE query

[Peter]

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

[Peter]

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

[Thomas]

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

[Thomas]

Hello Peter,

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

Kind regards,
Thomas
the Telerik team

[Nikesh]

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 

[Alexander]

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