Page 1 of 1
Like - statement
Posted: Fri 16 Sep 2011 09:33
by wgkwvl
Hi,
I want to execute a query
select naam from patient where patient.naam like '%VAN%DAMME%'
wanting to find 'VANDAMME' and 'VAN DAMME' and anything like it.
with linq ( Enitity Framework) :
patient.where(q=>q.Contains("VAN%DAMME"))
the following is generated :
select naam from patient where patient.naam like '%VAN/%DAMME%' ESCAPE '/'
I fear this wont be solvable till the REG_EXP is supported ?
Posted: Fri 16 Sep 2011 11:31
by wgkwvl
This was apparently working correctly in version 6.0.86.0 ->
So i seems this is a problem that started when we upgrade to version 6.30.202
version 6.0.86.0 generated a query :
SELECT
"Extent1".PATIENT_NR AS PATIENT_NR,
"Extent1".NAAM AS NAAM,
"Extent1".VOORNAAM AS VOORNAAM,
"Extent1".GSM_NR AS GSM_NR,
"Extent1".VOORLOPIG_IND AS VOORLOPIG_IND,
"Extent1".AFDELING_CD AS AFDELING_CD,
"Extent1".GEBLOKKEERD_IND AS GEBLOKKEERD_IND,
"Extent1".HUISARTS AS HUISARTS,
"Extent1".GEBOORTE_DT AS GEBOORTE_DT
FROM (SELECT
VW_PATIENTEN_10.PATIENT_NR AS PATIENT_NR,
VW_PATIENTEN_10.NAAM AS NAAM,
VW_PATIENTEN_10.VOORNAAM AS VOORNAAM,
VW_PATIENTEN_10.GSM_NR AS GSM_NR,
VW_PATIENTEN_10.VOORLOPIG_IND AS VOORLOPIG_IND,
VW_PATIENTEN_10.GEBLOKKEERD_IND AS GEBLOKKEERD_IND,
VW_PATIENTEN_10.AFDELING_CD AS AFDELING_CD,
VW_PATIENTEN_10.GEBOORTE_DT AS GEBOORTE_DT,
VW_PATIENTEN_10.HUISARTS AS HUISARTS
FROM AFDA.VW_PATIENTEN_10 VW_PATIENTEN_10) "Extent1"
WHERE ("Extent1".NAAM LIKE '%VAN%DAMME%') AND ...
Posted: Fri 16 Sep 2011 13:41
by wgkwvl
solved it by using OracleFunctions.Like(string, string) instead of string.Contains(string)
Posted: Fri 16 Sep 2011 16:16
by Shalex
This is correct. Please use OracleFunctions.Like(string, string).
Posted: Tue 20 Sep 2011 10:24
by wgkwvl
Now comes the hard part.
I am using the dynamic linq library :
patienten.where("naam = 'x' ");
any idea how 'OracleFunctions.Like' fits in here ?
patienten.where("OracleFunctions.Like(naam,'x')") does not work
Posted: Thu 22 Sep 2011 11:06
by Shalex
We recommend you using provider-specific functions with EntitySQL:
Code: Select all
patienten.Where("Devart.Data.Oracle.LIKE(it.naam,'x')")
Posted: Fri 18 Nov 2011 15:44
by cp_Holger
Hi!
We have a similar problem. Up to version 6.10 we used the "_" underscore as a wildcard for our statements (and told our customers to do so).
The above solution does not help us as we use the Dynamic LINQ Library in a generic component, not LINQToEntities.
Do you have any solution to get the wildcard feature back?
Thanks
Posted: Tue 22 Nov 2011 09:14
by Shalex
There was an incorrect behaviour in the previous versions of our providers and it is fixed now (escaping). We are planning to implemented the configuration option in the next build to turn off escaping (old behaviour).
Posted: Thu 24 Nov 2011 17:05
by Shalex
The config.QueryOptions.NoEscapeLike and CaseInsensitiveLike options are implemented to manage LIKE behaviour when using Linq to Entities extention methods .Contains() .EndsWith() .StartsWith():
1) turning off LIKE's ESCAPEs (Oracle, MySQL, PostgreSQL, SQLite)
Code: Select all
var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.QueryOptions.NoEscapeLike = true;
2) turning on case insensitive comparison mode (Oracle, PostgreSQL)
Code: Select all
var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.QueryOptions.CaseInsensitiveLike = true;
We will post here when the corresponding build of dotConnect for Oracle is available for download.
Posted: Fri 09 Dec 2011 16:34
by Shalex
New version of dotConnect for Oracle 6.60 is released!
It can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=22836 .