Using SQL Queries in Loops: Best Practices

Joins are good, use joins. For them to be effective though the join fields must be indexed as well as fields you intend to use in where clauses. Foreign Keys are not indexed automatically in SQL Server and I imagine they aren't in most other dbs either. PKs are generally indexed bu only if you formally make them a PK (which of course you should do).

Thousands of records is teeny tiny for a database. Our medium sized db has around 20 million records in one of it's main tables and we don't have performance problems. And we often join to 15-20 tables.

What you are describing as "Inline SQL" should really be called "string concatenation without parameterization," and you don't have to do that to use a Micro ORM safely.

Consider this Dapper example:

string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;";
var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"});

It's fully parameterized, even though string concatenation is taking place. See the @ sign?

Or this example:

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", 
          new { Age = (int?)null, Id = guid });

which is roughly equivalent to the following ADO.NET code:

List<Dog> dog = new List<Dog>();
using(var cmd = connection.CreateCommand()) {
    cmd.CommandText = "select Age = @Age, Id = @Id";
    cmd.Parameters.AddWithValue("Age", DBNull.Value);
    cmd.Parameters.AddWithValue("Id", guid);
    using(var reader = cmd.ExecuteReader()) {
        while(reader.Read()) {
            int age = reader.ReadInt32("Age");
            int id = reader.ReadInt32("Id");
            dog.Add(new Dog { Age = age, Id = id });
        }
    }
}

If you need more flexibility than this, Dapper provides SQL Templates and an AddDynamicParms() function. All SQL Injection safe.

So why use SQL strings in the first place?

Well, for the same reasons you would use custom SQL in any other ORM. Maybe the ORM is code-generating sub-optimal SQL, and you need to optimize it. Maybe you want to do something that is difficult to do in the ORM natively, like UNIONs. Or, maybe you simply want to avoid the complexity of generating all those proxy classes.

If you really don't want to write a SQL string for every CRUD method in Dapper (who does?), you can use this library:

https://github.com/ericdc1/Dapper.SimpleCRUD/

That will get you extremely simple and straightforward CRUD, while still giving you the flexibility of hand-written SQL statements. Remember, the ADO.NET example above was the way everyone did it before ORM's came along; Dapper is just a thin veneer over that.