Entity Framework Query Xml

Question

How would you create this query in the Entity Framework:

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

Data column

is XML. Apparently this translates to an Entity Framework string ...

This is my beginning, but from here I do not know how to add there ...

 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 the 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

None of the Entity Framework query languages ​​(LINQ to Entities and eSQL) directly support nested XML queries. Therefore, you cannot do this. Unless you run an XML query after calling AsEnumerable() , which, of course, is somewhat undesirable in terms of performance.

Having said that, you can probably write a Store function in SSDL that makes this filter for you.

Open the EDMX file in an XML editor and try adding an item in the StorageModel section (i.e. SSDL). <CommandText> (I think this is what it is called) of this Store function, where you can write the corresponding T-SQL, and you can also refer to the parameters of the function. Sorry, I do not have an example of this convenient.

Having done this, you can call the Store function in eSQL ie 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 can also write the stub function in .NET so that you can also call this function in LINQ:

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 that all of the above code is just a pseudo code that I haven't really tested, I'm just trying to help you get started.

Hope this helps

Alex

Entity Framework Team

Answer 2

Two options:

• Write proc, which returns all the data needed to match the entity type, and place the SQL there. This method can use the XML index on the database server.
• Extract the data on the client, then create an XML document and use LINQ to XML. It is convenient for the programmer, but cannot use the XML index.

LINQ to Entities does not know about the functions of the XML database server.