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.