Page 1 of 1
Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Mon 12 Jun 2017 08:16
by Wain123
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.
Re: Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Tue 13 Jun 2017 12:50
by Shalex
"
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'
Re: Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Wed 14 Jun 2017 07:29
by Wain123
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 '%'.
Re: Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Fri 16 Jun 2017 17:14
by Shalex
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.
Re: Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Thu 22 Jun 2017 10:26
by Pinturiccio
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.
Re: Query with string equality comparison gets translated to SQL with string "like" comparison
Posted: Mon 26 Jun 2017 09:13
by Pinturiccio
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