QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby PeterUser » Mon 27 Feb 2017 09:18

Hello

I have a string property in my model that can contain underscores (_). So I don't want _ treated as an wildcard character in my search. I followed option 1 posted here and set
Code: Select all
var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.QueryOptions.NoEscapeLike = true;


When I set my filter expression as
Code: Select all

expressions.Add(request => request.RequestNo.StartsWith(filter.RequestNo));

I get the SQL code
Code: Select all
WHERE ("Extent1"."RequestNo" LIKE :p__linq__0 ESCAPE '/')

which is exactly what I hoped to get.


The problem comes here:
When I set
Code: Select all
expressions.Add(request => request.RequestNo.ToLower().StartsWith(filter.RequestNo.ToLower()));

(please not the ".ToLower()"), I get
Code: Select all
WHERE (LOWER("Extent1"."RequestNo") LIKE LOWER(:p__linq__0) || '%')


As you see, the "ESCAPE '/'" is not present in the SQL.

Is this a bug or desired bahaviour?
Is there a way to achieve my goal without the .ToLower().StartWith()?

I am using Devart.Data.Oracle Version 9.1.131.0, together with Entity Framework 6.0.

Greetings
Peter
PeterUser
 
Posts: 5
Joined: Mon 27 Feb 2017 07:45

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby Shalex » Tue 28 Feb 2017 13:04

The option "config.QueryOptions.NoEscapeLike = true;" does work:
Code: Select all
string test = "S";
using (var context = new MyDbContext()) {
    var result = context.DEPTs.FirstOrDefault(a => a.DNAME.StartsWith(test));
    var result2 = context.DEPTs.FirstOrDefault(a => a.DNAME.StartsWith(test.ToLower()));
}

1) config.QueryOptions.NoEscapeLike = false;

a) SELECT
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM DEPT "Extent1"
WHERE ("Extent1".DNAME LIKE :p__linq__0 ESCAPE '/') AND ROWNUM <= 1

b) SELECT
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM DEPT "Extent1"
WHERE ("Extent1".DNAME LIKE LOWER(:p__linq__0) || '%') AND ROWNUM <= 1

2) config.QueryOptions.NoEscapeLike = true;

a) SELECT
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM DEPT "Extent1"
WHERE ("Extent1".DNAME LIKE :p__linq__0) AND ROWNUM <= 1

b) SELECT
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM DEPT "Extent1"
WHERE ("Extent1".DNAME LIKE LOWER(:p__linq__0) || '%') AND ROWNUM <= 1

We will investigate the issue why ESCAPE '/' is not generated in case 1) b) and notify you about the result.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby PeterUser » Wed 01 Mar 2017 09:04

Sorry for mixing that up. You are completely right. 1 b) is the issue I'm facing.

Thanks for your effort.
PeterUser
 
Posts: 5
Joined: Mon 27 Feb 2017 07:45

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby PeterUser » Tue 14 Mar 2017 15:41

Hello

Do you have any news on this issue?

Greetings
Peter
PeterUser
 
Posts: 5
Joined: Mon 27 Feb 2017 07:45

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby Shalex » Wed 15 Mar 2017 18:58

1 b) is a peculiarity of the EF engine functionality.

The behaviour of System.Data.SqlClient is the same.
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby PeterUser » Fri 17 Mar 2017 11:54

Hello Alex,

thanks for the information.

Do I understand correctly, that this is something inherent in LinqToSql that cannot be changed by dotconnect for oracle or myself?

Does anybody have an Idea how to circumvent this?

I have to search for in a column of indices that contain underscores ('_'). And I have to use .StartWith.

Do I have to resort to writing explicit SQL?

Greetings
Peter
PeterUser
 
Posts: 5
Joined: Mon 27 Feb 2017 07:45

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby Shalex » Tue 21 Mar 2017 09:58

PeterUser wrote:Do I understand correctly, that this is something inherent in LinqToSql that cannot be changed by dotconnect for oracle or myself?
That is correct.

PeterUser wrote:Does anybody have an Idea how to circumvent this?

I have to search for in a column of indices that contain underscores ('_'). And I have to use .StartWith.

Do I have to resort to writing explicit SQL?
As a workaround, please use the OracleFunctions.Like function, its overloads are:
Code: Select all
public static Boolean Like(String value, String pattern)
public static Boolean Like(String value, String pattern, String escape)
Shalex
Devart Team
 
Posts: 7531
Joined: Thu 14 Aug 2008 12:44

Re: QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Postby PeterUser » Wed 29 Mar 2017 14:38

Thanks for the suggestion. OracleFunctions.Like is a workaround I cal life with.
PeterUser
 
Posts: 5
Joined: Mon 27 Feb 2017 07:45


Return to dotConnect for Oracle