NHibernate calling PostgresSQL's SELECT DISTINCT ON ()

Before asking this question I have googled for some time, but could not find any relevant information on this topic.

My problem is simple:

I have NHibernate criteria and projection and I'm trying to set DISTINCT ON(column)

My code for projection is following:

        criteria.SetProjection(
            Projections.ProjectionList()
            .Add(Projections.Distinct(Projections.Property("ID")))
            .Add(Projections.Property("A"))
            .Add(Projections.Property("B"))
         );

This generates following SQL (bit simplified):

SELECT DISTINCT ID, A, B FROM ABC ORDER BY A

But unfortunately the performance if this query is very-very poor.

I have optimized my SQL that runs much faster and looks like this:

SELECT DISTINCT ON (A) ID, A, B FROM ABC ORDER BY A

Is th开发者_StackOverflowere anyway I can make NHibernate generate SQL that I have just shown? Is this problem solvable with NHibernate's dialects?

Looking forward to Your feedback! Thank You very much!

This is not the most elegant solution, by any means but I'm wondering if you can accomplish what you need by using a "SQLProjection"? I'm a Java guy, so the code may not be exactly right here, but the following approach looks like it works for me using Postgres:

criteria.SetProjection(
    Projections.ProjectionList()
    .Add(Projections.SqlProjection("DISTINCT ON(A) ID"))
    .Add(Projections.Property("A"))
    .Add(Projections.Property("B"))
 );

Which generates the optimized SQL query you gave in your question. Again, this approach may not work if you're doing something highly dynamic, but it is A way of tackling the problem.