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!!!

SPARSE COLUMN and COLUMN SET

Another new feature coming up with SQL Server 2008 is SPARSE column. It provides optimal storage by reducing the storage for nullable columns. But there are also downside to it as making a column as SPARSE would put some overhead when a non-null value is read for the column. The group of columns can be setup by making a COLUMN SET while creating a table. The column set would allow all the sparse columns to be updated like a single XML column.