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

@Maxim Gershkovich 1. As a DB user when your write a SQL query you will want to get the columns in the order you specified. 2. How can it be fixed by LINQ? — Florin D. Preda, Dec 08 '12 at 16:22
@Rippo Not really. It's not a SortedDictionary. It may look like that from the example above which was intentionally kept simple, but actually there's no particular order of the columns. — Florin D. Preda, Dec 09 '12 at 11:06
@Maxim Gershkovich It seems that I accidentally deleted your first comment. I apologize, still new to SO... — Florin D. Preda, Dec 09 '12 at 11:07

score 2 · Accepted Answer · answered Dec 09 '12 at 14:39

2

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.

answered Dec 09 '12 at 14:39

Oskar Berggren

5,583
1
19
36

Thanks. I used AliasToEntityMap because it was the only "pre-made" option I found that includes the columns names in the result. If there's no other option, I will implement a custom IResultTransformer, but I'd rather use an existing implementation if there is any... – Florin D. Preda Dec 09 '12 at 15:09
1

The AliasToEntityMap implementation is about 20 lines of code, half of which is boilerplate, so that is nothing to worry about. The complexity of this will depend mainly of what data structure you want to end up with. An alternative to SortedDictionary might be the DataSet. But if you have SQL queries as input and want DataSet for output, the benefit of having NHibernate in between (for this usecase) is very limited or even non-existent. – Oskar Berggren Dec 09 '12 at 16:06
True story. Done :). Thanks! – Florin D. Preda Dec 09 '12 at 17:16

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

1 Answers1