R – nHibernate Mapping/Query problem

nhibernate

    // Request.hbm.xml
    <many-to-one name="Grant" class="Grant" column="GrantId" lazy="false"/>

    // Grant.hbm.xml
    <set name="requests" table="Request" generic="true">
      <key column="GrantId" />
      <one-to-many class="Request"/>
    </set>

The mapping works for everything else that uses the Grant property of Request. (I added the mapping in Grant.hbm.xml to try to solve this problem.)

If I use line #1 below, NH can't find the Status property on Grant (true). If I use line #2, NH can't find the Grant.PO property on Request (also true). How can I make NH look in the right place?

    public IList<Request> GetAllActionRequired(string userName)
    {
        ISession session = GetSession();

        return session
            .CreateCriteria(typeof (Request))
            //.CreateCriteria("Grant") #1
            //.SetFetchMode("Grant", FetchMode.Eager) #2a
            //.SetFetchMode("Grant", FetchMode.Join) #2b
            .Add(Restrictions.Disjunction()
                .Add(Restrictions.Conjunction()
                    .Add(Restrictions.Eq("Status", "Draft"))
                    .Add(Restrictions.Eq("Requestor", userName)))
                .Add(Restrictions.Conjunction()
                    .Add(Restrictions.Eq("Status", "Submitted"))
                    .Add(Restrictions.Eq("Grant.PO", userName)))
                ...)
            .List<Request>();
    }

The query works if I comment out the conjunctions that look in the Grant table, so I'm just not getting the join I want.

EDIT, this worked, THANKS!!!

return session
    .CreateCriteria(typeof (Request), "r")
    .CreateCriteria("Grant", "g")
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Conjunction()
            .Add(Restrictions.Eq("r.Status", "Draft"))
            .Add(Restrictions.Eq("r.Requestor", userName)))
        .Add(Restrictions.Conjunction()
            .Add(Restrictions.Eq("r.Status", "Submitted"))
            .Add(Restrictions.Eq("g.PO", userName)))
        ...)
        .List<Request>();

Best Answer

You should use aliases like so :

return session
        .CreateCriteria(typeof (Request),"req")
        .CreateCriteria("req.Grant","gr") 

and then

.Add(Restrictions.Eq("req.Status", "Draft"))

or

.Add(Restrictions.Eq("gr.Status", "Draft"))

depending on where the properties are.

Related Topic