C Sharp Generic Dapper Query Joining two tables

Question

I am a c# noobie, but here is the gist of what I am trying to do:

    public IEnumerable<T> GetAll<T, K>(string schemaName) where T : GenericModel
    {
        var sql = @"SELECT * from " +
            schemaName + "." + T.getTableName() + " primaryTable LEFT JOIN " +
            schemaName + "." + K.getTableName() +
            " ForeignTable on ForeignTable.id = primaryTable." + T.getForeignFieldName();

        return _connection.Query<T, K, T>(sql, (primary, foreign) =>
        { (primary = T.getForeignFieldName()) = foreign; return primary; });
    }

As an example T= Employee and K = Person

    public IEnumerable<Employee> GetAll(string schemaName)
    {
        var sql = @"SELECT * from " + schemaName + 
            ".Employee primaryTable LEFT JOIN " + 
            schemaName + ".Person ForeignTable "+
            "on ForeignTable.id = primaryTable.person_id";
        return _connection.Query<EmployeeModel, PersonModel, EmployeeModel>
            (sql, (primary, foreign) => { primary.person = foreign; return primary; });
    }

GenericModel has the getForeignFieldName() and getTableName() which are shadowed by the derived classes (Employee and Person)

The example Models might look like:

Person {
  int id;
  string Name;
  string address;
  int age;
}

Employee {
  int id;
  int person_id;
  Person person;
  double salary;

}

Of course the generic solution doesn't work and I'm aware of sql injection. I just want to a generic method that I can use the way I have described with Dapper's Query() function.

In other words, how do you write a generic function that can do this: Dapper's Multi Mapping

Answer 1

Yup, sorry, but in the friendliest way - you've strayed off the reservation :-) The creation of a query is the moment where you specify what data you want, and what objects it gets returned to. People use dynamically constructed SQL to fiddle with the WHERE or ORDER BY clauses, but that's already a total anti-pattern in my view. By postponing the eventual decision about what tables, you've got a class that adds no value. Worse, you cut off vital possibilities, like a WHERE clause, or limiting the columns returned. In polite society, it's considered somewhat louche to ask for all columns in all rows from two joined tables!

Moral of the story, don't try and write 1 query that will do everything. Write more, smaller queries that bring back just what you need, just when you need it.