You could define a function in a custom dialect, something like
CalculateDistance(lat1,lat2,lng1,lng2) then use either NHibernate Linq
or HQL to call this function.
Simple example:
public class CustomizedSqliteDialect : SQLiteDialect
{
public CustomizedSqliteDialect()
{
RegisterFunction(
"AddDays",
new SQLFunctionTemplate(
NHibernateUtil.DateTime,
"datetime(?1,'+' || ?2 || ' day')" // || is string
concatenation operator in sqlite
)
);
}
}
Here's an article on how you might register the custom function into
nhibernate linq:
http://fabiomaulo.blogspot.com/2010/07/nhibernate-linq-provider-extension.html
The generator would then simply look more or less like this
public override HqlTreeNode BuildHql(MethodInfo method,
Expression targetObject,
ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder,
IHqlExpressionVisitor visitor)
{
return treeBuilder.MethodCall("CalculateDistance",
visitor.Visit(arguments[0]).AsExpression(),
visitor.Visit(arguments[1]).AsExpression(),
visitor.Visit(arguments[2]).AsExpression(),
visitor.Visit(arguments[3]).AsExpression());
}
You can then use plain linq syntax to filter and/or select:
// filtering:
var results =
Session.Query<Theater>().Where(p=>DistanceExtensions.CalculateDistance(p.Latitude,
lat, p.Longitude.Long) > 60);
// selecting:
var results = Session.Query<Theater>().Select(p=>new { TheaterId =
p.Id, Distance = DistanceExtensions.CalculateDistance(p.Latitude, lat,
p.Longitude.Long) });
On May 8, 2:20 am, cs <[email protected]> wrote:
> I have the following query (PostgreSQL) that I'd like to convert to
> ICriteria:
>
> SELECT
> t.theater_id as tid,
> round(cast (sqrt(pow(69.172 * (latitude-:lat(float)),2) +
> pow(50.8750184225293 * (t.longitude-:lng(float)),2)) as
> numeric),2)
> as distance
> FROM
> theater t
>
> Is it possible to write this query using QueryOver/ICriteria? Or am I
> better off projecting this as SQL? Ideally, I'd like for this to work
> in both dev/production with developer tests using SQLite and
> production using Postgres. Some of the functions I'm using aren't
> supported on SQLite, which is causing problems for me.
>
> Thanks!
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
