[SOLVED] Dapper, SqlBuilder extension and Order By descending

Dapper, SqlBuilder extension and Order By descending

I am trying to build a simple query that retrieves data in descending order using Dapper. The database is MySql if that's important.

This is the code I used:

var builder = new SqlBuilder();

var sql = @$"SELECT * FROM table t /**orderby**/ LIMIT @paramSkip, @paramTake";

var template = builder.AddTemplate(sql);

builder.OrderBy("@paramOrderBy DESC", parameters: new
{
    paramOrderBy = orderBy,
});

// Limit
builder.AddParameters(parameters: new
{
    paramSkip = skip,
    paramTake = take
});

return Connection.QueryAsync<TableModel>(
template.RawSql, template.Parameters,
transaction: Transaction
);

This always returns data in ascending order. DESC is just ignored. I tried using the DESC keyword in the query or as parameter but the result was the same.

Only thing that worked was putting order parameters and DESC keyword in query itself (by string interpolation)

(Edit: Typos and text simplification)

Solution

You need your query to look something like this:

... ORDER BY <Column name> DESC ...

A column name cannot be parameterized, so you need to insert it into the query something like this:

builder.OrderBy($"{orderBy} DESC");

If your orderBy originates from the user in any way, be sure to sanitize it first to prevent SQL injection. You could - for instance - keep a list of valid column names and validate against it.