使用实体框架数据库首先从SQL查询XML [英] Query Xml from SQL using Entity Framework Database First
问题描述
我需要使用实体框架,LINQ查询在我的asp.net MVC(C#)应用程序从SQL XML数据。
I need to use Entity Framework, LINQ to query the XML data from the SQL in my asp.net mvc(C#) application.
我有一列 XMLValue
数据
<MetaData>
<Reviews>1</Reviews>
<Rating>1</Rating>
</MetaData>
我需要让所有的客户
谁有从XML的1 评级
。我已经提到这个计算器帖子,我不能够去实现它。
I need to get all the Customers
who have a Rating
of 1 from the xml. I have referred to this stackoverflow post and I am not able to achieve it.
我添加了SQL功能,并把它添加到我的EDMX:
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
和以下DB功能:
[DbFunction("XMLDBModel.Store", "FilterCustomersByRating")]
public static IQueryable<XMLTest> MyXmlHelper(int rating)
{
throw new NotImplementedException("You can only call this function in a LINQ query");
}
下面是LINQ查询,我试过正是因为在后,但不能够使用的功能和它抛出错误。
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.
您可以检查什么回报键入您的 FilterCustomersByRating
方法是在你的DbContext?我不认为它应该是的XMLTest
。它应该类似于下面的代码:
Can you check what the return type for your FilterCustomersByRating
method is in your DbContext? I don't think it should be XMLTest
. 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);
}
在这种情况下,桩函数的返回类型是类型 FilterCustomersByRating_Result
这是阶级自动生成的,当你添加 FilterCustomersByRating
表值函数到您的EDMX文件。
In this case, the return type of the stub function would be of type FilterCustomersByRating_Result
which is class auto-generated when you add the FilterCustomersByRating
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
考虑到这一点存根功能应该是回归的IQueryable< FilterCustomersByRating_Result>
即
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);
请注意,虽然这会工作,它会返回所有客户
。您可能需要修改 FilterCustomersByRating
函数接受客户ID
和评级
。
Please note that while this will work it will return all Customers
. You might need to modify the FilterCustomersByRating
function to accept theCustomerID
and rating
.
试试看。
修改
在除了上述,定义 MyXmlHelper
的 EdmFunction ,请确保 FunctionName
和 NamespaceName
是正确的。在我的例子中, FunctionName
是 FilterCustomersByRating
和 NamespaceName
是 TestingEntities
在自动生成的DbContext类值相匹配哪个。
In addition to the above, when defining the MyXmlHelper
EdmFunction, make sure that the spelling of the FunctionName
and NamespaceName
is correct. In my case, the FunctionName
is FilterCustomersByRating
and NamespaceName
is TestingEntities
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屋!