> "it falls apart in real-world persistent code with considered algorithms and storage"
> "it makes it so easy to do [grouping / sorting / etc] that developers start to resort to that as a catch-all magical solution that is costless because how much could a line or two of code cost"
With hindsight I know your issue is with LINQ's deferred non-memoized execution. It's really easy to enumerate a sequence twice, but every place you do that might add a factor of 2 to the runtime and so there's a danger of exponential blowup that must be avoided. But that's not what I see when I read the above.
The above reads as complaints about easy composability and worries about code monkeys failing to consider what the code they're writing will do. Those are standard "functional / high-level programming is bad" complaints. With hindsight I know that's not what you meant, but that's what it reads like.
Basically, you're saying it's too easy to accidentally perform a computation twice by iterating through a sequence twice. Recurse on that problem and you get an exponential blowup.
That is actually a problem mostly unique to LINQ and it is really important that a programmer understand the deferred semantics. Some tools, e.g. ReSharper, will detect multiple enumerations and warn you about it. Judicious use of ToArray/ToList solves most issues with defer-splosion.