This question is locked. New answers and comments are not allowed.
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:
This generates the following SQL (from OpenAcces-Log)
Executing the query throws an exception at runtime:
The description field in the sql ce database is of type "ntext".
If I change the LINQ query to
The generated SQL looks different and runs throught with out any exception.
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
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