Linq To Sql: Exception "String must be exactly one character long"

Question

Consider a SQL Server table defined with a varchar(1) NULL field. It's being used to store a gender character. Some rows have data, some not: either null or blank. Granted the blanks SHOULD be nulls, but consider that blank is a valid value here. I'd much prefer the value to be null.

ID    Gender
1      'M'
4      'M'
3      ''
4      'F'

An exception is raised when running a Linq To Sql query where the value of someID is 3.

var emp = (from e in db.Employees
           where e.ID == someID
           select e);

Exception:

String must be exactly one character long.

Question: What is the cause of this exception? What can be done to prevent or eliminate this problem?

Answer 1

Check the Employee type that was created for you by the LINQ to SQL designer. Most likely the type for the Gender property is System.Char (which is the type that the LINQ to SQL designer uses for varchar(1)) and should be changed to a System.String to properly match your database schema.

The fact that the LINQ to SQL designer interprets a varchar(1) as a System.Char is foolish considering that this is valid T-SQL:

declare @foo varchar(1);
set @foo = '';

and this is invalid C#:

Char foo = '';

Since the type of the property that was generated is too restrictive you need to change it to be a System.String.

Note: You may want to consider adding some validation inside the setter of the property to throw an exception if the length of the string is greater than one.

Answer 2

Is it possible that the blank data like '' doesn't meet the current constraints of the table? E.g. perhaps the table doesn't permit empty strings (even though there is one in there).

Then, maybe LINQ is applying those constraints for you, or expecting those constraints to be met and complaining that they are not.

If this is what is going on, you might change the constraints/design of the table to allow blank values, or just update all the blank values to NULL (assuming NULLs are allowed)

Answer 3

Refer to the link below.

Good explanation of cause of the issue and possible resolution for the same.

http://aspdotnethacker.blogspot.com/2010/07/string-must-be-exactly-one-character.html

Answer 4

Here is a link that provides good information about LINQ to SQL best practices

http://www.a2zmenu.com/LINQ/String%20must%20be%20exactly%20one%20character%20long.aspx