To be honest you should keep all ORM queries fairly simple if you can. Where clauses fine. Inserts, updates, deletes, ORMs save so much code, and so much pain when you add new properties/remove them.
But if a query is more than a few includes or joins you should be handcrafting it with FromSQL() or loading it piecemeal using Load().
And don't even think about using it to make complicated reports, that is not a good idea. Make a stored procedure or view.
And that's especially true if you are using anything other than SQL Server. I've seen abysmal performance myself on MySQL/Maria on moderately complex EF queries. I've not really looked since EF 6, but it used to love making nested selects instead of JOINs, which were fine in SQL Server but terrible performance-wise in MySQL. Postgre I've never used with EF in anger so can't comment.
You can use EF with Hot chocolate to make a GraphQL endpoint really easily, but I'd imagine that's an easy way to saddle yourself with serious performance problems unless you limit the levels it can go. I'd be interested to hear if anyone's using it and how they find it?
Also the lazy-loading and the in-memory provider for tests are both kind of misfeatures.
If you don't want to debug difficult queries, then extend it to use Dapper and use the best of both worlds.
There is one gigantic footgun in EF Core, that is the decision between single query and split query. If you choose single query in the wrong situation you can end up with truly pathological queries. I might blame EF Core here a bit for a dangerous default, but to be honest the other choice would be dangerous in a different way, so there is no obvious good default choice here. This is a part that you need to understand to use this ORM, and fortunately it generates warnings now and kind of forces you to choose the strategy.
The one other aspect that helps to generate good queries with EF Core is to use "Select()" for any case where you want to request fewer columns than available in your tables. I find it quite natural to write queries this way in any case.
The Interpolated family of methods take nice, clean string interpolation like $"Select * from Table where Id = {Id}" and make sure that is properly parametric SQL queries (ie, avoiding things like SQL injection attacks).
It's a killer feature and I have some idea why it lives in the EF side of the house rather than being generally applied across all of ADO.NET, but it should still probably be a more reusable library of its own beyond just EF.
We're not used to something like EF, perhaps it would work for us. But debugging generated queries due to performance issues is something we'd like to avoid. For now the decision was made to not use EF.