Arbitrary Length ORDER BYs using NHibernate

Question

If I were doing this using PHP and MySQL, it would look something like this (disclaimer that this PHP code is not suitable for external/web-facing use, as it's vulnerable to SQL injection):

<?php

function orderByColumns ($columns, $sql) {
    if (0 < count($columns)) {
        $column = array_shift($columns);
        if (! stripos($sql, "ORDER BY")) {
            $sql .= " ORDER BY";
        }
        $sql .= " {$column['name']} {$column['dir']}";
        $sql .= 0 < count($columns) ? "," : ""; 
        return orderByColumns($columns, $sql);
    }
    return $sql;
}

$columns = array(
    array(
        "name" => "foo",
        "dir" => "ASC"
    ),
    array(
        "name" => "bar",
        "dir" => "DESC"
    )
);

$sql = "SELECT * FROM baz";

$sql = orderByColumns($columns, $sql); // And from here I could make my query

The point is that $columns is to be an input from a user somewhere, and that that could be used to order the columns without knowing the list in advance, and in a method that is reusable.

I'm looking for a way to do something similar using C# and specifically NHibernate, but it doesn't really seem to work. Here is something along the lines of what I've been trying in C#:

List<string> columns = new List<string>()
{
    "Column1",
    "Column2",
    "Column3"
    // And there could be more.
}
string column = columns.First();

fq = foo.Queryable.OrderBy(
    i => i.GetType().GetProperty(column).GetValue(i, null)
);

foreach (string column in columns)
{
    fq = fq.ThenBy(
        i => i.GetType().GetProperty(column).GetValue(i, null)
    );    
}

And, I've looked at a few StackOverflow answers (ok, more than a few), but they don't seem to be addressing how to build NHibernate queries dynamically in the way I'm looking for. The one that felt most promising is Dynamic QueryOver in nHibernate, but I'm having a hard time fully grokking whether that's even in the right direction.

Answer 1

So, the problem where is that you aren't executing anything at this point, so nhibernate is going to try to translate that to SQL, which is going to complain because it doesn't know about the GetType() method.

You'd have to build up your own Expression instance, and there aren't great ways of doing that dynamically, though it can be done, but still not fun to do.

I think it'd be easier to make a dictionary of lambda expressions and columns

var lookup = new Dictionary<string, Expression<Func<T, object>>> {
    { "ColumnA", x => x.ColumnA },
    { "ColumnB", x => x.ColumnB }
};

foreach (string column in columns) {
    fq = fq.ThenBy(lookup[column]);
}

Even then, this might not work if it complains about Expression<Func<T,object>>

Answer 2

I was intrigued by this question and wanted to take a crack at making @DarrenKopp's answer generic. My code got more long-winded than I expected, but I believe it does work. I tested with Linq to Objects, so nHibernate's Linq provider is untested.

The code is available here.

You can call to it with something like this...

var sortedItems = items.OrderBy(
    new OrderByKeyInfo ("MyPropertyA", OrderByDirection.Descending),
    new OrderByKeyInfo ("MyPropertyB", OrderByDirection.Ascending),
    new OrderByKeyInfo ("MyPropertyC", OrderByDirection.Ascending));

Answer 3

Here's a quick proof of concept around dynamic sort conditions. You might find that avoiding trips to the database via NHibernate may be better, as it may be confusing to the user if the initial sort contains, for example, 8 records, but sorting the data again returns 9, as a new record was added in between and is now displayed as we've gone back to the DB rather than just re-sorting the in-memory collection - and I'm not sure if/how this would map to NHibernate anyway.

This is a quick and dirty solution for a console application, simply to prove that it'll work, there'll be a few tweaks and optimisations available no doubt. Ultimately, the overload

List<T>.Sort(Comparison<T>)

is the one that will prevent having to dynamically create a class that implements IComparer of T:

class Program
{
    private class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public int NumberOfChildren { get; set; }
    }

    private static List<Person> people = new List<Person>()
{
    new Person() { Name="Andrew", Age=35, NumberOfChildren=3},
    new Person() { Name="Maria",Age=33,NumberOfChildren=3},
    new Person() {Name="Tim",Age=67,NumberOfChildren=4},
    new Person() {Name="Tim",Age=62,NumberOfChildren=2},
    new Person() {Name="Jim", Age=67,NumberOfChildren=2},
    new Person() {Name="Tim",Age=33,NumberOfChildren=0},
    new Person() {Name="Bob",Age=35,NumberOfChildren =3},
    new Person() {Name="Daisy",Age=1,NumberOfChildren=0}
};

    static void Main(string[] args)
    {
        List<string> sortConditions = new List<string>() { "Age", "Name", "NumberOfChildren" };

        var properties = GetSortProperties<Person>(sortConditions);

        people.Sort((Person a, Person b) =>
        {
            int result = 0;

            foreach (PropertyInfo prop in properties)
            {
                result = ((IComparable)prop.GetValue(a, null)).CompareTo(prop.GetValue(b, null));

                if (result != 0)
                    break;
            }

            return result;
        });
    }

    static List<PropertyInfo> GetSortProperties<T>(List<string> propertyNames)
    {
        List<PropertyInfo> properties = new List<PropertyInfo>();

        var typeProperties = typeof(T).GetProperties();

        foreach (string propName in propertyNames)
        {
            properties.Add(typeProperties.SingleOrDefault(tp => tp.Name == propName));
        }

        return properties;
    }
}