For simple/basic queries, LINQ to SQL does a good job - but it doesn't take long before you see instances where the generated code is less efficient than if you were doing it yourself. In some instances, the generated code (e.g. correlated subqueries) is precisely what some SQL authors instruct NOT to write.

IME it's possible to be too "smart" with Linq to SQL. IMHO to get the best result you have to Think Dumb. Assume nothing. Let Linq to SQL figure it out. If necessary, break it into chunks and join them. Even if you do things that you expect will result in TSQL requiring several selects or even heterogeneous joins with stuff in memory, Linq to SQL may well surprise you with a single perfectly formed select. ;-) IMHO it will become clearer over time how to get the best out of it.

It's very clear that Linq to SQL doesn't cover everything in TSQL. What Linq to SQL will do is automatically harness TSQL improvements over time. It will continue to function if you switch to a MySQL or Oracle backend- yeah I know you can't today, but that's clearly the intention.

Also, IMHO Linq to SQL is simply an expression of something that has been becoming more and more obvious: SQL is a commodity and people are going to spend less and less time with it. That's how it goes in industry... everything from PC innards to data management is being standardized and packed inside a black box. If the black box isn't efficient enough, they'll approach a specialist... or buy a bigger server if that's cheaper. I remember somebody else's old argument that "XYZ technique is faster because if it isn't, you can buy a bigger server." Well, that may well be the motto of our age. In the MS camp, anyway. ;-)