QueryOver上的GroupBy SqlFunction [英] GroupBy SqlFunction on QueryOver

问题描述

我有我使用的所有不同帐户名称前缀(a-z)的列表

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

但是，我想做的不是返回前缀列表，而是将前缀分组并返回以该前缀开头的帐户数量，但是我不确定如何通过使用查询来执行分组，因为它不是像标准linq一样简单.

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.

之所以使用QueryOver而不是Query是因为出于某种原因，子字符串函数是在内存中执行的，而不是在数据库服务器上执行的.

这是我通常会这样做的方式

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

表达式SqlFunction("substring"，NHibernateUtil.String，new [] {Property("Name")，Constant(Convert(1))，Constant(Convert(1))})中的无法识别的方法调用/em>

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

          );

推荐答案

如果其他所有方法都失败，则可以使用Projections.SqlGroupProjection来实现！

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

第一个参数是在select中选择的内容，第二个参数是按分组的内容，第三个参数是所选列的名称，第四个参数是正在处理的数据的类型选择.

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.

这篇关于QueryOver上的GroupBy SqlFunction的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！