I’ve compared SQL based data access layer with LINQ based one for a concrete use case: building a REST web service for high load systems.
My résumé reads as follows:
Generally, I would prefer to use SQL DAL for production code in high-loaded REST service systems. Not only it allows controlling all performance-relevant aspects, but also no DTOs are needed. In a typical high-load service with static pre-computed results, no business logic is expected to be performed during REST requests, so implementing DTOs is a clear development time overhead.
On the other side, when implementing mock services, it may be advantageous to use DTO LINQ, because of its DTO auto-generation features. When creating a mock service, it could be enough to create and populate a mock DB; and the DataContext and DTOs are generated just in seconds. If some custom attributes are required, it is also possible to switch to ad hoc objects at any time. Finally, the performance of mock services is unimportant.
ADO.NET Data Services (aka Astoria) should be additionally evaluated. While its output formats are fixed (JSON and standard Atom feed), it provides several promising features like CRUD operations and easy Windows Azure integration.