How do I check if a SQL Server 2005 TEXT column is not null or empty using LINQ To Entities?

Question

Hi there guys

I'm new to LINQ and I'm trying to check whether a TEXT column is null or empty (as String.IsNullOrEmpty).

from c in ...
...
select new
        {
            c.Id,
            HasBio = !String.IsNullOrEmpty(c.bio)
        }

Trying to use the above query produces an SqlException:

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

The SQL generated is similar to the following:

CASE WHEN ( NOT (([Extent2].[bio] IS NULL) OR (( CAST(LEN([Extent2].[bio]) AS int)) = 0))) THEN cast(1 as bit) WHEN (([Extent2].[bio] IS NULL) OR (( CAST(LEN([Extent2].[bio]) AS int)) = 0)) THEN cast(0 as bit) END AS [C1]

LEN is not applicable to TEXT columns. I know DATALENGTH should be used for them...

How can I force LINQ to produce such thing? Or any other workaround to test if a text column is null or empty???

Thanks!

Update

I came up with this

HasBio = c.bio.Substring(0, 1).Length > 0

but it's a little bit ugly though, any other options?

Answer 1

Well I've decided to convert the TEXT columns to VARCHAR(MAX) taking in mind the following article.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx