CODEWITHSUNDEEP
nhibernatehibernatehqlviewmodelcriteria
Felipe Oriani
Felipe Oriani

Reputation: 38618

Subquery to fetch a property NHibernate

I'm using NHibernate for a web application. I have a model like this:

public class ProductViewModel {
   public virtual int Id { get; set; }
   /* others simple properties */

   public virtual IList<OfferViewModel> LastOffers { get; set; }

   public ProductViewModel() { }
}
public class OfferViewModel {
   public virtual string UserName { get; set; }
   public virtual decimal Prince { get; set; }

   public OfferViewModel() { }
}

I would like to know, how to fetch the "LastOffers" collection property with a HQL subquery ? I want to fetch it with top 10 last offers. I have my model mapped correctly but I don't know the sintax to do a subquery fetch this property.

Today, I'm using a command like this to fetch my ViewModel:

public IList<ProductViewModel> GetProductsForSalles()
        {
            return
                Session.CreateQuery(@"select p.Id as Id, 
                                             p.Name as Name,
                                             p.Price as Price,
                                             p.Price as Date
                                             /* FETCH LastOffers? */
                                       from Product p  
                                       where p.Active=true and (p.Status=:Status)
                                       order by a.Date asc")
                    .SetParameter("Status", Status.Started)
                    .SetMaxResults(50)
                    .SetResultTransformer(Transformers.AliasToBean<ProductViewModel>())
                    .List<ProductViewModel>();
        }

Thanks!

Upvotes: 0

Views: 846

Answers (1)

Genius
Genius

Reputation: 1782

Based on description you provided I guess you need to load a set of products with preloaded lastoffers, joined to products by FK. The code below should do this:

return Session.CreateCriteria(typeof(ProductViewModel), "p")
  .CreateCriteria("p.LastOffers", "lastoffers")
  .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
  .Add(Restrictions.Eq("p.Active", true))
  .Add(Restrictions.Eq("p.Status", Status.Started))
  .SetMaxResults(50)
  .List<ProductViewModel>();

Not testes, but idea is that we join two tables and the result will be 'collapsed' to each row of product (by DistinctRootEntityResultTransformer)

Sorry that the example is not in HQL - I prefer Criterias and QueryOver<> as more stable.

Upvotes: 1

Related Questions