I've largely abandoned ORMs that generate SQL queries for me. A mapping layer that takes a SQL result and stuffs it into an object (DTO) is useful, but anything beyond that is more trouble than it's worth.

For complex SQL, it's better to write it by hand. You get to tune it and ensure the indexing is correct (either the query usage, or create/modify your indexes), and you get to see exactly what it's doing.

For even simple conditional joins, there's something nice about knowing exactly what's going on, and knowing it's not going to be doing something that results in a nested join loop (eg: the type of slow query you don't notice until you're using it in production and performance suddenly drops).

I'm a big fan of Dapper for this, so a lot of my data layer code looks like:

    public IEnumerable<Order> LoadOrders(int customerId) 
    {
        using (var db = GetConnection())
        {
            return db.Query<Order>("SELECT * FROM orders WHERE customerId = @customerId", new { customerId });
        }
    }

The one exception I make to ORM-generated code is INSERT/UPDATE queries. For most cases, the ORM doesn't have much it can screw up, and since it's essentially just mapping code ("Name = @Name, Address1 = @Address1, etc") it's more likely I'll make a typo or copy/paste error than anything else.

I've spent too many hours debugging crappy ORM queries, and I find it generally takes twice as long (with quadruple the frustration) to get the ORM to generate the complex SQL you want it to vs just writing the SQL.