CODEWITHSUNDEEP
c#linqnhibernate
svinja
svinja

Reputation: 5576

Translating HQL into another nHibernate query form

The HQL query is:

from BusinessObject as businessobject
    left outer join businessobject.BussinesPartner as businesspartner
    left outer join businessobject.Document as document
    left outer join businessobject.Group as group ";
where businessobject.Date >= :datefrom and businessobject.Date <= :dateto
     and (:filter = ''
          or :filter is null 
          or businesspartner.Name like '%' || :filter || '%'
          or group.Name like '%' || :filter || '%'
          or document.Name like '%' || :filter || '%')

I do not want to use HQL. I also want a strongly typed collection of results without using the inconvenient and messy SetResultTransformer. I would prefer to use Query<>(), but I don't know how to force outer joins.

When I write this in Query<>() form, it will not return a result for which the date is in the correct range and filter = "" if, for example, BusinessPartner is null. While the "where" logical expression is true for these objects, it looks like they are not in the result set anyway because of the use of inner joins:

session.Query<BusinessObject>()
       .Where(x =>
             (x.Date >= criteria.DateFrom && x.Date <= criteria.DateTo
          && (criteria.Filter == ""
             || x.BusinessPartner.Name.Contains(criteria.Filter)
             || x.Group.Name.Contains(criteria.Filter)
             || x.Document.Name.Contains(criteria.Filter))))

This doesn't work, because any row which has a null in the field referencing BusinessPartner, Group or Document will not be returned (indicating an inner join).

Is it possible to do what I want with Query<>()? Or with Criteria?

Upvotes: 1

Views: 337

Answers (1)

Mr Mush
Mr Mush

Reputation: 1538

I don't know about Query<>() but with ICriteria you can control the type of JOIN and get strongly type result by using CreateAlias.

ICriterion conjunction = Restrictions.Conjunction();
conjunction.Add(Restrictions.Contains(criteria.Filter),"BusinessPartner.Name");
conjunction.Add(Restrictions.Contains(criteria.Filter),"Group.Name");
conjunction.Add(Restrictions.Contains(criteria.Filter),"Document.Name");

 ICriteria query = ICriteria.CreateCriteria
.CreateAlias("BusinessPartner", "BusinessPartner", JoinType.LeftOuterJoin)
.CreateAlias("Group", "Group", JoinType.LeftOuterJoin)
.CreateAlias("Document", "Document", JoinType.LeftOuterJoin)
.Add(Restrictions.Between(criteria.DateFrom,criteria.DateTo),"Date")
.Add(conjunction));

List<BusinessObject> results = query.List<BusinessObject>();

Upvotes: 1

Related Questions