Nhibernate self join read join field value

Question

I intend to read the referencing field (ParentMenu) value for my self join mapping. The class and mappings are as follows;

public class MenuSetup
{
    public virtual int MenuId { get; set; }
    public virtual string DisplayText { get; set; }
    public virtual int MenuOrder { get; set; }
    public virtual bool MenuStatus { get; set; }
    public virtual bool HasKids { get; set; }

    public virtual MenuSetup Parent { get; set; }
    public virtual ICollection<MenuSetup> SubMenu { get; set; }
 }

Mappings below;

public MenuSetupMap()
    {
        Id(x => x.MenuId).GeneratedBy.Identity();
        Map(x => x.DisplayText);
        Map(x => x.MenuStatus);
        Map(x => x.MenuOrder);
        Map(x => x.HasKids);
        HasMany(x => x.SubMenu).KeyColumn("ParentMenu"); 
        References(x => x.Parent).Column("ParentMenu"); 
            .Cascade.AllDeleteOrphan()
            .Fetch.Join().Inverse().KeyColumn("MenuId");
    }

There is ParentMenu field that i want to read like this into my view model HomeMenuViewModel.

var session = MvcApplication.SessionFactory.GetCurrentSession();
string qry = @"select p.MenuId,p.DisplayText,p.MenuStatus,p.MenuOrder,
m from MenuSetup as p left join p.Parent m";
var vm=session.CreateQuery(qry).List<object[]>()
            .Select(x=>new HomeMenuViewModel()
            {
                 MenuId=(int)x[0], 
                 DisplayText=(string)x[1], 
                 MenuStatus=(Boolean)x[2], 
                 MenuOrder=(int)x[3],
                 ParentMenu = x[10] == null ? 0 : (int)x[10]
            }).ToList();

throws an error "System.IndexOutOfRangeException was unhandled by user code". I am stuck really need help.

The query generated from NHProf is shown below;

   select menusetup0_.MenuId      as col_0_0_,
   menusetup0_.DisplayText as col_1_0_,
   menusetup0_.MenuStatus  as col_2_0_,
   menusetup0_.MenuOrder   as col_3_0_,
   menusetup1_.MenuId      as col_4_0_,
   menusetup1_.MenuId      as MenuId10_,
   menusetup1_.DisplayText as DisplayT2_10_,
   menusetup1_.MenuStatus  as MenuStatus10_,
   menusetup1_.MenuOrder   as MenuOrder10_,
   menusetup1_.HasKids     as HasKids10_,
   menusetup1_.ParentMenu  as ParentMenu10_
   from   [MenuSetup] menusetup0_
   left outer join [MenuSetup] menusetup1_
     on menusetup0_.ParentMenu = menusetup1_.MenuId

Waiting for help in earnest.

Thank you

UPDATE SOLUION

I found the solution after much tinkering

I needed to declare the field in the MenuSetup class as below

public class MenuSetup
{
    .
    .
    public virtual int ParentMenu { get; set; }
    .
}

In the Mapping class, i declared the column as well.

My retrieval code changed to

var session = MvcApplication.SessionFactory.GetCurrentSession();
string qry = @"select p.MenuId,p.DisplayText,p.MenuStatus,p.MenuOrder,
              p.ParentMenu from MenuSetup as p";
var vm=session.CreateQuery(qry).List<object[]>()
        .Select(x=>new HomeMenuViewModel()
        {
             MenuId=(int)x[0], 
             DisplayText=(string)x[1], 
             MenuStatus=(Boolean)x[2], 
             MenuOrder=(int)x[3],
             ParentMenu = x[4] == null ? 0 : (int)x[4]
        }).ToList();

Answer 1

The amount of object[] items is related to the HQL query, not to the generated SQL query. I.e. the above query will return object[] having 5 elements:

// the HQL Snippet
select 
p.MenuId,      // object[0]
p.DisplayText, // object[1]
p.MenuStatus,  // object[2]
p.MenuOrder,   // object[3]
m              // object[4]
from MenuSetup ....

So, there is no object[10] ... and that's why we do have System.IndexOutOfRangeException

The 5th element is complete parent. The solution should be like this:

ParentMenu = x[4] == null ? null : (MenuSetup)x[4]

NOTE: it could be better if we would use only one property from parent, e.g. change HQL to ...m.DisplayText...