Well, in strong typed languages LINQ gives you compile time type checking both within the query and of the result. That's definitely a nice to have, especially in combination with intellisense. In some sense it's a nice way to prototype queries especially since (and now I'm taking about C#/VB.Net) with anonymous types, you can easily and on just one place add some more columns to the result set and have them available right away. No code generation, no changes in different layers, ...
If you then find out that the query got too complex, is not performing well enough or just want to keep all queries in the database, you can change the LINQ query to a stored procedure call.
If the "extra layer" makes it difficult to debug and optimize, then the query has already got too complex and needs to be moved to a stored procedure or hidden in a database view that you can then use in LINQ to go the last step.
Enoch was right!
Enjoy the last years of Rome.