I’m a big fan of LINQ, especially the query expression form (e.g., from c in cars where …), but there are times when LINQ isn’t appropriate. Like all abstraction layers unfortunately the abstraction tends to leak the more deeply it’s applied (not unlike metaphors that break down when over-applied).
If you’re building a high-scale service and much of that service’s ability to scale is dependent on a relational database then it seems that time and time again this abstraction leakage becomes more of a problem than the convenience it provides.
In the case of LINQ-to-SQL the problem is that LINQ often translates queries in a very sub-optimal way. This translation is not readily apparent when a human being looks at the source (though it can be determined with tools). This is my first pet peeve about LINQ; by abstracting away the query translation (and thereby shielding the developer from having to learn SQL) the application itself becomes harder to maintain.
If the database itself, with all of its knowledge about the distribution of the data and query access patterns, occasionally comes up with sub-optimal execution plans then how on earth is LINQ-to-SQL, which doesn’t have this information available, going to do as well or better?
For small databases this abstraction leakage isn’t a problem. The increase in developer productivity probably more than makes up for the loss of efficiency.
For large databases this leakage quickly results in slow queries that are difficult to identify since the database only sees the translated query but the source code only shows the LINQ form. LINQ-to-SQL is so similar to SQL that learning SQL is a very small price to pay for the increased efficiency and maintainability of an application.