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)))
);
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/ZveofSWb2iMJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
