0

How can I create the following SQL statement in Nhibernate using CreateCriteria:

SELECT distinct top 20 a.* from ActivityLog a left join WallPost w on a.ActivityLogId = w.ActivityLogId left join ItemStatus i on i.StatusId = w.ItemStatus

I always tend to get all columns from all tables returned in the sql statement producing duplicates even though I map it to the ActivityLog table. I am also doing paging as the code below shows:

ICriteria crit = nhelper.NHibernateSession.CreateCriteria(typeof(Model.ActivityLog), "a").CreateAlias("a.WallPosts", "w",CriteriaSpecification.LeftJoin) .CreateAlias("w.ItemStatus", "i", CriteriaSpecification.LeftJoin) .SetMaxResults(pageSize).SetFirstResult(startRow).AddOrder(Order.Desc("a.Date"));

Thanks

H

Hem
  • 1
  • 2

2 Answers2

0

Sounds like you have set lazy loading to false in your mapping files meaning that all the associations and child collections are being loaded up too. Can you verify that?

Samuel Goldenbaum
  • 18,391
  • 17
  • 66
  • 104
0

You ask "How to select one table using NHibernate CreateQuery" (HQL). In that case you can choose what to fetch using select.

In your text you're using criteria. AFAIK, you cannot directly control what columns you want to read. However, if you create a DetachedCriteria for the join, this won't be fetched.

Roger
  • 1,944
  • 1
  • 11
  • 17
  • Yes just changed that. Do you have a sample of how to achieve this? – Hem Jul 05 '12 at 13:33
  • select a from ActivityLog a left join a.WallPosts w left join w.ItemStatus i order by a.Date skip 4 take 10 – Roger Jul 05 '12 at 13:52
  • ...where the numbers for skip and take (of course) is for paging. – Roger Jul 05 '12 at 13:53
  • Thanks, is it possible to do this without using CreateQuery, because i have to generate a fairly complex where clause. I haven't included that here because it would make the code too long. – Hem Jul 05 '12 at 14:02
  • Yes, as I wrote, use DetachedCriteria for the joins if you want to control what columns are fetched. http://nhforge.org/doc/nh/en/index.html#querycriteria-detachedqueries – Roger Jul 05 '12 at 14:14
  • How do I do a left join to a detachedcriteria? – Hem Jul 05 '12 at 14:52