Order Dapper

Question

Is there a reason why the following code will not be received 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 }); 

He always returns without applying an order.

I could just put sortOrder and sortDirection directly on a line like this

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

but I'm not sure how this will affect dapper since I believe that it has its own query plan caching.

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

Answer 1

Of course, in the general case, database mechanisms do not allow parameterization of column names. For example:

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

When you try to parameterize the order of the articles, I would recommend using the built-in replacement, provided that you can guarantee your acceleration, bobby tables are always hidden. (Either this, or you can use proc)

I'm not sure about the situation around profilers (MySQL? Oracle?), But you can use a tool like MiniProfiler to see SQL.

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