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.

Is there really no one who knows the answer to this? It seems like something that would be used fairly frequently. If not for this additional column that I'm essentially adding on the fly with this query, everything could be done using the standard ICriteria stuff (e.g. my Item class and mapping file work fine in normal usage).

I basically want to retrieve the Items along with one extra piece of information that is determined by a stored procedure.

For now, I'm using the following line:

CreateSQLQuery(sql).SetResultTransformer
(Transformers.AliasToEntityMap).List<Hashtable>();

This works in that it returns me all my columns, but I lose the strong typing of the Item class. Is there a better way?

I think I may have found my solution on and old blog post by (who else) Oren:

http://ayende.com/Blog/archive/2007/04/ ... y-way.aspx

Granted, what he's doing here is adding a TotalRows column, it's similar to what I'm trying to achieve in my query. I'll experiment some tonight and see if it works.

public class Distance: Item
{
private double distance;

public double DistanceBetween
{
get { return distance; }
set { distance = value; }
}
}

Your query:

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



ISQLQuery query = CurrentSession.CreateSQLQuery(sql);

//CreateSqlQuery is typically used to SELECT data out, we need to add thi
//we need to do this to trick it into thinking we're pulling out data (won't work for all db's)
query.AddScalar("item stuff 0", NHibernateUtil.Int32)
.AddScalar("item stuff 1", NHibernateUtil.String)
.AddScalar("DistanceBetween", NHibernateUtil.Double)



IList distanceBetweenList = query.List();

After you got a result in distanceBetweenList, you can move it to a:

IList<Distance> list = new List<Distance>();

Move from distanceBetweenList to list.

which means that you got a strong typing of the Distance class

Good luck!

// Mats