Call procedure or function as part of Hibernate query

Question

I need to return records filtered and sorted by complex logic. I am planing to make sorting and filtering at the database side (function or stored procedure).

Is it possible to call procedure or function at the QueryOver or Criteria syntax in order to make filtering of the query?

Something like code shown below, where CallProcedure - calling my procedure/function

var articles = Session.QueryOver<ArticleData>()
    .Where(x => x.CompanyId == 1)
    .CallProcedure???
    .Skip(startIndex)
    .Take(number).List();

Thanks for help.

score 2 · Accepted Answer · edited May 23 '17 at 11:55

First, you would need to register your function with NHibernate, by creating a custom dialect:

public class MyDialect : MsSqlServer2008Dialect
{
    public MyDialect() 
    {
        RegisterFunction("myfunction", new SQLFunctionTemplate(...));
    }
}

After that, you can use the function within your query:

var articles = Session.QueryOver<ArticleData>()
    .Where(x => x.CompanyId == 1)
    .Where(Projections.SqlFunction("myfunction", ...))
    .Skip(startIndex)
    .Take(number).List();

Something along those lines. Not sure if you'll be able to do both filter and sort with it though. Maybe a database view will be more appropriate for that case.

Some links to get you started:
Can I use SQL functions in NHibernate QueryOver?
NHibernate QueryOver SQLFunction in where clause
Can NHibernate's QueryOver syntax select MAX() of an SqlFunction?

Call procedure or function as part of Hibernate query

1 Answers1