我认为问题是由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 a Rating of 1 from the xml. I have referred to this *** 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 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);
}

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

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 the FilterCustomersByRating function to accept theCustomerID and rating.

Give it a try.

EDIT

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