CODEWITHSUNDEEP
c#sql-serverstored-proceduresnhibernatefluent-nhibernate
Leigh Ciechanowski
Leigh Ciechanowski

Reputation: 1317

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

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?

Upvotes: 2

Views: 4248

Answers (1)

Firo
Firo

Reputation: 30813

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;
            }
        }
    }
}

Upvotes: 4

Related Questions