e as queries forem ser sempre implementadas assim, não vejo como isso pode ser facilmente manutenível e nem escalável. É comum ver implementações assim em grandes projetos? Eu tenho contato com um grande projeto legado atualmente, de cerca de 500 mil linhas, e ele é repleto de queries chumbadas. Honestamente é um pesadelo em todos os sentidos.

O que é comum - e recomendado - de se ver em grandes projetos são os chamados DAOs (data access objects) no lugar das queries chumbadas no código. São objetos que executam uma query, mas obdecem à uma interface bem construída. De certa forma são uma espécie de 'ORM' feitos sob medida para cada projeto.

Os ORMs são tão populares porque lidam eficientemente com a maioria dos casos - digamos 99% para ilustrar. No entanto, à medida que as consultas se tornam mais complexas, nos aproximamos desse 1% restante - melhor recorrer ao SQL puro.

Infelizmente, como você mencionou em muitos projetos grandes onde um ORM é usado, surge a necessidade do uso direto do SQL e - muitas vezes - o código acaba sendo implementado sem seguir nenhum padrão ou boa prática. Isso leva as tais queries chumbadas.

A solução ideal seria refatorar esses trechos conforme surgem utilizando o design pattern DAO ou até mesmo integrando diretamente no ORM utilizado quando este oferece recursos para isso.

Boa sorte em seu projeto!

No desenvolvimento com C# é muito comum as pessoas pensarem que o Dapper por ser mais leve que o Entity Framework e ser comumente utilizado com RAW SQL no código é mais performático, mas isso não é uma regra, pois ORMs modernos podem pegar a sua query feita em métodos e escrever uma query realmente performática por baixo dos panos, muitas vezes mais do que uma query escrita "na unha" por um desenvolvedor. E isso na verdade não é nem uma opinião, é algo que já foi amplamente discutido e testado.

Sim. Defender que o SQL puro é virtualmente sempre mais rápido não é apenas uma opinião, mas um fato comprovado por várias pesquisas [1][2]. Este estudo de caso inclusive compara o dapper com o linq como você mencionou e bem ele realmente é bem mais rápido..

Embora os ORMs modernos sejam capazes de gerar consultas SQL eficientes, eles ainda adicionam uma camada extra de abstração que necessariamente vai levar a um desempenho ligeiramente inferior em comparação com o SQL puro. Em um caso que ambos tenham sido 100% otimizados, o SQL sempre vai ser rápido, simples.

Em relação a otimização feita "na unha" X otimização feita pelo ORM: No PostgreSQL, por exemplo, você pode usar o comando EXPLAIN para entender exatamente como sua consulta está sendo executada. O EXPLAIN retorna um plano de execução detalhado que mostra o caminho escolhido pelo otimizador do PostgreSQL para resolver a consulta a nível de acessos de disco. Isso permite identificar gargalos potenciais e obter o melhor desempenho possível. De maneira que seria simplemente impossivel no nível de abstração de um ORM. Utilizando consultas como EXPLAIN e ANALYZE da maneira correta, um DBA SEMPRE vai ser capaz de escrever uma query mais eficiente que qualquer ORM faria.