Entity Framework Query Xml

Question

How would you build up this query with Entity Framework :

SELECT  *
FROM    TreeNodes
WHERE   data.value('(/edumatic/assessmentItem/@type)[1]', 'nvarchar(max)') like 'multiplechoice1'

data column is XML. Apparently this is converted to a string by the Entity Framework...

This is my start but from here I wouldn't know how to add the where...

var query = from e in edumatic3Context.TreeNodes
                        where e.Data.???????
                        select e;

            foreach (var treeNode in query)
                Console.WriteLine("{0} {1} {2} {3}", treeNode.TreeNodeId, treeNode.Name, treeNode.Type, treeNode.DateChanged);

I also tried something like following code but that didn't work either:

var sql = "SELECT VALUE treeNode FROM TreeNodes as treeNode WHERE data.value('(/edumatic/assessmentItem/@type)[1]', 'nvarchar(max)') like 'multiplechoice1'";
            var query = edumatic3Context.CreateQuery<TreeNodes>(sql);

foreach(...)

Answer 1

Two choices:

1. Write a proc which returns all the data required to map to an entity type, and put your SQL there. This method can use an XML index on the DB server.
2. Retrieve the data on the client, then construct an XML doc and use LINQ to XML. Convenient for the programmer, but can't use an XML index.

LINQ to Entities doesn't know about DB server XML features.

Answer 2

Neither of the Entity Framework's query languages (LINQ to Entities and eSQL) directly support nested XML queries. So you are not going to be able to do this sort of thing. Unless you run the XML query after a call to AsEnumerable(), which of course is somewhat undesirable from a performance perspective.

Having said that you can probably write a Store Function in the SSDL that does this filter for you.

Open the EDMX file up in an XML Editor, and try adding a element under the StorageModel section (i.e. the SSDL). The <CommandText> (I think that is what it is called) of that Store Function is where you could write the appropriate T-SQL and you can refer to parameters of the function too. Sorry I don't have an example of this handy.

Having done that you can call the Store Function in eSQL i.e. something like this:

SELECT VALUE treeNode FROM TreeNodes as treeNode WHERE 
StorageModelNamespace.MyXmlWrapperFunctionForNVarchar('(/edumatic/assessmentItem/@type)[1]', treeNode.Data) LIKE 'multiplechoice1'

In .NET 4.0 you will also be able to write a stub function in .NET so you can call that function in LINQ too:

i.e.

[EdmFunction("StorageModelNamespace", "MyXmlWrapperFunctionForNVarchar"]
public static string MyXmlHelper(string path, string data)
{
   throw new NotImplementedException("You can only call this function in a LINQ query");
}

then something like this:

var query = from e in edumatic3Context.TreeNodes
            where MyXmlHelper("(/edumatic/assessmentItem/@type)[1]", e.Data)
                 .StartsWith("multiplechoice1")
            select e;

Please note all the above code is just pseudo-code I haven't actually tested it, I'm just trying to help you get started.

Hope this helps

Alex

Program Manager Entity Framework Team