How to convert this HQL to DetachedCriteria?

Question

This HQL statement, when executed, produces the following result:

select t, count(s) from Submission s right join s.Topics as t GROUP BY t.Id result[0] [0] topic_id, topic_name, ... [1] 10 result[1] [0] topic_id, topic_name, ... [1] 12 . result[n] [0] topic_id, topic_name, ... [1] 19 

This DetachedCriteria API produces almost the same result, but without loading a theme.

 ProjectionList PrjList = Projections.ProjectionList(); PrjList.Add(Projections.GroupProperty("Topics"), "t"); PrjList.Add(Projections.Count("Id")); DetachedCriteria Filter = DetachedCriteria.For<Submission>(); Filter.CreateCriteria("Topics", "t", JoinType.RightOuterJoin); Filter.SetProjection(PrjList); result[0] [0] null [1] 10 result[1] [0] null [1] 12 . result[n] [0] null [1] 19 

For some reason, nhibernate refuses to create theme objects for a result set, but it does for an HQL query. Why is this?

Answer 1

Look at the individual criteria on the Ayende blog at http://ayende.com/blog/tags/nhibernate?page=3 It gives you a very detailed walkthrough on left outer joins and the HQL group, as well as the mapping behind.

Answer 2

 var orderIDsContainingCurrentSku = DetachedCriteria.For<OrderItem>() .Add<OrderItem>(x=>x.Product.SKU==sku) .SetProjection(Projections.Property("Order.id")); var skusOfProductsAppearingInOrdersContainingCurrentSku = DetachedCriteria.For<OrderItem>() .SetProjection(Projections.GroupProperty("Product.id")) .AddOrder(NHibernate.Criterion.Order.Desc(Projections.Count("Order.id"))) .Add<OrderItem>(x=>x.Product.SKU!=sku) .Add(Subqueries.PropertyIn("Order.id", orderIDsContainingCurrentSku)) .SetMaxResults(15); var recommended = _session.CreateCriteria<Product>() .SetFetchMode<Product>(x => x.Descriptors, FetchMode.Join) .Add(Subqueries.PropertyIn("id", skusOfProductsAppearingInOrdersContainingCurrentSku)) .SetResultTransformer(Transformers.DistinctRootEntity) .List<Product>(); 

And here is the SQL result:

 SELECT this_.SKU as SKU1_1_, this_.ProductName as ProductN2_1_1_, this_.BasePrice as BasePrice1_1_, this_.WeightInPounds as WeightIn4_1_1_, this_.DateAvailable as DateAvai5_1_1_, this_.EstimatedDelivery as Estimate6_1_1_, this_.AllowBackOrder as AllowBac7_1_1_, this_.IsTaxable as IsTaxable1_1_, this_.DefaultImageFile as DefaultI9_1_1_, this_.AmountOnHand as AmountO10_1_1_, this_.AllowPreOrder as AllowPr11_1_1_, this_.DeliveryMethodID as Deliver12_1_1_, this_.InventoryStatusID as Invento13_1_1_, descriptor2_.SKU as SKU3_, descriptor2_.DescriptorID as Descript1_3_, descriptor2_.DescriptorID as Descript1_4_0_, descriptor2_.Title as Title4_0_, descriptor2_.Body as Body4_0_ FROM Products this_ left outer join ProductDescriptors descriptor2_ on this_.SKU = descriptor2_.SKU WHERE this_.SKU in (SELECT top 15 this_0_.SKU as y0_ FROM OrderItems this_0_ WHERE not (this_0_.SKU = 'Binoculars2' /* @p0 */) and this_0_.OrderID in (SELECT this_0_0_.OrderID as y0_ FROM OrderItems this_0_0_ WHERE this_0_0_.SKU = 'Binoculars2' /* @p1 */) GROUP BY this_0_.SKU ORDER BY count(this_0_.OrderID) desc) 

See http://ayende.com/blog/4315/building-a-recommendation-engine-in-nhibernate for how this is done!