String.Format for padding Leading Zeros in LINQ To SQL

As most of you might know that LINQ to SQL does not support many .net string manipulation functions. Basically, it seems that it mostly the limitation of T-SQL and not just LINQ to SQL. All LINQ to SQL queries are parsed into T-SQL statements and they are sent to the database and the result is provided to the caller code.

The list of string manipualation functions not supported in LINQ to SQL can be found in the following location.

http://msdn.microsoft.com/en-us/library/bb882672.aspx

Today I had the requirement of using one Of the many string manipulation functions that LINQ to SQL does not support. This is String.Format. I don't want to discuss in detail about String.Format function because it might take the discussion in a different direction. The requirement was to add leading zeros to the value of variable and make it a fixed length value of certain length.

If we just want to do it in .net we write it like this: String.Format("{0:00000}", myVariable). Here myVariable is the name of variable whose value we want to have as a fixed length string with leading zeros if required.

Dim myValues = From myTable in myTables Select New with {.myVal = String.Format("{0:00000}", myTable.myVariable)}

Even If I have not made any syntactic mistake, this code would not work. It would rather result in an exception in runtime explaining the limitation of LINQ to SQL parsing the Format function. Now it is a genuine requirement to add leading zeros. What should we do?

Well the answer is another string manipulation function provided, which is PadLeft. It is used to pad a character to the left of a string. We can make it a fixed length string by specifying the length of resulting string. It should be used like this:

Dim myValues = From myTable in myTables Select New with {.myVal = myTable.myVariable.PadLeft(5, "0"c)}

Here we have requested a string with length = 5. It would result in appending character "0" to the left of the string if the length of its value is lesser than 5. And this did the work for me.

Remember that there is another method PadRight() which you might guess that it could be used for padding any special character to the right of a string.