QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
PeterUser
Posts: 34
Joined: Mon 27 Feb 2017 07:45

QueryOptions.NoEscapeLike in combination with StartsWith and ToLower seems broken

Post by 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

Shalex
Devart Team
Posts: 8074
Joined: Thu 14 Aug 2008 12:44

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

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

PeterUser
Posts: 34
Joined: Mon 27 Feb 2017 07:45

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

Post by 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: 34
Joined: Mon 27 Feb 2017 07:45

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

Post by PeterUser » Tue 14 Mar 2017 15:41

Hello

Do you have any news on this issue?

Greetings
Peter

Shalex
Devart Team
Posts: 8074
Joined: Thu 14 Aug 2008 12:44

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

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

PeterUser
Posts: 34
Joined: Mon 27 Feb 2017 07:45

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

Post by 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

Shalex
Devart Team
Posts: 8074
Joined: Thu 14 Aug 2008 12:44

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

Post by 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)

PeterUser
Posts: 34
Joined: Mon 27 Feb 2017 07:45

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

Post by PeterUser » Wed 29 Mar 2017 14:38

Thanks for the suggestion. OracleFunctions.Like is a workaround I cal life with.

Post Reply