CODEWITHSUNDEEP
nhibernatequeryover
Alexey Zimarev
Alexey Zimarev

Reputation: 19610

NHibernate QueryOver with JoinQueryOver select all columns

I have some QueryOver with several joins and the result I get is OK in terms of the returned objects. This is the code>

   var l = session.QueryOver<Discount>(() => discount)
                .JoinQueryOver<ConPrdGrp>(r => r.ConPrdGrps)
                .JoinQueryOver<PrdGroupTree>(k => k.PrdGroupTree)
                .JoinQueryOver<Product>(g => g.Products)
                .Where(p => p.ProductID == Product.ProductID)
                .And(() => discount.FomDato <= DateTime.Now && discount.TomDato >= DateTime.Now).List();

But if I look at the SQL statement, I can see that the generated query selects ALL columns from all joined tables although the result only returns a list of Discount objects. I can get some properties of Discount using projections and the query will be much smaller. But how I can instruct NHibernate to get just columns from the Discount table and not from all joined tables?

Upvotes: 2

Views: 4688

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

As far as I know there is NOT a simple way like: .Select(Projections.Properties<Discount>()). Check this a bit outdated question (NHIbernate: Shortcut for projecting all properties?)

What you can do is explicitly name the columns you would like to see:

  ... // the QueryOver you have
  .SelectList (l => l
    .Select(() => discount.ID).WithAlias(() => discount.ID)
    .Select(() => discount.Code).WithAlias(() => discount.Code)
    ...
  )
  .TransformUsing(Transformers.AliasToBean<Discount>())
  ...

The second approach could be to create the Subquery, and then just QueryOver<Discount>() 16.8. Subqueries

QueryOver<Discount> subQuery =
    QueryOver.Of<Discount>(() => innerDiscount)
      .JoinQueryOver<ConPrdGrp>(r => r.ConPrdGrps)
      .JoinQueryOver<PrdGroupTree>(k => k.PrdGroupTree)
      .JoinQueryOver<Product>(g => g.Products)
      .Where(p => p.ProductID == Product.ProductID)
      .And(() => innerDiscount.FomDato <= DateTime.Now 
              && innerDiscount.TomDato >= DateTime.Now).List()
      .Select( Projections.Property(() => innerDiscount.ID))
 ;

var query = session.QueryOver<Discount>(() => discount)
  .Where(Subqueries.PropertyIn("ID", subQuery.DetachedCriteria))
  ;

Upvotes: 3

Related Questions