Fluent NHibernate Map a Stored Procedure which returns a Dynamic set of Columns

Question

I have to access a set of stored procedures via NHibernate.

One of these stored procedures returns a result, however the columns returned are different based on the parameters passed in.

For example if I pass in the dates 01/01/2014 and 01/01/2015 I might get the results

column1, column2, column3

However if I pass in a different date range the stored procedure may return a different set of columns e.g.

column1, column2, column3, column4, column5, column6

How can I map this to an entity?

Is it possible to somehow have a map that maps all the columns that could possibly come back and then just set the properties as null if the column does not come back from the stored procedure?

public class ModelMap : ClassMap<Model>
    {
        public ModelMap()
        {
            this.ReadOnly();
            this.Id(x => x.Date);
            this.Map(x => x.Column1)
            this.Map(x => x.Column2)
            this.Map(x => x.Column3)
            this.Map(x => x.Column4)
            this.Map(x => x.Column5)
            this.Map(x => x.Column6)            
        }
    }

Any ideas on how I could get this type of stored procedure to map to an entity?

Take a look at: http://stackoverflow.com/questions/16415877/fluent-nhibernate-to-query-stored-procedure-without-an-hbm-xml-mapping — Jedediah, Dec 03 '14 at 15:28
Yes this is what I have done but my 'MyDomainObject' in .AddEntity(typeof(MyDomainObject)) some of the properties on this are not returned from the stored procedure and sometimes they are it all depends on what parameters are passed in. So it breaks when they are not returned by the stored procedure. — Leigh Ciechanowski, Dec 03 '14 at 15:41

score 4 · Accepted Answer · answered Dec 15 '14 at 12:48

instead of have a dynamic mapping you could issue the query using SQLQuery and transform the results manually using a result transformer

public List<Model> CallFoo(ISession session, DateRange range)
{
    return session.CreateSqlQuery("call sproc")
        .SetParameter(...)
        .SetResultTransformer(new ModelResultTransformer())
        .List<Model>();
}

class ModelResultTransformer : NHibernate.Transform.IResultTransformer
{
    public IList TransformList(IList collection)
    {
        return collection;
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        var model = new Model();
        for (int i = 0; i < aliases.Length; i++)
        {
            var columnName = aliases[i];
            var value = tuple[i];
            switch (columnName)
            {
                case "column1":
                    model.Prop1 = (string)value;
                    break;
                case "column2":
                    model.Prop2 = (int)value;
                    break;
                case "column3":
                    model.Prop1 = (int)value;
                    break;
            }
        }
    }
}

Fluent NHibernate Map a Stored Procedure which returns a Dynamic set of Columns

1 Answers1