With Dapper Extensions, how do I sort by random?

Question

It seems sorting in Dapper Extensions can be achieved with Predicates:

Predicates.Sort<Person>(p => p.LastName)

My question is, how do I implement random sorting (i.e. RAND() in sql) to predicates?

Answer 1

Predicates.Sort actually produces an ISort-compatible interface which is defined as follows:

public interface ISort
{
    string PropertyName { get; set; }
    bool Ascending { get; set; }
}

It looks like we have a chance of setting property name to "RAND()" or something, right?... But, sadly, this interface is used in this way:

if (sort != null && sort.Any())
{
    sql.Append(" ORDER BY ")
       .Append(sort.Select(s => GetColumnName(classMap, s.PropertyName, false) + (s.Ascending ? " ASC" : " DESC")).AppendStrings());
}

So Dapper Extensions in fact check that the passed name is a column name. And the GetColumnName is defined as follows:

public virtual string GetColumnName(IClassMapper map, string propertyName, bool includeAlias)
{
    IPropertyMap propertyMap = map.Properties.SingleOrDefault(p => p.Name.Equals(propertyName, StringComparison.InvariantCultureIgnoreCase));
    if (propertyMap == null)
    {
        throw new ArgumentException(string.Format("Could not find '{0}' in Mapping.", propertyName));
    }
    return GetColumnName(map, propertyMap, includeAlias);
}

Therefore, the string actually should be not a column name, but a property name (they have named the field in ISort interface for a purpose, right?).

So, to implement something like the thing you want you need to customize their SQL generator.

For further details, the best way is to refer to sources:

Predicates: https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/Predicates.cs

SqlGenerator: https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/Sql/SqlGenerator.cs

PS: I am unable to post link to the repo root due to beginner's rep, hope you can guess it ;)

PPS: The same is true for whole predicates system in Dapper Extensions. I believe it should be greatly refactored to allow more than plain-forward column-based restrictions.