Query over Max with function

Question

I am try to construct this query in nHibernate:

SELECT max(split_part(person.Name,'-',2)) 
FROM   data.person

How can I make this using projections ?

I have this at the moment :

session.QueryOver<PersonEntity>()
                          .Select(Projections.Max<PersonEntity>(x=>Projections.SqlFunction("split_part", NHibernateUtil.String, Projections.Property<PersonEntity>(p=>p.Name), Projections.Constant("-"), Projections.Constant(2))))
                          .SingleOrDefault<int>()

But I can make it work in nHibernate.

Answer 1

You're close:

session.QueryOver<PersonEntity>()
    .Select(
        Projections.Max(
            Projections.SqlFunction(
                new SQLFunctionTemplate(
                    NHibernateUtil.String,
                    "split_part(?1, ?2, ?3)"),
                NHibernateUtil.String,
                Projections.Property<PersonEntity>(p => p.Name),
                Projections.Constant("-"),
                Projections.Constant(2))))
    .SingleOrDefault<int>();

You could also clean this up a bit by registering the function in your own dialect (I'm assuming you're using PostgreSQL:

public class CustomPostgresDialect : PostgreSQLDialect
{
    public CustomPostgresDialect()
    {
        this.RegisterFunction(
            "split_part",
            new SQLFunctionTemplate(
                NHibernateUtil.String,
                "split_part(?1, ?2, ?3"));
    }
}

And then use that dialect instead inside of your configuration file.

Then, you could add your own custom method that calls the split_part method:

public static class ProjectionExtensions
{
    public static IProjection SplitPart<T>(
        Expression<Func<T, object>> expression,
        string delimiter, 
        int field)
    {
        return Projections.SqlFunction(
            "split_part",
            NHibernateUtil.String,
            Projections.Property<T>(expression),
            Projections.Constant(delimiter),
            Projections.Constant(field));
    }
} 

Then, your query would end up looking a little cleaner:

session.QueryOver<PersonEntity>()
    .Select(
        Projections.Max(
            ProjectionExtensions.SplitPart<Person>(p => p.FullName, "-", 2)))
    .SingleOrDefault<int>();