Linq to Entities and Xml Fields

Question

I have a scenario like this:

• SQL Server myTable table with field1, xmlField (nvarchar (50) and xml sql data type)
• Linq for objects

Now I would like to receive a request like this:

SELECT Field1, XmlField FROM MyTable WHERE CAST(XmlField AS nvarchar(4000)) = '<myXml />' 

Obviously, this is the correct query in SQL Server, but I cannot find a solution for writing to L2E.

Please report that this code does not work:

 var query = from row in context.MyTables where (string)row.XmlField == "<myXml />" select row 

and other casting methods. This only happens because "ToString" is not working properly in L2E.

Now my idea is this: extension method:

 var query = from row in context.MyTables select row query = query.CompareXml("XmlField", "<myXml />") 

and this is an extended method:

 public static IQueryable<TSource> CompareXml<TSource>(this IQueryable<TSource> source, string xmlFieldName, string xmlToCompare) { ConstantExpression xmlValue = Expression.Constant(xmlToCompare); ParameterExpression parameter = Expression.Parameter(typeof(TSource), source.ElementType.Name); PropertyInfo propertyInfo = typeof(TSource).GetProperty(xmlFieldName); MemberExpression memberAccess = Expression.MakeMemberAccess(parameter, propertyInfo); var stringMember = Expression.Convert(memberAccess, typeof(string)); BinaryExpression clauseExpression = Expression.Equal(xmlValue, stringMember); return source.Where(Expression.Lambda<Func<TSource, bool>>(clauseExpression, parameter)); } 

and again this will not work either.

Now I would like to understand how I can make Convert using Cast so that I can compare Xml and nvarchar.

Thanks in advance Massimiliano

Answer 1

Unfortunately, EF still does not support XML columns. I am afraid that the only choice I know is to create a view that makes a throw and matches it with another object. This is likely to make the code inconvenient, but also offers additional possible scenarios; for example, with lots of SQL code, you can map the elements in the XML columns to the actual columns in the view, which allows you to query in certain parts of the XML.

On the bright side, at least inserting values ​​in an XML column works pretty much as expected.