Like - statement
Like - statement
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 ?
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 ?
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 ...
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 ...
We recommend you using provider-specific functions with EntitySQL:
Code: Select all
patienten.Where("Devart.Data.Oracle.LIKE(it.naam,'x')")
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
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
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)
2) turning on case insensitive comparison mode (Oracle, PostgreSQL)
We will post here when the corresponding build of dotConnect for Oracle is available for download.
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;
Code: Select all
var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.QueryOptions.CaseInsensitiveLike = true;
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 .
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 .