实体框架查询Xml

问题描述

  SELECT * 
 FROM TreeNodes 
 WHERE data.value（'（/ edumatic / assessmentItem / @ type）[1]'，'nvarchar（max）'）像'multiplechoice1'
  

数据列是XML。显然这被实体框架转换成一个字符串...

这是我的开始，但从这里我不知道如何添加在哪里...

  var query = from e in edumatic3Context.TreeNodes 
其中e.Data。 
选择e; 
 
 foreach（查询中的var treeNode）
 Console.WriteLine（{0} {1} {2} {3}，treeNode.TreeNodeId，treeNode.Name，treeNode.Type， treeNode.DateChanged）; 
  

我也尝试过像下面的代码，但是没有工作：

  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（...）
  

解决方案

实体框架的查询语言（LINQ to Entities和eSQL）都不直接支持嵌套的XML查询。所以你不可能做这样的事情。除非您在调用 AsEnumerable（）之后运行XML查询，这从性能角度来看当然不太可取。

在XML编辑器中打开EDMX文件，然后尝试在StorageModel部分（即SSDL）下添加一个元素。该商店功能的< CommandText> （我认为这是所谓的），您可以在其中编写适当的T-SQL，您可以参考功能也。对不起，我没有这个方便的例子。

完成后，您可以在eSQL中调用Store Function，即这样：

  SELECT VALUE treeNode FROM TreeNodes as treeNode WHERE 
 StorageModelNamespace.MyXmlWrapperFunctionForNVarchar（'（/ edumatic / assessmentItem / @ type）[1]'，treeNode。数据）LIKE'multiplechoice1'
  

在.NET 4.0中，您还可以在.NET，所以你也可以在LINQ中调用该函数：

ie

  [EdmFunction（StorageModelNamespace，MyXmlWrapperFunctionForNVarchar] 
 public static string MyXmlHelper（string path，string data） 
 {
 throw new NotImplementedException（您只能在LINQ查询中调用此函数）; 
} 
  

然后这样：

  var query = from e in edumatic3Context.TreeNodes 
其中MyXmlHelper（（/ edumatic / assessmentItem / @ type）[1]，e.Data）
 .StartsWith（multiplechoice1）
选择e; 
  

请注意，所有上述代码只是伪代码，我还没有真正测试过，我只是想帮助你开始。

希望这有帮助

Alex

项目经理实体框架小组

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(...)

解决方案

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

这篇关于实体框架查询Xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！