Query Xml from SQL using Entity Framework Database First

First error

where clause in your queries needs to evaluate to a bool value.

MyXmlHelper(1).Where(xh=> xh.XMLValue.Contains("1")) will give a record of type System.Linq.IQueryable<XMLTest> and not bool. You need to come up with a expression which will return a bool value.

Second error

Same is applicable to second error - change your where clause to get bool value from the expression.

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

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', 'int') LIKE '<Rating>'.@Rating.'</Rating>'
GO

Change "=" to "LIKE". Don't know what N(C) is, cross apply, or N.C.value(), but using = instead of LIKE often gives me trouble. Its trying to cross evaluate ints/bools with strings, and for strings like "1" you should use LIKE