views:

185

answers:

2

I'm reasonably new to NHibernate and everything has been going pretty well so far but I've come across a problem I'm not exactly sure of how to go about solving. Basically I need to filter by the output of a User Defined function. If I was writing in SQL this is what I'd write:

declare @Latitude decimal
declare @Longitude decimal
declare @radius int

set @Latitude = -118.4104684 
set @Longitude = 34.1030032

select  * 
from    store
where   dbo.CalculateDistance([Latitude], [Longitude], @Latitude, @Longitude) < @radius

I've seen the formula attribute which I don't think is appropriate, named queries and examples of creating your own dialect extension (which seemed a little over kill). I would've thought there was a more straight forward way of doing it but I can't seem to find a tidy example.

+4  A: 

You could use SQL expression in your hibernate queries. Assuming you've mapped a Store type you could write the following query:

var result = session
    .CreateCriteria<Store>()
    .Add(Expression.Sql(
        "dbo.CalculateDistance({alias}.Latitude, {alias}.Longitude, ?, ?) < ?",
        new object[] { 
            -118.4104684d, 
            34.1030032d, 
            100 
        },
        new IType[] { 
            NHibernateUtil.Double, 
            NHibernateUtil.Double, 
            NHibernateUtil.Int32 
        }
    ))
    .List<Store>();
Darin Dimitrov
Perfect, thanks Darin!
toxaq
+4  A: 

Creating custom dialect extensions is rather easy:

public class CustomFunctionsMsSql2005Dialect : MsSql2005Dialect 
{ 
   public CustomFunctionsMsSql2005Dialect() 
   { 
      RegisterFunction("calculatedistance",
                       new SQLFunctionTemplate(NHibernateUtil.Int32,
                                               "CalculateDistance(?1, ?2, ?3, ?4)"));
   }
}

Register it, like so:

<property name="hibernate.dialect">
  CustomFunctionsMsSql2005Dialect, MyAssembly
</property>

Now you can use it like any other HQL function in queries like those created with session.CreateQuery().

asbjornu
Thanks for that asbjornu. It wasn't that I thought it was necessarily hard, it just seemed a little over kill considering this is likely to be the only place I needed to do such a thing. I'll keep this in mind for future projects though. I'm still learning what's appropriate and when.
toxaq
Actually, I ended up going with this method when it was decided we needed the values in the output as well. Really great technique, cheers! Only thing missing from an almost copy and past was the requirement for "dbo." at the start of the function.
toxaq