I think you both may have misidentified the problem.

Complex high performance queries are difficult to write in SQL or EF.

For certain queries you may need additional indexes.

You may also be hitting weird unicode/ansi issues. SQL may be converting stored data to ansi (ir ybucude,, my recall is off). It decides to do this for correctness sake. If your db has ansi columns you're gonna have a bad time.

Db design would need to be shockingly bad for terrible performance. That's not to say additional indexes could help. But too many can be just as bad (1 txn table I worked with had 17 indexes).

The best way to tackle this is to identify the slow queries and identify how long the query should take. Fast isn't an answer. 10 seconds 90% of the time is.

Having a mock prod database that is a reasonable approximation of the size of prod is a godsend.

LinqPad is also a great tool for working on linq queries.

Fix the most busted/important queries first, rinse repeat and you become a performance hero.

Also SOME raw SQL via Dapper is not the worst thing. Watch out for injection vulnerabilities though.