Like - statement

Like - statement

Postby 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

Postby 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

Postby wgkwvl » Fri 16 Sep 2011 13:41

solved it by using OracleFunctions.Like(string, string) instead of string.Contains(string)
wgkwvl
 
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Postby Shalex » Fri 16 Sep 2011 16:16

This is correct. Please use OracleFunctions.Like(string, string).
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby 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
wgkwvl
 
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Postby 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')")
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby 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
cp_Holger
 
Posts: 4
Joined: Wed 20 Jul 2011 09:04

Postby 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
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle