What's wrong with Linq to SQL?
Or - what about Linq to SQL would make it unsuitable for a project, either new or existing? I want to hear about why you would not choose Linq to SQL for a particular project - including what project parameters make it unsuitable.
Well, I have developed some applications using LINQ to SQL. One of the main problems that I find is having to layer your application. In LINQ to SQL the entity classes are tied very closely with the data access code. Also, there are some issues with DataContext which means that you can use a DataContext object to retrieve an item but you cannot transfer the item (object) to another DataContext (at least not easily).
LINQ to SQL will be useful if you don't care about layering your application properly and also if all you wanted is to create an application in a rapid manner also know as RAPID Application Development.
It is not very adaptable to changes in the database schema. You have to rebuild the dbml layer and regenerate your data contexts.
Like any ORM (I am not getting into the debate as to whether it is an ORM or not), you do have to be aware what SQL is being generated, and how that will influence your calls.
Inserts are not batched, so can be high cost in performance.
It's being sunsetted in favour of Entity Framework
Despite the fact it is using a provider model that will allow providers to be built for other DBMS platforms, only SQL Server is supported.
[EDIT @ AugustLights - In my experience: ] Lazy loading may take a bit of hacking to get working.
That being said, I think it it is very handy if used correctly
A lot of the advantage to LINQ-to-SQL comes from supposedly being able to construct data queries right in your code-behind based on strongly-typed queryable/enumerable data objects from your dbml (which plays the role of a very limited DAL). So a consequence, as has already been mentioned, is that it encourages you somewhat towards playing outside strongly defined and separated layers or tiers to your application.
To counter that point, it should mean that you should be able to eliminate most or all of any business logic you were writing into stored procedures on the database, so then at least you only have to go to the code that deals with the data to change non-schema-impacting business rules... However, that breaks down a bit when you realise how complicated it can be to write a query with an outer join with aggregates with grouping, at least when you first approach it. So you'll be tempted to write the sprocs in the SQL you know that is so simple and good at doing those things rather than spend the extra time trying to figure out the LINQ syntax to do the same thing when it's just going to convert it to ugly SQL code anyway...
That having been said, I really do love LINQ, and my esteem for it vastly increased when I started ignoring this "query syntax is easier to read" sentiment I've seen floating around and switched to method syntax. Never looked back.
It is difficult to mock while unit testing because of a lack of an interface on the System.Data.Linq.DataContext class. Here's a possible approach: Mocking LINQ to SQL DataContext.
For a project that needs to make use of databases other than SQL Server:
1) You are locked in to using SQL Server
For a project with complex entity relations and/or relations that change gradually over time:
2) You are locked in to 1-to-1 mapping of tables to classes
For a project that must use 1.x versions of .NET
3) Won't work with .NET 1.x
This question was asked once before over here. But, in essence, LINQ to SQL generates sub-optimal execution plans in your database. For every different length of parameter you search for, it will force the creation of a different execution plan. This will eventually clog up the memory in your database that is being used to cache execution plans and you will start expiring older queries, which will need to be recompiled when they come up again.
As I mentioned in the question I linked to, it's a matter of what you're trying to accomplish. If you're willing to trade execution speed for development speed, LINQ to SQL might be a good choice. If you're concerned about execution speed, there are other ORMs/DALs/solutions available that may take longer to work with but will provide you with future proofing against schema changes and better performing solutions at the cost of additional development overhead.
Despite all of the above I think linq-to-sql is an excellent choice for many projects.
The only thing I would label as a technical "showstopper" is if you want to use other RDBMSes than SQL Server. (although it can be worked around - see Matt Warren's blog @ http://blogs.msdn.com/mattwar/ )
Besides that, there are some pros and cons already listed in previous answers to your question. However, all of the negatives mentioned so far have workarounds so they are not really showstoppers.
A non-technical [potential] showstopper is the risk that MSFT will abandon it in favour of EF... More on that here: http://oakleafblog.blogspot.com/2008/05/is-adonet-team-abandoning-linq-to-sql.html
Although (in my opinion, ) the current state of EF is reason enough for them to continue work on L2S. So let's hope they do...
A true ORM should separate the design of your Business Entities from your persistence medium. That way you can refactor either one of them separately and only have to maintain the mapping between the two. This reduces the amount of application logic code that you need to maintain for database changes.
To accomplish this kind of persistence agnostic approach with Linq-to-SQL, you would have to use its generated classes at DTOs and maintain a mapping logic between your DTOs and your Entities.
There are much better ORMs for taking this approach (like NHibernate) that greatly reduce the need for DTOs.