首先使用实体框架数据库从SQL查询Xml [英] Query Xml from SQL using Entity Framework Database First
问题描述
我有一个列 XMLValue
与数据
< MetaData>
<评论> 1< /评论>
< Rating> 1< / Rating>
< / MetaData>
我需要得到所有的客户
谁从xml获得一个评级
1。我已经提到了这个stackoverflow文章,我无法实现。
我添加了SQL函数并将其添加到我的edmx:
CREATE FUNCTION [dbo]。[FilterCustomersByRating ]
pre>
(@Rating int)
RETURNS TABLE
AS
RETURN
SELECT XMLTest。*
FROM XMLTest
CROSS APPLY XMLValue.nodes '// MetaData')N(C)
其中NCvalue('Rating [1]','int')= @ Rating
GO
以下DB函数:
[DbFunction( XMLDBModel.Store,FilterCustomersByRating)]
public static IQueryable< XMLTest> MyXmlHelper(int rating)
{
throw new NotImplementedException(您只能在LINQ查询中调用此函数);
}
以下是我在帖子中完全一样的linq查询,但不是能够使用这个功能,它会抛出错误。
var _dbCustomers =(from x in _context.XMLTests
其中MyXmlHelper (1).Where(xh => xh.XMLValue.Contains(1))
select x);
错误:
code>不能将类型'System.Linq.IQueryable< XMLTest>'隐式转换为'bool
如果我使用Any(),我有以下错误:
var _dbCustomers =(from x in _context.XMLTests
其中MyXmlHelper(1).Any(xh => xh.XMLValue.Contains(1))
select x);
错误:
code>CustomerRepository类型的指定方法'System.Linq.IQueryable`1 [XMLTest] MyXmlHelper(Int32)'无法转换为LINQ to Entities存储表达式,因为它的返回类型与返回类型不匹配的DbFunction属性指定的函数。
有人可以建议如何实现吗?
解决方案我认为问题是由stub函数的返回类型引起的。
在您的DbContext中输入您的
FilterCustomersByRating
方法?我不认为应该是XMLTest
。它应该类似于以下代码:[EdmFunction(TestingDbEntities,FilterCustomersByRating)]
public虚拟IQueryable< FilterCustomersByRating_Result> FilterCustomersByRating(Nullable< int> rating)
{
var ratingParameter = rating.HasValue?
new ObjectParameter(Rating,rating):
new ObjectParameter(Rating,typeof(int));
return((IObjectContextAdapter)this)
.ObjectContext
.CreateQuery< FilterCustomersByRating_Result>([TestingEntities]
。[FilterCustomersByRating](@ Rating),ratingParameter );
}
在这种情况下,存根功能的返回类型将为
FilterCustomersByRating_Result
当您将FilterCustomersByRating
表值函数添加到您的edmx文件时,该类将自动生成。CREATE FUNCTION [dbo]。[FilterCustomersByRating]
(@Rating int)
RETURNS TABLE
AS
RETURN
SELECT XMLTest。*
FROM XMLTest
CROSS APPLY XMLValue.nodes('// MetaData')N(C)
其中NCvalue('Rating [1 ]','int')= @评分
GO
记住你的存根函数应该返回
IQueryable< FilterCustomersByRating_Result>
ie[EdmFunction(TestingDbEntities,FilterCustomersByRating)]
public static IQueryable&FilterCustomersByRating_Result> MyXmlHelper(int rating)
{
throw new NotImplementedException(您只能在LINQ查询中调用此函数);
}
您可以使用它,如下所示:
var dbCustomers =(from x in _context.XMLTests
其中MyXmlHelper(1).Any(xh => xh.XMLValue.Contains( 1))
select x);
请注意,虽然这将工作,它将返回所有
客户
。您可能需要修改FilterCustomersByRating
函数来接受CustomerID
和评级
。
试试看。
编辑
除了上述之外,定义
MyXmlHelper
EdmFunction ,确保FunctionName
和NamespaceName
是正确的。在我的情况下,FunctionName
是FilterCustomersByRating
和NamespaceName
TestingEntities
与自动生成的DBContext类中的值相匹配。//< / auto-generated code>
public partial class TestingEntities:DbContext
{
public TestingEntities()
:base(name = TestingEntities)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public DbSet< XMLTest> XMLTests {get;组; }
[EdmFunction(TestingEntities,FilterCustomersByRating)]
public virtual IQueryable< FilterCustomersByRating_Result> FilterCustomersByRating(Nullable< int> rating)
{
var ratingParameter = rating.HasValue?
new ObjectParameter(Rating,rating):
new ObjectParameter(Rating,typeof(int));
return((IObjectContextAdapter)this)
.ObjectContext
.CreateQuery< FilterCustomersByRating_Result>([TestingEntities]
。[FilterCustomersByRating](@ Rating),ratingParameter );
}
}
I need to use Entity Framework, LINQ to query the XML data from the SQL in my asp.net mvc(C#) application.
I have a column
XMLValue
with data<MetaData> <Reviews>1</Reviews> <Rating>1</Rating> </MetaData>
I need to get all the
Customers
who have aRating
of 1 from the xml. I have referred to this stackoverflow post and I am not able to achieve it.I have added the SQL function and added it to my edmx:
CREATE FUNCTION [dbo].[FilterCustomersByRating] (@Rating int) RETURNS TABLE AS RETURN SELECT XMLTest.* FROM XMLTest CROSS APPLY XMLValue.nodes('//MetaData') N(C) where N.C.value('Rating[1]', 'int')=@Rating GO
And the following DB function:
[DbFunction("XMLDBModel.Store", "FilterCustomersByRating")] public static IQueryable<XMLTest> MyXmlHelper(int rating) { throw new NotImplementedException("You can only call this function in a LINQ query"); }
Below is the linq query which I tried exactly as in the post, but not able to use the function and it throws error.
var _dbCustomers = (from x in _context.XMLTests where MyXmlHelper(1).Where(xh=> xh.XMLValue.Contains("1")) select x);
Error:
Cannot implicitly convert type 'System.Linq.IQueryable<XMLTest>' to 'bool
If I user Any(), I have the following error:
var _dbCustomers = (from x in _context.XMLTests where MyXmlHelper(1).Any(xh => xh.XMLValue.Contains("1")) select x);
Error:
The specified method 'System.Linq.IQueryable`1[XMLTest] MyXmlHelper(Int32)' on the type 'CustomerRepository' cannot be translated into a LINQ to Entities store expression because its return type does not match the return type of the function specified by its DbFunction attribute.
Can someone suggest on how to achieve this please?
解决方案I think the problem is caused by the return type of your stub function.
Can you check what the return type for your
FilterCustomersByRating
method is in your DbContext? I don't think it should beXMLTest
. It should look similar to the code below:[EdmFunction("TestingDbEntities", "FilterCustomersByRating")] public virtual IQueryable<FilterCustomersByRating_Result> FilterCustomersByRating(Nullable<int> rating) { var ratingParameter = rating.HasValue ? new ObjectParameter("Rating", rating) : new ObjectParameter("Rating", typeof(int)); return ((IObjectContextAdapter)this) .ObjectContext .CreateQuery<FilterCustomersByRating_Result>("[TestingEntities] .[FilterCustomersByRating](@Rating)", ratingParameter); }
In this case, the return type of the stub function would be of type
FilterCustomersByRating_Result
which is class auto-generated when you add theFilterCustomersByRating
Table-valued function to your edmx file.CREATE FUNCTION [dbo].[FilterCustomersByRating] (@Rating int) RETURNS TABLE AS RETURN SELECT XMLTest.* FROM XMLTest CROSS APPLY XMLValue.nodes('//MetaData') N(C) where N.C.value('Rating[1]', 'int')=@Rating GO
With this in mind your stub function should be return
IQueryable<FilterCustomersByRating_Result>
i.e.[EdmFunction("TestingDbEntities", "FilterCustomersByRating")] public static IQueryable<FilterCustomersByRating_Result> MyXmlHelper(int rating) { throw new NotImplementedException("You can only call this function in a LINQ query"); }
you can the use it as shown below:
var dbCustomers = (from x in _context.XMLTests where MyXmlHelper(1).Any(xh => xh.XMLValue.Contains("1")) select x);
Please note that while this will work it will return all
Customers
. You might need to modify theFilterCustomersByRating
function to accept theCustomerID
andrating
.Give it a try.
EDIT
In addition to the above, when defining the
MyXmlHelper
EdmFunction, make sure that the spelling of theFunctionName
andNamespaceName
is correct. In my case, theFunctionName
isFilterCustomersByRating
andNamespaceName
isTestingEntities
which match the values in the auto-generated DBContext class.// </auto-generated code> public partial class TestingEntities : DbContext { public TestingEntities() : base("name=TestingEntities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public DbSet<XMLTest> XMLTests { get; set; } [EdmFunction("TestingEntities", "FilterCustomersByRating")] public virtual IQueryable<FilterCustomersByRating_Result> FilterCustomersByRating(Nullable<int> rating) { var ratingParameter = rating.HasValue ? new ObjectParameter("Rating", rating) : new ObjectParameter("Rating", typeof(int)); return ((IObjectContextAdapter)this) .ObjectContext .CreateQuery<FilterCustomersByRating_Result>("[TestingEntities] .[FilterCustomersByRating](@Rating)", ratingParameter); } }
这篇关于首先使用实体框架数据库从SQL查询Xml的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!