2

I'd like to get the max of the count of some grouped by query with Projections or Subqueries. Is it possible?

     AEntity aAlias = null;
     BEntity bAlias = null;
     var cntQuery = await _session.QueryOver<AEntity>()
        .Left.JoinAlias(pv => pv.BEntity, () => bAlias)
        .Select(
            Projections.Group<AEntity>(e => e.Id),
            Projections.Count(Projections.Property<AEntity>(x => x.Id)))
        .ListAsync(ct);

So this returns something like this:

+----------------------------------------+-------+
|                   id                   | Count |
+----------------------------------------+-------+
| "af517a65-18c2-4e9f-9df6-a537cc5c9c92" |     5 |
| "48bf681d-2ccd-4df8-b0e5-b2c3f418e3d0" |     1 |
| "c0699258-9f2b-4ce6-a895-91d759cbde29" |     3 |
| "0959f6b4-b365-43fa-aede-25df327a27d1" |     2 |
+----------------------------------------+-------+

I need the max of these but wouldn't like to use:

  • order desc and take
  • LINQ after ListAsync
  • SQL query
Tamas Toth
  • 359
  • 6
  • 19
  • https://stackoverflow.com/questions/5708177/nhibernate-queryover-with-maxresult-group-by-and-order-by Something like that? Just order by the count-projection then Take(1) and you will get the Max Count. – hightech Jul 22 '20 at 08:52
  • Yes, it could work, but as I mentioned in the question I wouldn't like to order desc and take if there is native support for this max selection. – Tamas Toth Jul 22 '20 at 09:31

1 Answers1

0

Can you try this and see if it works?

var results = session.QueryOver<AEntity>()
        .Left..JoinQueryOver(pv => pv.BEntity, () => bAlias)
        .SelectList(list => list
            .SelectGroup(pv => pv.Id)
            .SelectCount(() => pv.Id)
        )
        .List<object[]>();
Govind
  • 439
  • 3
  • 6