Entity Framework to Query Xml

Question

I have an issue. Perhaps one of you experts can help out here. The table that I am querying has 2 xml columns. I need to perform query on xml data in these columns too ( in my advanced search functionality ). Problem is I am using Entity Framework and it doesnt support that. Even if I try to do "ObjectQuery.Execute" it causes Syntax Error. So what are my options here ?

1. Modify the table and add columns for the data that is in XML which I need to query, so that I can use Entity Framework to perform the operation ?
2. Just for Advanced Search use ODBC and do regular query where I would be able to use SQLXml ? What are the potential threats here [ like multiple connection, too many connections, Abomination of design/Architect etc ] ?
3. Or can anyone propose a better solution to this ?

Thanks,

Ali Nahid

Answer 1

EF maps SQL Server XML type columns to strings. So for this definition:

CREATE TABLE [dbo].[XmlData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XmlTestColumn] [xml] NOT NULL
)

you would have the following entity:

public partial class XmlData
{
    public int ID { get; set; }
    public string XmlTestColumn { get; set; }
}

and would be able load the string into XmlDocument, etc. That's one approach.

If you prefer to issue a native XML query against SQL Server, you'll need to

1. Define or choose a suitable entity type;

2. Use Database.SqlQuery with a your XML query:

   var query = context.Database.SqlQuery(@"SELECT it.Id, it.Name, it.SomeData, t2.someotherdata FROM Table1 as it CROSS APPLY ...");

ObjectQuery works against the conceptual model and does not support native queries.