Linq to SQL error SQL does not compare NText, Text, Xml, or Image?

Question

When trying to do an update for a Linq object I receive the following error.

"SQL Server does not handle comparison of NText, Text, Xml, or Image data types."

There is a field in the database that is a Text field because it is going to hold a string that could be as much as 32kb.

Should I change the data type or is there a work around in Linq I could use?

EDIT

I have support SQL 2000 which is why it is a TEXT field.

Answer 1

Change it to a VARCHAR(max) - it will make life much easier.

---

EDIT

Full text indexing may help you here, not sure if SQL 2000 would allow you to then "search" on your text column

Answer 2

you can always use ToString() on the string in question which will force client side comparison.

Answer 3

You can manually change the Update Check to "Never" in the .dbml.

Answer 4

Or you could set UpdateCheck in code:

        [Column(Name = "SomeXml", Storage = "_someXml", CanBeNull = true, UpdateCheck = UpdateCheck.Never)]
    public System.Xml.Linq.XDocument SomeXml { get { return _someXml; } set { _someXml = value; } }

Answer 5

You have to change ALL the fields of the table to NVARCHAR(MAX) - Even the ones that you don't update.

Answer 6

I had the same problem and I just did something like this:

From this:

Dim query = From x In db.Transactions
            Where x.Description = "from ImnoX"
            Select x

to This:

Dim query = From x In db.Transactions
            Where x.Description.ToString = "from ImnoX"
            Select x