Nhibernate self join read join field value

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();

Answers


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...


Need Your Help

How to extract plain text and ImageUrl from HTML with PHP

php android regex

I use a rich text editor in my android app, which works by parsing rich text to HTML.