Dapper Order By

Question

Is there any reason why the following code wouldn't be retrieved in the correct order when using dapper?

connection.Query<User>("SELECT id, name " +
                       "FROM user " +
                       "ORDER BY @sort @dir " +
                       "LIMIT @offset, @pageSize; ",
                       new {
                           sort = sortOrder, // sortOrder = "name"
                           dir = sortDirection, // sortDirection = "ASC"
                           offset = pageIndex * pageSize, // offset = 0
                           pageSize = pageSize // pageSize = 10
                       });

It always returns without applying the ordering.

I could just place the sortOrder and sortDirection directly into the string like this

"SELECT id, name " +
"FROM user " +
"ORDER BY " + sortOrder + " " + sortDirection + " " +
"LIMIT @offset, @pageSize; "

but I'm not sure how that will effect dapper since I believe it has it's own query plan caching.

Also, is there a way to view the query that is generated by dapper?

Answer 1

Sure, in general database engines do not allow you to parametrize column names. So for example:

var row = cnn.Query("select @bob as col from table", new {bob = "col"}).first(); 
// most likely returns "row.col == col"

When you are trying to parametrize order by clauses I would recommend using inline substitution, provided you can guarantee your escaping, bobby tables is always lurking. (Either that or you could use a proc)

I am not sure about the situation around profilers for (MySQL? Oracle?) but you could use a tool like MiniProfiler to see the SQL.

It will affect caching, however there are only a small number of permutations of sortOrder and sortDirection so the impact is minimal.