0

I've hit an error when I've been working on a table that uses a text field.

If I was getting the length of a varchar column I could do

var result = (from t in context.tablename select t.fullname.Length)

However, if I run the same query on a text field:

var result = (from t in context.tablename select t.biography.Length)

I get the error :

Argument data type text is invalid for argument 1 of len function.

Having done a bit of reading up on the subject I understand why SQL Server raises this error but I'm not sure of the best way around it. I know I could return the result and then get the length of the resulting string but surely there is an easier way of doing this?

GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104
  • While not the answer to your question, have you considered changing the column data type to `varchar(MAX)`? – Seph Apr 25 '12 at 12:52
  • 1
    @Yuck, no, they're not *exactly* the same (for one thing, [TEXT is deprecated](http://msdn.microsoft.com/en-us/library/ms143729%28v=SQL.90%29.aspx), and in particular they're not the same with respect to the `len` function. @Seph's suggestion is good. – Craig Stuntz Apr 25 '12 at 13:04
  • I should have tagged this; I'm on SQL 2008 if that makes any difference. – GrandMasterFlush Apr 25 '12 at 13:07
  • @Yuck - They are completely different data types, and behave very different too – Lamak Apr 25 '12 at 13:14
  • @Seph - It seems that upgrading the columns is the best way to go, if you want to post that as the answer I'll mark it accordlingly. – GrandMasterFlush Apr 25 '12 at 13:20

1 Answers1

5

I think your best option is to update the column data type to VARCHAR(MAX) if it is TEXT or NVARCHAR(MAX) if it is NTEXT. There are plenty of resources on how to do this, but generally you make a new column of [N]VARCHAR(MAX) and then you update all your data across into the new column, then drop the old column and finally rename the new column to the old name.

If you can't change the table schema, then you will need to create a view and do the type casting in the select of that view.. but then you might as well have just changed the column data type as mentioned above (unless you're not the db owner and you create the view in a different database). But be mindful that EF doesn't always play as nice with views as it does with tables.

Seph
  • 8,472
  • 10
  • 63
  • 94
  • Thanks for that Seph. I hadn't realised that text had been depreciated (I went from SQL 2000 to SQL 2008). Converting the columns with an ALTER statement was painless and the code works perfectly now. In case anyone is wondering, this post gives an outline of the differences between the two column types: http://stackoverflow.com/a/834863/543538 – GrandMasterFlush Apr 25 '12 at 13:44