Converting SQL Server query containing GROUP BY into NHibernate LINQ

Question

I'm basically trying to retrieve a paged list of unique GUIDs, sorted by (row) creation date.

I've been able to draft a SQL Server query that seems to work for me based on this answer, but now I have to translate that into LINQ.

SELECT TOP 15 payment.ClientRef, 
          MAX(payment.CreatedDateUtc)
FROM PaymentTransactionState payment
INNER JOIN OrderState orderstate ON payment.ClientRef = orderstate.ClientRef
WHERE orderstate.UserId = 2 AND 
      payment.PaymentState IN (
        'Rejected',
        'Authorized') 
GROUP BY payment.ClientRef
ORDER BY MAX(payment.CreatedDateUtc) DESC,
         payment.ClientRef

Problem is, I can't apply GroupBy on an IQueryOver, I'm probably missing the appropiate syntax:

session
    .QueryOver<Payment>()
    .JoinAlias(orderState => orderState.OrderStateEntity, () => orderStateRow)
    .Where(() => orderStateRow.UserId == customer.UserId)
    .WhereRestrictionOn(payment => payment.PaymentState).IsIn(paymentStates)
    .GroupBy(pts => pts.ClientRef)
    .OrderBy(payment => payment.CreatedDateUtc).Desc
    .Skip(pageIndex*pageSize)
    .Take(pageSize)
    .List();

I could probably do the group by in query syntax, but I'm not so sure about the Skip & Take bit.

You should specify what Linq provider you wish to use; e.g. linq-to-sql or entity-framework. Either involves building a set of classes that map to your database tables and through which you construct your Linq queries. — sshine, Nov 26 '15 at 12:55
Have you tried to translate it yourself? What problem have you faced? — Ivan Stoev, Nov 26 '15 at 13:03
I forgot to mention: NHibernate; I've updated the story with the exact challenge I'm facing — Alexandru Marculescu, Nov 26 '15 at 13:15
execute the SQL query from entity framework using ExecuteSqlQuery function — Awais Mahmood, Nov 26 '15 at 13:23
Don't do that! NHibernate is an **ORM**, it maps tables to objects. It's NOT a replacement for SQL. Grouping has nothing to do with objects. Just write and execute the SQL statement. Better yet, create a view and map your result object to this view — Panagiotis Kanavos, Nov 26 '15 at 16:28
PS. If the *entites* are related, there should be a mapping between them. If you need to join, you are missing a relation in the mapping — Panagiotis Kanavos, Nov 26 '15 at 16:32

score 0 · Answer 1 · answered Nov 26 '15 at 12:56

I would try like this:

var query = db.PaymentTransactionState 
  .Where( pts => pts.OrderState.UserId == 2 &&
                 new string[] {"Rejected", "Authorized"}.Contains(pts.PaymentState) )
  .GroupBy( pts => pts.ClientRef )
  .OrderByDescending( pts => pts.Max( p => p.CreatedDateUtc))
  .ThenBy( p => p.Key )
  .Take(15);

score -1 · Accepted Answer · answered Nov 26 '15 at 15:41

So here's what worked for me: basically I had to use SelectList instead of GroupBy; SelectGroup, SelectMax & TransformUsing were easy to tackle once I found that;

PaymentRow paymentAlias = null;
OrderStateRow orderStateRow = null;

var transactionStateRows = session
    .QueryOver<PaymentRow >()
    .JoinAlias(orderState => orderState.OrderStateEntity, () => orderStateRow)
    .Where(() => orderStateRow.UserId == customer.UserId)
    .WhereRestrictionOn(payment => payment.PaymentState).IsIn(paymentStates)
    .SelectList(list => list
        .SelectGroup(payment => payment.ClientRef).WithAlias(() => paymentAlias.ClientRef)
        .SelectMax(payment => payment.CreatedDateUtc).WithAlias(() => paymentAlias.CreatedDateUtc))
    .TransformUsing(Transformers.AliasToBean<PaymentRow >())
    .OrderBy(payment => payment.CreatedDateUtc).Desc
    .Skip(pageIndex*pageSize)
    .Take(pageSize)
    .List();

I'll leave this here in case someone might find my travails useful in the future. Thank you for your replies.

This code is almost unmaintainable. ORMs aren't replacements for SQL. They are meant to make mapping tables to entities easier. In this case there are no entities or tables. This is a reporting scenario, something that is *not* meant to be used with ORMs. Just use the original query. — Panagiotis Kanavos, Nov 26 '15 at 16:32

Converting SQL Server query containing GROUP BY into NHibernate LINQ

2 Answers2