How can I check if a SQL Server 2005 TEXT column is null or empty using LINQ To Entities?
I'm new to LINQ and I'm trying to check if a TEXT column is empty or empty (like String.IsNullOrEmpty).
from c in ...
...
select new
{
c.Id,
HasBio = !String.IsNullOrEmpty(c.bio)
}
Attempting to use the above query throws a SqlException:
Argument data type text is invalid for argument 1 of len function.
The generated SQL looks like 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 does not apply to TEXT columns. I know DATALENGTH must be used for them ...
How do I get LINQ to create such a thing? Or any other workaround to check if the text column is null or empty.
Thanks!
Update
I came up with this
HasBio = c.bio.Substring(0, 1).Length > 0
but this is a bit ugly though, any other options?
+2
a source to share
2 answers
Well, I decided to convert TEXT columns to VARCHAR (MAX), taking into account the following article.
http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
+1
a source to share