Query with string equality comparison gets translated to SQL with string "like" comparison

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Wain123 » Mon 12 Jun 2017 08:16

If I write a query with string equality comparison, like this:

Code: Select all

ctx.Users.Where(z => z.Userid == "abc").Count();
It gets translated to the following SQL with a "like" comparison:

Code: Select all

SELECT COUNT(*)
FROM USERS "z"
WHERE "z".USERID LIKE 'abc'
In this case, the result is the same, but if instead of "abc" I use, for example, "a%c", the result will be incorrect since the '%' character has a special meaning in the "like" operator.

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

Re: Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Shalex » Tue 13 Jun 2017 12:50

"WHERE "z".USERID LIKE 'abc'" means that provider maps USERID property to CLOB/NCLOB column which is possible either with explicit specifying CLOB/NCLOB or with lack of .HasColumnType() / .HasMaxLength(). In a later case provider maps property to the biggest string type (CLOB/NCLOB). But if USERID is a key, it should be VARCHAR2/NVARCHAR2 by default.

Samples:

1.

Code: Select all

  modelBuilder.Entity<User>().Property<string>(x => x.USERID).HasColumnName(@"USERID").HasColumnType(@"NVARCHAR2").IsRequired().ValueGeneratedNever().HasMaxLength(50);
OR

Code: Select all

  modelBuilder.Entity<User>().Property<string>(x => x.USERID).HasColumnName(@"USERID").IsRequired().ValueGeneratedNever();
  modelBuilder.Entity<User>().HasKey(@"USERID");
->

Code: Select all

SELECT COUNT(*)
FROM C##SCOTT."user" "z"
WHERE "z".USERID = N'abc'
2.

Code: Select all

  modelBuilder.Entity<User>().Property<string>(x => x.USERID).HasColumnName(@"USERID").IsRequired().ValueGeneratedNever();
  modelBuilder.Entity<User>().HasKey(@"other_than_USERID_column");
->

Code: Select all

SELECT COUNT(*)
FROM C##SCOTT."user" "z"
WHERE "z".USERID LIKE 'abc'

Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Re: Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Wain123 » Wed 14 Jun 2017 07:29

That fixes my issue with the Userid, but what should I do with columns that are actual CLOBs? Using "like" gives me wrong results when the string contains a '%'.

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

Re: Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Shalex » Fri 16 Jun 2017 17:14

Wain123 wrote:That fixes my issue with the Userid, but what should I do with columns that are actual CLOBs? Using "like" gives me wrong results when the string contains a '%'.
We will investigate the question and notify you about the result.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Pinturiccio » Thu 22 Jun 2017 10:26

We have changed the behavior: now a DBMS_LOB.COMPARE call is generated for comparing CLOB/NCLOB columns.

We will post here when the corresponding build of dotConnect for Oracle is available for download.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Query with string equality comparison gets translated to SQL with string "like" comparison

Post by Pinturiccio » Mon 26 Jun 2017 09:13

New build of dotConnect for Oracle 9.4.299 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=35592

Post Reply