CustomSelect with parameters? Trying to get calculated distance field/sorting from autoquery request

ServiceStack.OrmLite SQL Server
1

So we have an AutoQuery DTO that successfully used a template to limit for a radius like so:

[QueryDbField(Template = "geography::Point({Value1}, {Value2}, 4326).STDistance({Field}) < 10000", Field = "GeoLocation")]
public double[] Within10KMRadiusOfLatLong { get; set; }

We would also like to order by the geography::Point({Value1}, {Value2}, 4326).STDistance({Field}) as a calculated return field Distance.
The CustomSelectAttribute looks to be half the answe,r but only handles static SQL expressions, it doesn’t have any provision to take in parameters.
Any ideas on existing Attributes which might achieve our goal? Or dowe have to build a custom request implementation for this query to get the functionality we desire.

2

No there isn’t any attributes that allows custom SQL with variable placeholders, you’d need to create a custom AutoQuery impl.

3

The solution I can up with isn’t pretty, but gets the job done for now:

var q = AutoQuery.CreateQuery(req, base.Request);

var sqlSelectFragment = q.SelectExpression.Replace("SELECT", string.Empty);

q = q.Select($ "{sqlSelectFragment}, geography::Point({req.Coordinates[0]}, req.Coordinates[1]},4326).STDistance(GeoLocation)/1000 as \"DistanceKM\"");

q = q.OrderBy($ "geography::Point({req.Coordinates[0]}, {req.Coordinates[1]}, 4326).STDistance(GeoLocation)");

return AutoQuery.Execute(req, q);

Thanks for the feedback.

2 Likes