Hi, Thanks for helping. But I'm having issues with your code (Criteria part). At compiling :
Error 1 'NHibernate.Criterion.Projections' does not contain a definition for 'List' Error 2 The best overloaded method match for 'NHibernate.Criterion.Order.Desc(string)' has some invalid arguments Error 3 'NHibernate.Transform.Transformers' does not contain a definition for 'EntityToBeanCtor' I use NH 2.0, could that be the reason ? Lastly, I don't want to create another dto. Would that be ok if I add a 'average' property to my BuzzGallery class, and instead of returning a list of MyDto, I return a list of BuzzGallery entities ? On 22 jan, 14:21, Jason Meckley <[email protected]> wrote: > your query is a projection, not entity retrieval, so there is no > reason to set the distinct root entity transformer. If this data maps > to a dto class than you can do this. > [HQL] > select new MyDto(b.Name, avg(u.Rating) as Expr1, b.Scrapped, > b.DateCreated) > from BuzzGallery b > join b.BuzzGalleryArt a > join a.BuzzUserArt u > group by b.Name, b.Scrapped, b.DateCreated > having b.Scrapped = 0 > order by avg(u.Rating) desc, b.DateCreated desc > > [Criteria] > var average = Projections.Avg("u.Rating"); > var fields = Projections.List() > .Add(Projections.GroupProperty("Name")) > .Add(average) > .Add(Projections.GroupProperty("Scrapped")) > .Add(Projections.GroupProperty("DateCreated")); > var results = session. > .CreateCritera<BuzzGallery>() > .CreateAlias("BuzzGalleryArt", "a") > .CreateAlias("a.BuzzUserArt", "u") > .AddOrder(Order.Desc(average)) > .AddOrder(Order.Desc("DateCreated")) > .SetProjection(fields) > .Add(Restrictions.Eq("Scrapped",0)) > .SetResultTransformer(Transformers.EntityToBeanCtor) > .List<MyDto>(); > > where MyDto looks like this > public class MyDto > { > public MyDto(string name, decimal average, int scrapped, > DateTime dateCreated) > { > Name = name; > Average = average; > Scrapped = scrapped; > DateCreated = dateCreated; > } > > public string Name {get; private set;} > public decimal Average {get; private set;} > public int Scrapped {get; private set;} > public DateTime DateCreated {get; private set;} > > } > > On Jan 22, 5:59 am, graphicsxp <[email protected]> wrote: > > > > > Hi, > > > I need to write with the NH criteria API, the equivalent of this SQL > > query : > > > SELECT DISTINCT dbo.BuzzGallery.Name, AVG(dbo.BuzzUserArt.Rating) AS > > EXPR1, dbo.BuzzGallery.Scrapped, dbo.BuzzGallery.DateCreated > > FROM dbo.BuzzGallery INNER JOIN > > dbo.BuzzGalleryArt ON > > dbo.BuzzGallery.BuzzGalleryID = dbo.BuzzGalleryArt.BuzzGalleryID INNER > > JOIN > > dbo.BuzzUserArt ON > > dbo.BuzzGalleryArt.BuzzUserArtID = dbo.BuzzUserArt.BuzzUserArtID > > GROUP BY dbo.BuzzGallery.BuzzGalleryID, dbo.BuzzGallery.Name, > > dbo.BuzzGallery.Scrapped, dbo.BuzzGallery.DateCreated > > HAVING (dbo.BuzzGallery.Scrapped = 0) > > ORDER BY EXPR1 DESC, dbo.BuzzGallery.DateCreated DESC > > > The diagram can be seen here:http://buzzmyart.s3.amazonaws.com/diag.jpg > > > So far, I've got the following. Note that I don't know how to > > integrage the AVG part. Can someone help ? > > > ICriteria criteria = SessionManager.GetCurrentSession > > ().CreateCriteria(typeof(BuzzGallery)); > > > criteria.SetResultTransformer(new > > DistinctRootEntityResultTransformer()); > > > criteria.AddOrder(Order.Desc("DateCreated")); > > return criteria.SetCacheable(true).List<BuzzGallery>(); > > > Thanks -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
