Does LINQ to SQL replace the whole DAL?

Probably the balanced answer should be "it depends on the application you are building", but I usually go for a straight "no way" - there's always time to subtleties.

At least in any SQL Server based application that is not trivial (in terms of data model and user concurrence), there's little doubt about the key role that stored procedures should still play. Their functionality may now be complemented with LINQ to SQL direct operations in a mix whose exact proportion depends on the application context.

This may range from air-tight corporate DB environments (no direct access allowed)  to more pragmatic scenarios in which a big chunk of the simple data operations are easily implemented as LINQ queries. In almost any case, some stored procedures are still required as the best way of solving complex queries and handling complex or critical updates.

Having established this, there's still a question about the necessity of a "duplicate" data access layer, considering that LINQ to SQL does allow stored procedure calls. In my opinion, several reasons make a separate, full-featured DAL an important component of you application

• Full control over the SP invocation: your generic data access code may be customized with the desired exception handling, parameter examination and completion, and any fine-grain control your application requires.
• Ability to return or fill datasets: in several situations (i.e. reporting) datasets are still a simple and flexible way to carry a list of tabular data thru your application tiers.
• If you prefer to return custom objects (or you are a TDD / mockable objects fan), you may extend the DAL to materialize a POCO instance or collection from a Data Reader obtained thru a SP call.

The last assertion - returning a POCO instance or collection in your custom DAL, instead of just calling the SP thru LINQ to SQL is motivated by the need to establish a clear rule of use:

• Use the LTS data access for direct LINQ queries and updates
• Use the custom DAL for all SP calls.

How to implement the custom objects materialization from a DataReader ? You may write your own code -examining attributes or thru reflection - or you may just delegate this task to the Translate method of a LTS Data Context, inside your own DAL. Assuming the necessary LTS attributes are present in the Customer class,  this code works:

    DbDataReader dr = cmd.ExecuteReader();
    myDataContext.Translate<Customer>(dr);

NOTES:

• For a reason unknown to me, the provided DC should hold a valid database connection, even when data is pulled from the already open data reader.



• And if you are thinking of this as a way to workaround LINQ to SQL 's ties to SQL Server (after all, a DbDataReader may be obtained from many data sources) forget it: the Translate method with throw an exception when the reader is not a SqlDataReader. Nice try!

Side topic: for an interesting discussion on the typed dataset vs LINQ to SQL objects, you may read this entry in Aaron's Technology Musings blog.