LINQ To SQL Cheat Sheet

Damien has created a cool cheat sheet for LINQ to SQL. I highly recommend this. This covers both C# and VB. They are separate pages, so you can print just for your desired language. You can download from the following link:

http://download.damieng.com/dotnet/LINQToSQLCheatSheet.pdf

Bulk / Batch database operations like INSERT, UPDATE and DELETE in LINQ To SQL

I have recently written an article about bulk insert / update / delete operations using LINQ To SQL. It involves sending data in IQueryable objects as XML to the database stored procedures and running the set based SQL operations.

Article: http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx

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

LINQ To SQL Command Time out

What should we do when our ado.net commands are timing out if we have already optimized our queries / procedures. We know that these commands should take that long. we just want our code to be supporting this delay and not throw any kind of exception.

This is a general scenario we face when we are dealing with data access. In ADO.Net, we access database by creating connections and command objects. If we know that our commands will take long, we increase time out for our commands.

But how should we do that in LINQ to SQL? We don't create any connections are command objects explicitly. Rather we have datacontexts and collections. We generally face timeout issues when we call stored procedures using LINQ To SQL DataContexts. The easiest answer is to change the time out for all data access.

Before presenting the solution let me make this know that LINQ to SQL classes are implemented using Partial classes. You can add definitions to these partial classes in your code. Now, we can define various event handlers in these partial classes other than various other implementation details. One of the event is OnCreated. Here is the solution of this problem by defining OnCreated event handler in partial class of DataContext. The vb.net code is as follows:

Private Sub OnCreated()
        Me.CommandTimeout = Integer.MaxValue
End Sub

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.