I've been using Linq to SQL for about a year now and recently I decided to make it the core of my new webshop.

If you decide to move along from writing SQL queries and using the System.Data.SqlClient classes to Linq to SQL, you'll notice how nice it is and how fast you can create the backend part of your (web)application. Some things like grouping, counting and making sub-selections can take some time to get used to, but once you get the hang of it, it will become second nature.

As with most programs that are constantly expanded as new ideas pop into your head, data structures change. To have those changes reflect in your Linq generated classes, you have to delete the entities from the .dbml designer and add them again. This is where things get tricky.

There are a few issues with Linq to SQL and the .dbml editor.

1) Auto-Generated column values (defined as NON NULL with a default value in SQL) like newsequentialid() or getdate() will not receive the property "Auto Generated". This causes insert's to fail. These kind of columns are often used with Guid Primary Key's or Date fields that indicatie creation, etc. These properties have to be set again after re-adding the entities or your inserts will fail.

2) If you have classes in place with names that correspond with a table name, the designer is not smart enough to pick a different name or to ask you what to do. Instead, you will receive an error indicating that there is a duplicate sub new() with the same signature...

It has happened a few times now that I forgot to set all the properties again for the auto generated columns, which resulted in people not being able to purchase products, which is killing if you run a webshop.

A solution for these problems would be to create unit tests that test all your Linq to SQL Classes, just make sure to use transactions to force a rollback in the end or it would result in pollution of your database.

To bad I only thought if this previous alinea while writing this article - it started out as "Linq to SQL and why it shouldn't be used for commercial applications". I'll be sure to write the unit tests in the upcoming week and post some examples after they are in place.