How to preserve the columns order when using nHibernate's CreateSQLQuery?

Question

Context
I'm creating a web application and one of its features is to allow DB queries via UI. For persistance it uses nHibernate ORM. Below is the repository method which handles the incoming SQL queries:

public IList ExecuteSqlQuery(string sqlQuery)
{
    var query = Session.CreateSQLQuery(sqlQuery);
    var queryResult = query.SetResultTransformer(Transformers.AliasToEntityMap).List();

    return queryResult;
}  

Problem
The method above will return a List of Dictionaries each containing DictionaryEntry objects that have Key=ColumnName and Value=ColumnValue.

This is all well except the column names are not in the same order as specified in the original SQL query. For example, the following SQL query:

select FirstName, LastName, c.Name   
from SoftwareDeveloper sf  
join Certification c on  sf.SoftwareDeveloperId = c.SoftwareDeveloperId  

, returns for each row something like:

["FirstName"] = "John"
["Name"] = "70-515  MCTS, .NET Framework 4, Web Applications"
["LastName"] = "Doe"  

Notice that the order of the columns is not preserved. It should be:

["FirstName"] = "John"  
["LastName"] = "Doe"  
["Name"] = "70-515  MCTS, .NET Framework 4, Web Applications"  

UPDATE In the example above the keys of the dictionary are ordered by name, but this applies only to this example, which was intentionally kept simple. For larger queries the keys(=columns) are not ordered.

Question
in this context(nHibernate/CreateSQLQuery), how can I preserve the column order specified in the original query?

UPDATE#2
Solution
Solved by creating the following custom implementation of IResultTransformer:

public class DictionaryResultTransformer : IResultTransformer
{
    #region IResultTransformer Members
    public IList TransformList(IList collection)
    {
        return collection;
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        var result = new Dictionary<string, object>();
        for (int i = 0; i < aliases.Length; i++)
        {
            result[aliases[i]] = tuple[i];
        }
        return result;
    }
    #endregion
}  

In the code above the aliases are the columns names.

Sources:
Is it possible for nhibernate to return a query as an IDictionary instead of an entity class?
https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Transform/AliasToEntityMapResultTransformer.cs

Answer 1

You can't have the result as Dictionaries, as these do not define any key ordering. So you will have to replace the use of AliasToEntityMap with something that builds a data structure that preserves ordering (implement IResultTransformer). SortedDictionary might work if you give it a comparer that internally maps from column name to column index.

Note that if you call List() directly without using a result transformer, you should get a list of object arrays, in which the order of elements follows that which is specified in the select clause.