Bug Querying CLOB Columns using Entity SQL.
Posted: Wed 26 Jan 2011 14:25
I have a table, Metadata, with the columns:
I get the System.Data.EntityCommandExecutionException exception with the following error message: ORA-00932: inconsistent datatypes: expected - got CLOB
However, the following query works:
And this query also works:
I looked into DB Monitor to see what was going on, and I found that the first query (the error case) produced the following SQL:
The second query:
The third query:
It seems in the first case, there a bug where no special handling done for comparing CLOB values.
I am using Devart dotConnect for Oracle Professional 6.0.86.0.
- Name: VARCHAR2(50)
Value: CLOB
Code: Select all
var results = context.CreateQuery("SELECT VALUE m FROM Metadatas WHERE m.Value = 'Value1'").ToList();
However, the following query works:
Code: Select all
var results = context.Metadatas.Where(x => x.Value == "Value1").ToList();
Code: Select all
results = context.CreateQuery("SELECT VALUE m FROM Metadatas AS m WHERE m.Value = @v", new ObjectParameter("v", "Value1")).ToList();
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE = 'Value1'
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE LIKE TO_CLOB('Value1')
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE LIKE :v
I am using Devart dotConnect for Oracle Professional 6.0.86.0.