Geek Answers Handbook

Filtering Nhibernate Using a Custom Function

I'm fairly new to NHibernate, and so far everything went pretty well, but I ran into a problem, I'm not quite sure how to solve the problem. Basically, I need to filter out the output of the User Defined function. If I wrote in SQL, this is what I would 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 saw a formula attribute, which, it seems to me, does not fit, called requests and examples of creating my own dialect (which seemed a little more killed). I would have thought that there is a more direct way to do this, but I cannot find an example.

+4
source share
2 answers

You can use the SQL expression in your sleeping queries. Assuming you have mapped the 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>();
+7
source

Creating custom dialect extensions is pretty simple:

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

Register it, for example:

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

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

+4
source

More articles:


All Articles