I haven't used LINQ, but I do use Rails and its ORM. Using Rails' ORM to generate SQL is a case of optimizing for development time rather than execution time; assuming I would write faster SQL by hand, which is questionable, it would still be premature optimization and would be harder to maintain.

My take would be: use a great ORM to develop quickly and maintain easily, but be aware of common pitfalls, set up sensible indices, and have some performance monitoring in place. Tune by hand if absolutely necessary.