GroupBy SqlFunction on QueryOver

Question

I have a list of all distinct account name prefixes (a-z) which I acquire using

var accounts = this.SessionManager.GetActiveSession().QueryOver<Account>();

var q = accounts.Select(Projections.Distinct(
        Projections.SqlFunction("substring", 
                                NHibernateUtil.String, 
                                Projections.Property("Name"),
                                Projections.Constant(1),
                                Projections.Constant(1))));

However what I want to do is instead of returning a distinct list is group the prefixes and return the number of accounts that start with that prefix, but I am unsure how to perform a group by using query over as it is not as straightforward as standard linq.

The reason I am using QueryOver and not Query is because for some reason the substring function is being performed in memory and not on the database server.

This is how I would usually do it

var prefixes = (from acc in this.SessionManager.GetActiveSession().Query<Account>()
              group acc by acc.Name.Substring(0, 1)
              into grp
              select new
                       {
                         Prefix = grp.Key,
                         Count = grp.Count()
                       });

Edit This is what I tried but I received the following error

Unrecognised method call in expression SqlFunction("substring", NHibernateUtil.String, new [] {Property("Name"), Constant(Convert(1)), Constant(Convert(1))})

var accounts = this.SessionManager.GetActiveSession().QueryOver<Account>().Select(
            Projections.Group<string>(x => Projections.SqlFunction("substring", NHibernateUtil.String,
                                                       Projections.Property("Name"), Projections.Constant(1),
                                                       Projections.Constant(1))),
            Projections.Count<string>(x => Projections.SqlFunction("substring", NHibernateUtil.String,
                                                       Projections.Property("Name"), Projections.Constant(1),
                                                       Projections.Constant(1)))

          );

Answer 1

You can do it using Projections.SqlGroupProjection if all else fails!

var accounts = _busDb.Session.QueryOver<QueueEntity>()
        .Select(
            Projections.SqlGroupProjection(
                "SUBSTRING({alias}.Name, 1) as FirstChar", 
                "SUBSTRING({alias}.Name, 1)",
                new[] {"FirstChar"},
                new[] {NHibernateUtil.String}),
            Projections.Count("id"));

The first argument is what is selected in the select, the second argument is what is grouped by, the third argument is the name of the column(s) that are selected, and the fourth argument is the type of data that's being selected.