LINQ problems with NText, Text and Image on SQL server

Question

Apologies up front, because this isn't a question but a solution - but it took a lot of searching to find out the answer and google wasn't much help, so I wanted to give something back to the community by providing the error and the solution to help future googlers.

When using LINQ to SQL, I ran into a problem when submitting changes (the second time) to a data table. The first time I submit changes all is OK, the second time I submit changes I recieved a SQL exception which said:

"The text, ntext, and image data types cannot be compared or sorted, 
 except when using IS NULL or LIKE operator"

The exception was thrown when I was calling SubmitChanges(), but because of the lazy loading of LINQ it actually arose in the previous line when I was calling Refresh(RefreshMode.KeepCurrentValues, myObject).

Answer 1

The problem is that the Refresh mode is attempting to compare the current with the database version of all the fields, and SQL doesn't support that kind of comparison text, ntext and image columns (at least not on SQL Server 2000).

The solution is to add the following attribute to your column definition

UpdateCheck = UpdateCheck.Never

e.g.

[Column(DbType = "NText", CanBeNull = true, UpdateCheck = UpdateCheck.Never)]

Then the Refresh mode works OK and SubmitChanges doesn't have any further problems.