As a developer I don't find linq compelling at all. It seems like a step in the wrong direction. First, it tries to take data access concepts and make them language constructs which feels like adding the proverbial kitchen sink to a programming language.
Secondly, this will lead to developers having even less of an understanding of what they are doing and being even more ignorant of relational data concepts.
Third, as mentioned a lot of unoptimized sql will be run with little or no tuning ability.
ORM is hard and that needs to be accepted. There are some decent and interesting solutions out there (NHibernate, iBatis, even Subsonic perhaps), but linq tries to let the programmer ignore the data store and live in a OO fantasy.
Linq will work for small, quick apps but I'll bet it becomes a bottleneck for serious applications. It would have been much better to see MS put some energy behind something like NHibernate (As the Java community did when Sun basically adopted Hibernate for their EE data access strategy) that gives you most all of linq's advantages which better control over the queries themselves. We have also used iBatis.NET on mission critical projects because it solves much of the object to relational mapping problem but lets you specify every query explicitly which is great for performance tuning and dbas.
As for the comment about NHibernate not being widely used, if you look around at the upper tier of .NET developers you will find a lot of NHibernate use. It is definitely industrial strength and Hibernate, which it is based on, is arguably the defacto Java standard for Enterprise apps.
iBatis.NET is also being used more and more even by MySpace (Not that MySpace has great performance, but their technology guy has said they are getting fantasic performance out of iBatis.NET doing data access at loads that most people will never see)