The XML datatype in SQL Server allows you to store, process and query XML rather easily. There are some caveats, however:
- You can not compare or order by an XML data type field in SQL Server
- Entity Framework does not support the XML data type, so your XML field will be converted to a string in your entity model
When querying a SQL Server View in Entity Framework, I started to receive a pesky error:
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
When running SQL Server Profiler to capture the actual SQL that my LINQ query produced, I found that Entity Framework decided to order by the XML field even though I did not ask it to do any such ordering. After many hours of scouring the internet, I came across a post with no answer that gave me a clue that helped me solve the problem.
It turns out that Entity Framework decides to order by columns marked as “Entity Key“. Since I was using database first with Entity Framework 6, the solution was to open up my
.edmx file, right-click on the XML field in my table and de-select “Entity Key”. Voila! Problem solved.