One of the fundamental truths of coding is the Kiss Principle. I think that using LINQ to SQL for data access is a splendid example of applying the Kiss Principle. It is easy to get started with and efficient to work with.
LINQ to SQL was originally developed by a member of the .NET framework team as a proof of concept for the LINQ technology. When it was time to release LINQ the planned data access library was not ready for shipping (it was eventually shipped as Entity Framework). Releasing LINQ without any support for querying databases would be quite embarrassing, so Microsoft decided to ship the proof of concept implementation under the LINQ to SQL name. I am glad they did.
LINQ to SQL is simple. It does a 1-1 mapping between the database structure and classes in the code. There is very little support for having a domain model that differs from the database. For those who likes building large domain models with complex inheritance hierarchies LINQ to SQL isn’t powerful enough. But for the rest of us, that merely use the OR-mapper for querying and updating the database it is just about perfect. In many projects there is no need for a full object oriented domain model that is separate from the relational model in the database. It just adds complexity without giving anything in return.
It is way better to focus on getting the relational model of the database right and letting the database do what it is good at – structuring data and ensuring integrity. The data model in the database is the one thing that will be hardest to change once the system is in production. Put effort into getting it right. Put effort into refactoring it when weaknesses are found. Put effort into defining reasonable constraints to ensure data integrity.
The code generation tools is unfortunately a weak spot for LINQ to SQL. The visual designer looks nice, until the database is changed and there is no way to refresh the model. I prefer using SqlMetal.exe which generates a complete set of classes for the entire database in one sweep. it offers very limited customization options, but on the other hand it requires no manual work at all. Using such a tool also ensures that the code is always in sync with the database. Having changed the database? Just regenerate all the classes and you can be sure that your code is up to date. A change of data types in the database will typically give compilation errors in the code. Extending type safety to span over both the database and the code helps catching bugs early.
Having worked with LINQ to SQL in a number of real projects, my opinion is clear: It is a simple to understand, yet competent technology that offers great productivity.