Problem with CLOB support

[crazypit]

Hello,

I need to run the following LINQ statement:

List values = (from objDocument in dataContext.Documents
from objUdfValue in dataContext.UdfsValues
where objDocument.GroupId == documentGroupId && objDocument.ParentId == null
&& objDocument.Id == objUdfValue.RefId
&& objUdfValue.UdfId == 1181
group objDocument by objUdfValue.Value
into document
select document.Key).ToList();

where the column objUdfValue.Value is a NCLOB field in Oracle.

It translates to the following SQL statement:

SELECT t2.UV_VALUE AS "Value"
FROM DOCUMENTS t1, UDFS_VALUES t2
WHERE (t1.DOCUMENT_GROUP_ID = 2) AND (t1.DOCUMENT_PARENT_ID IS NULL) AND (t1.DOCUMENT_ID = t2.UV_REF_ID) AND (t2.UDF_ID = 1181)
GROUP BY t2.UV_VALUE

This results in an Oracle error, probably because one cannot use a CLOB column in a group by clause as is. Nevertheless, the 4000 characters limitation is enough for me and the data i need to group by. Therefore, if the SQL statement was like this:

SELECT to_char(t2.UV_VALUE) AS "Value"
FROM DOCUMENTS t1, UDFS_VALUES t2
WHERE (t1.DOCUMENT_GROUP_ID = 2) AND (t1.DOCUMENT_PARENT_ID IS NULL) AND (t1.DOCUMENT_ID = t2.UV_REF_ID) AND (t2.UDF_ID = 1181)
GROUP BY to_char(t2.UV_VALUE)

everything works ok. However, i could not find a proper way to generate this using Devart LINQ. It would be even better for CLOB support if one could generate a statement like:

SELECT dbms_lob.substr(t2.UV_VALUE, 4000) AS "Value"
FROM DOCUMENTS t1, UDFS_VALUES t2
WHERE (t1.DOCUMENT_GROUP_ID = 2) AND (t1.DOCUMENT_PARENT_ID IS NULL) AND (t1.DOCUMENT_ID = t2.UV_REF_ID) AND (t2.UDF_ID = 1181)
GROUP BY dbms_lob.substr(t2.UV_VALUE, 4000)

where the number of characters can be defined by the user. Maybe the .NET String.Substring method is appropriate.

When i try to use this method, the generated SQL does not work...

[StanislavK]

Thank you for your report, we will analyze the possibility of implementing the support for grouping by LOB fields and for using the Substring() method on them. We will inform you about the results here.

[StanislavK]

We have supported using the Substring method on LOBs: the DBMS_LOB.SUBSTR function is now used for this instead of the common Oracle function SUBSTR. To group by a LOB field, please use Substring(0,4000) on it.

These changes are available in the new 5.70.180 build of dotConnect for Oracle. You can download it from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version only) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information about the improvements and fixes available in dotConnect for Oracle 5.70.180, please refer to
http://www.devart.com/forums/viewtopic.php?t=19236