Is it possible to do Standard Deviation in NHibernate?

Question

Is it possible to calculate Standard Deviation with NHibernate? I'm using the Microsoft SQL Server 2005 Dialect.

I can't find any examples of this anywhere.

Please help.

thanks!

Answer 1

You can use native SQL queries, including aggregate functions, in HQL (Hibernate Query Language). See Chapter 12 in the documentation.

Edited to add: I just read it myself and STDEV is not supported. An alternative that might work is to create a view that includes the calculation and map the view instead of the table. Further Googling leads me to believe that the documentation may be out of date or there are extensions to NHibernate that include STDEV.

Answer 2

I'm not that familiar with NHibernate, but using the Java Hibernate you can subclass an existing dialect easily to add extra functions. Something like this might do the trick for you (this is the Java version):

public class MyDialect extends SQLServerDialect
{
    public MyDialect()
    {
        super();

        // register extra functions (may need to specify parameter types)
        registerFunction( "stdev", new StandardSQLFunction( "stdev" ) );
    }
}

Answer 3

STDEV is a native SQL function for SQL Server... can you pass through a native query?

Answer 4

Just wanted to let everyone know how I accomplished this with code that may help others:

When I create my SessionFactory, I simply add a custom SQL Function to the Configuration Object:

Setup:

var configuration = new Configuration();
configuration.AddSqlFunction("stdev", new StandardSQLFunction("stdev", HibernateUtil.Double));

SessionFactory = configuration.Configure().BuildSessionFactory();

Usage:

Then in my data provider I now can call the custom function:

ICriteria criteria = _session.CreateCriteria(typeof(ItemHistory));
criteria.SetProjection(Projections.SqlFunction("stdev", NHibernateUtil.Double, Projections.Property("Speed") ));
IList results = criteria.List();