首先使用实体​​框架数据库从 SQL 查询 XML [英] Query Xml from SQL using Entity Framework Database First

问题描述

我需要使用 Entity Framework、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 中获取 Rating 为 1 的所有 Customers.我参考了 this stackoverflow post，但我无法实现.

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);

错误:

Cannot implicitly convert type 'System.Linq.IQueryable<XMLTest>' to 'bool

如果我使用 Any()，则会出现以下错误:

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);

错误:

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");
}

你可以如下图使用它:

var dbCustomers = (from x in _context.XMLTests
                   where MyXmlHelper(1).Any(xh => xh.XMLValue.Contains("1"))
                   select x);

请注意，虽然这会起作用，但它会返回所有 Customers.您可能需要修改 FilterCustomersByRating 函数以接受 CustomerID 和 rating.

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屋！