Like - statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Like - statement

Post by wgkwvl » Fri 16 Sep 2011 09:33

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 ?

wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Post by wgkwvl » Fri 16 Sep 2011 11:31

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 ...

wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Post by wgkwvl » Fri 16 Sep 2011 13:41

solved it by using OracleFunctions.Like(string, string) instead of string.Contains(string)

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 16 Sep 2011 16:16

This is correct. Please use OracleFunctions.Like(string, string).

wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Post by wgkwvl » Tue 20 Sep 2011 10:24

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

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 22 Sep 2011 11:06

We recommend you using provider-specific functions with EntitySQL:

Code: Select all

patienten.Where("Devart.Data.Oracle.LIKE(it.naam,'x')")

cp_Holger
Posts: 4
Joined: Wed 20 Jul 2011 09:04

Post by cp_Holger » Fri 18 Nov 2011 15:44

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

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 22 Nov 2011 09:14

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).

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 24 Nov 2011 17:05

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.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 09 Dec 2011 16:34

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 .

Post Reply