CODEWITHSUNDEEP
c#nhibernatefluent-nhibernatequeryover
user3511244
user3511244

Reputation: 247

Query over Max with function

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.

Upvotes: 1

Views: 780

Answers (1)

Andrew Whitaker
Andrew Whitaker

Reputation: 126052

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

Upvotes: 1

Related Questions