0
SELECT 
  [ProductErpIdentifier], 
  [PriceValue], 
  [ManufacturerProductIdentifier], 
  SUM([QuantityOrdered]) as  [QuantityOrdered]
FROM 
  [eCommerceDev].[dbo].[OrderItem] 
GROUP BY 
  [ProductErpIdentifier],[PriceValue],[ManufacturerProductIdentifier]
ORDER BY  
  [QuantityOrdered] desc

How should I write a CreateCriteria to generate SQL like the example above?

TedOnTheNet
  • 1,082
  • 1
  • 8
  • 23
stefan
  • 11
  • 3

1 Answers1

0

This should do the trick. Due to the fact you did not post your mapping of the orderitem ntity I used the fieldnames as property names. You will need to change OrderItemEntity to your own mapped class, and you need to change the names preceded by oi. by the names of the properties in your class (be aware: these strings are case sensitive!)

var yourResult = Session.CreateCriteria<OrderItemEntity>("oi")
                   .SetProjection(Projections.ProjectionList()
                      .Add(Projections.Alias(Projections.GroupProperty("oi.ProductErpIdentifier"), "ProductErpIdentifier"))
                      .Add(Projections.Alias(Projections.GroupProperty("oi.PriceValue"), "PriceValue"))
                      .Add(Projections.Alias(Projections.GroupProperty("oi.ManufacturerProductIdentifier"), "ManufacturerProductIdentifier"))
                      .Add(Projections.Alias(Projections.Sum("oi.QuantityOrdered"), "QuantityOrdered")))
                   .AddOrder(Order.Desc("QuantityOrdered"))
                   .List();
TedOnTheNet
  • 1,082
  • 1
  • 8
  • 23
  • thanks for answer now i get Could not find a setter for property 'SummedQuantityOrdered' in class 'OrderItem' , i need to add som temporary column OrderItem – stefan Jun 13 '13 at 12:16
  • iam using this also http://nkpatterson.blogspot.se/2010/12/re-usable-nhibernate-paging-extension.html – stefan Jun 13 '13 at 12:21
  • @stefan does it work if you remove the AddOrder? if not, please post the code of your entity (orderitem) and it's mapping file. – TedOnTheNet Jun 13 '13 at 12:40
  • yes it working if remove addorder , it also working if i sort on other columns – stefan Jun 13 '13 at 19:26
  • @stefan I updated the code above, that should work with the addorder as well. – TedOnTheNet Jun 14 '13 at 09:26