What are the downsides and limitations of using Linq to Sql verses writing a more traditional data layer calling stored procs/using dynamic sql through the .NET SQL Server data provider?
The advantages are well documented but I’ve found little discussion of the real world issues people have experienced.
Please note I’m not talking about comparing with O/R mappers such as NHibernate and Subsonic.
The major downside of Linq to SQL is the uncertainty surrounding it with the advent of the ADO.Net Entity Framework - unless the situation has changed, there has been strong rumours that Linq to SQLs future is cloudy as MS are concentrating on the Entity Framework and moving away from Linq to SQL.
I have already moved to the Entity Framework, and until the situation becomes clearer I am using Linq to SQL sparingly on major projects.
There are a few - not sure if those matter to you:
But again - those are just limitations, but a lot of folks (myself included) can live with those no problem - at least for a certain type of project.
If you need more flexibility (more database backends, more granular mapping), you should definitely look at NHibernate or later on at Entity Framework 4. They offer more power and more punch - but they're also a tad harder to learn.
ON the other hand, Linq-to-SQL also has massive pros:
But I'm sure you're well aware of those pro sides, right? :-)
The biggest problem I've found is that you seem to be required to drag and drop database tables onto a designer, and it's not easy to access the dbml text directly. There have been many times I've added a column to a table and wanted to update it, only to have to delete the table from the designer, re-add it, and remap any custom associations I've done.
If someone can tell me how to easily get at the dbml text, I'd love to hear how to do this.
Another thing that bugs me is if I get a "string or binary data may be truncated" error, I can't tell which column caused the error, making me play a trial and error game.