Devart Forums

Posted: **Mon 10 May 2010 16:16**

It appears that the XMLTYPE is not supported in DevArt's LINQ to SQL product for Oracle.

We use Microsoft's LINQ to SQL for SQL Server 2005 databases. We use the XML data type in SQL Server so that we can do server side XQuery searches and so on.

When we converted our database to Oracle using the equivalent XMLTYPE and tried to use DevArt's LINQ to Oracle SQL we found that it was only possible to return the XML content as a C# String rather than a XDocument or XElement that Microsoft maps SQL Server XML types to by default.

This is a problem for us because our code is already based around the XElement type for XML SQL types.

This means that we have to make 100s of code changes in the C# code.

For example we would have to make 100s of changes like this:

Code: Select all

#if oracle

XElement x = XDocument.Parse((from p in db.MyTable
                                       where p.Id == id
                                       select p.Xml).SingleOrDefault()).Root;

#else

XElement x = (from p in db.MyTable
             where p.Id == id
             select p.Xml).SingleOrDefault();
#endif

This also defeats the purpose of using the XML type somewhat as the data is stored as an XMLTYPE in the database but then converted to string by LINQ to Oracle then back to XML!

I believe it is possible to modify the DevArt LINQ templates, we attempted to do this under guidance of support but it did not work:

We added the following line to the template:

Code: Select all

if(property.ServerDataType == "XMLTYPE") propType = "XDocument";

However, the generated code did not compile and we had to also adjust the following lines in the generated C# code.

private string _Xml; ---> private XDocument _Xml;

partial void OnXmlChanging(string value); ---> partial void OnXmlChanging(XDocument value);

It then compiled OK.

However, when we use LINQ to retrieve the XML from the Oracle table we then get the following runtime errors:

{"Unable to cast object of type 'System.String' to type 'System.Xml.Linq.XDocument'."}

Is it possible to support Oracle XMLTYPES with this product somehow or do we need to find an alternative solution?

It would be a shame not to purchase the product because of this since it appears to work well otherwise.

Posted: **Tue 11 May 2010 15:13**

I have sent you the template dealing with XDocument.
Please let me know if anything goes wrong.

Posted: **Tue 11 May 2010 15:37**

Your template did not work.

Steps to reproduce the problem are:

1. Create an Oracle table with a column/field of type XMLTYPE named Xml.

2. Create a new Visual Studio 2008 C# forms project.

3. Go to tools->Entity developer->Templates.. and create and paste in your supplied template.

4. Add new item->Devart LINQ to SQL Model

5. Right click on the lqml designer surface, select Edit and set the new template.

6. Create a new database connection.

7. Drag the table into the design surface.

8. Type of the XML field will still show as String.

9. Compiling the code gives the error:
a. The type or namespace name 'XDocument' could not be found (are you missing a using directive or an assembly reference?)

10. Fix the error by adding using System.Xml.Linq; to the DataContext1.Designer.cs file.

11. Compile again and get the errors:
a. Operator '!=' cannot be applied to operands of type 'string' and 'System.Xml.Linq.XDocument'
b. The best overloaded method match for 'OracleTest.DataModule.OnXmlChanging(string)' has some invalid arguments
c. Argument '1': cannot convert from 'System.Xml.Linq.XDocument' to 'string'

12. Fix the errors by changing

private string _Xml;
to
private XDocument _Xml;

and

partial void OnXmlChanging(string value);
to
partial void OnXmlChanging(XDocument value);

13. Code now compiles. Test code as follows:

var x = from p in db.MyTable
select p.Xml;

foreach (XDocument xml in x)
{
...
}

14. Get the run time error

Unable to cast object of type 'System.String' to type 'System.Xml.Linq.XDocument'.

Posted: **Thu 13 May 2010 15:48**

We are investigating this situation.
I have sent you the correct template.

Devart Forums

Oracle XMLTYPE not supported in LINQ to SQL?

Oracle XMLTYPE not supported in LINQ to SQL?