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)))

          );

Jessiejessika

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.

Lenwood

The substring function needed an extra parameter but this solved the problem. Many thanks – Jessiejessika

Answer 2

how big is your list? If it is less than 1000 I would collect item list from sql server then perform your regular group by query against the list

var sqlout= (from acc in this.SessionManager.GetActiveSession().Query<Account>()
          select new
                   {
                     Name = acc.Name,
                     col1= acc.col1
                   }).TolList();

and then

var prefixes = (from acc in sqlout
          group acc by acc.Name.Substring(0, 1)
          into grp
          select new
                   {
                     Prefix = grp.Key,
                     Count = grp.Count()
                   });

Substring function would work here since its running on c# list not on sql server

Lewes

At the moment we're looking at lists with over 20k accounts. :( – Jessiejessika

Answer 3

Have you considered eliminating the need for the substring query by storing the first character of the name in a separate column?

Assuming you are using SQL Server, you could make it a persisted computed column to avoid having to update the code that inserts/updates the table.

The ability to add an index containing this column should also help you improve your query performance.

Schuck

+1 As a long term solution this is probably going to be the way we're going to have to tackle this problem. Will definitely keep it in mind – Jessiejessika