Xml query from SQL using Entity Framework First database

Question

I need to use Entity Framework, LINQ to query XML data from SQL in my asp.net mvc (C #) application.

I have an XMLValue column with data

 <MetaData> <Reviews>1</Reviews> <Rating>1</Rating> </MetaData> 

I need to get all Customers that have Rating of 1 from xml. I was referring to https://stackoverflow.com/a/166269/ and I cannot reach it.

I added an 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 NCvalue('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 in the exact same way as in the post but could not use this function, and this causes an error.

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

Mistake:

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

If I am an Any () user, I have the following error:

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

Mistake:

 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 anyone suggest how to achieve this?

Answer 1

I think the problem is caused by the return type of your stub function.

Can you check what type of return for your FilterCustomersByRating method is in your DbContext? I do not think this should be an XMLTest . It should look like 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 will be of type FilterCustomersByRating_Result , which is automatically generated when the FilterCustomersByRating Table-valued function is added 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 NCvalue('Rating[1]', 'int') =@Rating GO 

Given this, your stub function should be returned by IQueryable<FilterCustomersByRating_Result> ..

 [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 use it as 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 may need to change the FilterCustomersByRating function to accept CustomerID and rating .

Give it a try.

EDIT

In addition to the above, when defining MyXmlHelper EdmFunction, make sure that the spelling of FunctionName and NamespaceName is correct. In my case, FunctionName is FilterCustomersByRating , and NamespaceName is TestingEntities , which correspond to the values ​​in the automatically 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); } } 

Answer 2

First mistake

where in your queries you need to evaluate the value of bool .

MyXmlHelper(1).Where(xh=> xh.XMLValue.Contains("1")) will give an entry of type System.Linq.IQueryable<XMLTest> , not bool . You need to come up with an expression that returns a bool value.

Second mistake

The same applies to the second error - modify the where clause to get the bool value from the expression.

Answer 3

 CREATE FUNCTION [dbo].[FilterCustomersByRating] (@Rating int) RETURNS TABLE AS RETURN SELECT XMLTest.* FROM XMLTest CROSS APPLY XMLValue.nodes('//MetaData') N(C) where NCvalue('Rating', 'int') LIKE '<Rating>' .@Rating. '</Rating>' GO 

Change "=" to "LIKE". I don't know what N (C), cross apply or NCvalue () is, but using = instead of LIKE often causes me problems. His attempt to cross-evaluate ints / bools with strings, and for strings like "1" you should use LIKE