Linq To Sql: Exception “String must be exactly one

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

This problem also occurs when Linq To SQL Designer tries to auto generate a results class, for holding stored procedure results. Copy the auto generated class into your own new class file (use same name) and make changes there. Each time you update your DataContext it will just be a case of deleting the auto generated class. Not an ideal solution but a workaround for 2008.