Generated comparison for Oracle CLOB column is incorrect

[Constantine]

Hi.

Case:
1. Use Oracle database.
2. Create table with CLOB column.
3. Write LINQ query to compare content of this column with string. For example, 'database.Users.Any(u => u.Email.ToUpper() == email.ToUpper());'.

Expected result:
Boolean value.

Real result:
Exception "Failure: Count(*) query failed: Telerik.OpenAccess.RT.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB".

Reason:
TDA generated wrong SQL query 'SELECT COUNT(1) FROM ( SELECT a."Id" COL1, a."Email" COL2 FROM "Users" a WHERE upper(a."Email") = ?'. Right query is: 'SELECT COUNT(1) FROM ( SELECT a."Id" COL1, a."Email" COL2 FROM "Users" a WHERE DBMS_LOB.COMPARE(upper("Login"), ?) = 0'.

Question:
Will you change comparison generation for Oracle CLOB columns?

[Ralph Waldenmaier]

Hi Constantine,
The DBMS_LOB.COMPARE function call is necessary to ensure that CLOB types are compared correctly on the server. Unfortunately, in this situation the UPPER function call does not return the appropriate type to perform the operation correctly.

Though there is a way to fullfil your requirements by leveraging the .SQL LINQ extension. Please see this blog post for an example.

In your case it would look like this.

database.Users.Any(u => "UPPER({0}) = UPPER({1})".SQL<bool>(u.Email, email));

Please double check if the UPPER really meets your requirements because the documentation is not 100% clear if the same amount of data is returned as stored in the CLOB. This means, a CLOB can have a length bigger than 4000 chars. Probably the UPPER returns a max of 4000 chars.

I hope this information is helpful for you.
Do come back in case you need further assistance.


Regards,
Ralph Waldenmaier
Telerik