I have to say that I like LINQ to SQL, heck, I even do presentations about it. So, it is a cute ORM. What is that good about it features LINQ. However, there are some serious showstoppers in there. One are more serious than the others. I won't deal with what's good about LINQ to SQL, rather I'll dive into showstoppers as I see them. So, let's take a look at them:
- Support for SQL Server flavors only, not even Katmai (SQL Server 2008).
This might or might be a showstopper for you. One of the great advantages of ORM products is that they are abstracted from database, meaning the same code works with different supported databases. You probably won't write an application that is totally abstracted and you'll have to write few database dependant stuff but it is still true for the majority of the code. So, why would I bother learning an ORM that deals with SQL Server only when I have plenty of other ORMs that supports a bunch of different databases? Why would I bother to tie my application to SQL Server only when I could have an application that works with all the major databases out there?
- Poor n-tier support
A good ORM would let you send all the modified entities to the server tier with a few of method calls and store changes to the database with few other method calls. But not LINQ to SQL. Oh, no. Doing n-tier with LINQ to SQL requires a fair amount of coding and lot of manual work that should be done by LINQ to SQL itself. IOW LINQ to SQL is very client-server oriented. Looks like n-tier was added afterwards.
- Designer that doesn't sync with database
Imagine you have a Context with myriad of entity types on it. That's very messy by itself since you can't isolate a certain set of entity types you might be interested in but you always have to look at all of them. But that's not the real issue here. The real problem arises when database metadata changes (right, you always define your database structure in stone before you start coding and you don't modify it ever) - perhaps this is a bad project management, but it does change through the life of the project. And when it does, LINQ to SQL won't help you modifying the Context. Nope, you are on your own. So, you can recreate the context manually and reapplying all the modifications done to it (you are certainly looking forward to this) which is not only boring, but error prone, too. Or you can manually sync the Context with database which is not that boring but error prone and tedious. So, this one is a huge showstopper if you ask me.
Luckily there is [CodeSmith] with its PLINQ templates that supports database synchronization. While not a perfect solution it solves many problems. Furthermore it allows you to modify the templates to your requirements - IMO the [CodeSmith] path is the only path if you are serious about LINQ to SQL.
- No source code
[MS] is probably releasing LINQ to SQL source code, but even if they does, you won't be able to fix problems as you can't modify it in any way. Not a big problem.
- Slow development cycle (tied to .net 3.5)
If you need a fix quickly you will have problems as LINQ to SQL is part of .net 3.5 framework. And knowing the speed of [MS] applying bugfixes it might take years before official update is available.
- No direct execution against database
Imagine you have a task that has to update all item prices for 10% for whatever reason (it acually happened last month in Slovenia for bread and milk items). All orders have to be fetched from the database to the application, each order has to be properly update in application and all of them have to be persisted back to database. Right, you can't execute the operation directly against database from the code. OK, you can issue a SQL statement or use a stored procedure but the former breaks strong typing (SQL statement is a string, compiler won't help you catching the errors) while the later is unnecessary burden.
- There is plenty of better competitors out there
Apart from the (obviously) LINQ advantage there is plenty of better and proven ORM products out there. Even the LINQ, major LINQ to SQL's advantage will soon be integrated by the competition as some of them already feature LINQ (to Entities) while others will soon.
Again, don't take me wrong. LINQ to SQL has plenty of good features. It is just that it has too much of show stoppers for me and knowing problems of certain technology before using it is usually a valuable knowledge. So, what can you do? Pick an alternative of course, either one of commercial or free 3rd parties or wait for Entity Framework which will see light next year, sometime after .net 3.5 RTM.
What will I do? Actually nothing. I am very happy with [LGP] that is much superior to LINQ to SQL except for the LINQ. Also [LGP] is getting LINQ support supposedly this year so it shouldn't be long before I am be able to happily LINQing with ORM.
And let me know if there are other problems you foresee with LINQ to SQL.