Wednesday, January 20, 2010

LINQ To SQL and VARCHAR(1) Fields

You don't know it until you experience it!

Mapping between data types between database systems and programming languages has always been an issue. Though LINQ To SQL maps the datatypes of every field to it's corresponding datatypes.

One of these mappings, is the mapping between VARCHAR fields in the database and .net datatypes. When we create LINQ To SQL entity in our dbml files for a table in the database, all VARCHAR fields are correspondingly created as String fields in the LINQ To SQL entity. This is with exception to VARCHAR fields with width equal to one (1). VARCHAR(1) fields are mapped to character datatype.

This seems to make sense. This is because that is exactly what might be intended when this field was defined as VARCHAR(1). So what is the issue??

The issue comes at runtime for all these VARCHAR(1) fields which are NULLABLE too. Whenever there is NULL in such fields. The system is not able to load corresponding value in LINQ To SQL entity. This is because there is nothing like empty character. You would never know about this issue until you run your system and you try to load data with NULL values in this field. This results in the exception with following message:

'String must be exactly one character long'

Now we know about the problem. How can we avoid this problem to occur at runtime. The solution is very simple. After generation of Entity by LINQ To SQL designer, change all these character datatypes to String. This would cause empty string to be loaded when NULL data is loaded in such a field.

Hope this would help you!!!


Dean said...

It would be great to have a video of you doing this, for the more newbies of us, who will not know how to do one of the steps you have explained. Just a thought.
Otherwise, great explanation thanks!

Dean, webmaster of Spy Gear

Jo said...

Great solution. Thanks for sharing.
how to get a flat stomach