NHibernate query with distance calculation stored proc

Question

I have a stored procedure in my database that calculates the distance between two lat/long pairs. This stored procedure is called "DistanceBetween". I have a SQL statement allows a user to search for all items in the Items table ordered by the distance to a supplied lat/long coordinate. The SQL statement is as follows:

SELECT Items.*, dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude) AS Distance
FROM Items
ORDER BY Distance

How do I go about using this query in NHibernate? The Item class in my domain doesn't have a "Distance" property since there isn't a "Distance" column in my Items table. The "Distance" property really only comes into play when the user is performing this search.

Answer 1

You could try:

session.CreateSqlQuery(@"SELECT {item.*}, dbo.DistanceBetween(:lat1, :lat2, {item}.Latitude, {item}.Longitude) AS Distance
    FROM Items {item}
    ORDER BY Distance")
        .AddEntity("item", typeof(Item))
        .SetDecimal("lat1", lat1)
        .SetDecimal("lat2", lat2)
        .List<Item>()

NHibernate is finicky about table & column aliases in the query, so you need to let it expand them using the {} syntax. Also, use the HQL named parameter syntax (:lat1 instead of @lat1), and change SetDecimal() to the correct data type.

Answer 2

Ayende explains how to do this in the mapping files, without haveing to resort to passing SQL to your session:

http://ayende.com/Blog/archive/2006/09/18/UsingNHibernateWithStoredProcedures.aspx

Also see the Hibernate documentation regarding this issue (also relevant to NHibernate):

http://docs.jboss.org/hibernate/stable/core/reference/en/html/querysql.html

Answer 3

There are basically three approaches that can be used, some of which have already been discussed:

1. Use an HQL query or CreateCriteria/ICriteria query; downsides: it is not part of the entities/DAL; upsides: it is flexible;
2. Use a property mapping with a formula; downsides: it is not always feasible or possible, performance can degrade if not careful; upsides: it the calculation an integral part of your entities;
3. Create a separate XML HBM mapping file and map to a separate (to be created) entity; downsides: it is not part of the base entities; upsides: you only call the SP when needed, full control of mapping / extra properties / extensions, can use partial or abstract classes to combine with existing entities.

I'll briefly show an example of option 2 and 3 here, I believe option 1 has been sufficiently covered by others earlier in this thread.

Option two, as in this example, is particularly useful when the query can be created as a subquery of a select statement and when all needed parameters are available in the mapped table. It also helps if the table is not mutable and/or is cached as read-only, depending on how heavy your stored procedure is.

<class name="..." table="..." lazy="true" mutable="false>
  <cache usage="read-only" />

    <id name="Id" column="id" type="int">
      <generator class="native" />
    </id>
    <property name="Latitude" column="Latitude" type="double" not-null="true" />
    <property name="Longitude" column="Longitude" type="double" not-null="true" />

    <property name="PrijsInstelling"
        formula="(dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude))"
        type="double" />

    ... etc
</class>

If the above is not possible due to restrictions in the mappings, problems with caching or if your current cache settings retrieve one by one instead of by bigger amounts and you cannot change that, you should consider an alternate approach, for instance a separate mapping of the whole query with parameters. This is quite close to the CreateSqlQuery approach above, but forces the result set to be of a certain type (and you can set each property declaratively):

<sql-query flush-mode="never" name="select_Distances">
    <return
        class="ResultSetEntityClassHere,Your.Namespace"
        alias="items"
        lock-mode="read" >

        <return-property name="Id" column="items_Id" />
        <return-property name="Latitude" column="items_Latitude" />
        <return-property name="Longitude" column="items_Longitude" />
        <return-property name="Distance" column="items_Distance" />
    </return>

    SELECT 
        Items.*, 
        dbo.DistanceBetween(@lat1, @lat2, Latitude, Longitude) AS Distance
    FROM Items
    WHERE UserId = :userId

</sql-query>

You can call this query as follows:

List<ResultSetEntityClassHere> distanceList = 
    yourNHibernateSession.GetNamedQuery("select_Distances") 
        .SetInt32("userId", currentUserId)  /* any params go this way */
        .SetCacheable(true)                 /* it's usually good to cache */
        .List<ResultSetEntityClassHere>();  /* must match the class of sql-query HBM */

Depending on your needs, you can choose an approach. I personally use the following rule of thumb to decide what approach to use:

• Is the calculation light or can it be cached? Use formula approach;
• Are parameters needed to be sent to the SP/SQL? Use sql-query + mapping approach;
• Is the structure of the query (very) variable? Use ICriteria or HQL approach through code.

About the ordering of the data: when you choose the "formula" or the "sql-query mapping" approach you'll have to do the ordering when you retrieve the data. This is not different then with retrieving data through your current mappings.

Update: terrible edit-mistake corrected in the sql-query XML.