Error in LINQ to SQL with empty rows in the database

Question

I have been using LINQ to SQL for many years, but this is the first time I have seen this behavior.

I have a DB table with multiple columns ( varchar(15) ) that can contain empty rows ( '' ). I check this by running LEN(Column) and checking the result be 0 .

Now, when I call this from LINQ2SQL, it returns an object field with a string containing one space ( string.Length == 1 ).

There are several workarounds that I could apply, for example, make them NULL in the database or trim the string, but I would like to know if someone has come across this before or if the error is known (reported in MS Connect). If not, I will report it.

Thanks.

Answer 1

The problem is the LEN function:

 SELECT LEN(' ') 

Returns 0 in SQL Server; This is a complete PITA.

But

 SELECT DATALENGTH(' ') 

Returns 1