WebAPI 2.2 Blog:
Background:
As we all know OData
offers many functions to be used as part of your $filter system query. Example
In above example substring is a function used on $filter system query. Substring is
part of String Functions and then there are more like Date/Math/Type
Functions.
In OData v4 spec a new bunch of Geo Spatial functions were introduced.
Problem:
My Client was capturing Geo co-ordinates as part of their information
collection and wanted me to look into providing all objects when a particular
Lat Long with a radius is passed to the WebAPI OData.
At that time I was using
the following:
- SQL server 2008R2
- Entity Framework 6
- WebAPI v2.1
- OData v3
The SQL Server already had columns storing decimal Lat/Long. Therefore I introduced a new column of type Geography. This new column was
populated while the data was coming via OData post request. This conversion was
quite easy using the LINQ and is not part of this blog discussion.
Client was interested in enabling the Geo spatial functions.
I found out that this is supported as part of new OData v4 spec. This news was
shared with the client and was enough to start investigating and upgrading to
WebAPI 2.2 with OData v4 support. I thought it would be a straight forward
upgrade. After moving from hurdles of upgrading to new version in Development environment.
I started applying the new geo spatial functions using the browser an example
is below:
http://localhost:3665/OData/Resturants()?$filter=geo.Distance(Position,geography'
Point(-122.03547668457 47.6316604614258)') lt 900
Consider a database with all restaurants with their geographic info stored in database column named ‘Position’. So the above example should get me all restaurants with
Longitude: -122.03547668457
Latitude: 47.63166046128
Radius: Less than 900 meters
However this simple function “geo.Distance” did not yield any results. This was confirmed by Microsoft by rasing a support ticket.
Solution:
Despite of unsuccessful venture of upgrading from Odata v3
to Odata v4 , I did not give up and was adamant to find a solution . Therefore
I thought about a work around to at least support “geo.Distance” function and wait
for upgrade later when Microsoft is ready. Here are two easy steps I did.
- Inherit the EntitySetRoutingConvention class
- Activate the controller to act as soon as it encounters geo.distance in uri
Inherit the EntitySetRoutingConvention class
I had to intercept the incoming url by overriding the SelectAction
of helper class. This helper class was inherited
from EntitySetRoutingConvention
class. This SelectAction is called
before the url is handed over to the controller.
So in essence my code was reading the incoming request and
looking for “geo.distance” function when a HTTP GET is performed.
Public class helperInterceptor :
EntitySetRoutingConvention
{
Public override string SelectAction(ODataPath odataP, HttpControllerContext context,
Ilookup<> actionMap){
If (context.request.requesturi.query.contains(“geo.distance”)
&& (context.Request.Method == HTTPMethod.Get)){
Context.Request.RequestUri
= cleanTheURI(context.request.requesturi.query);
}
}
}
If such condition was satisfied then the function above “cleanTheURI” will do the following:
- This action method would rip the “geo.Distance” from $filter query
- Remove the $filter function and add the “geo.distance” as a parameter at the end of the url.
URL Before:
http://localhost:3665/OData/Resturants()?$filter=geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900&$Select=ResturantName
URL After:
http://localhost:3665/OData/Resturants()?$Select=ResturantName&geo.Distance(Position,geography'
Point(-122.03547668457 47.6316604614258)') lt 900
This cleansing is a necessary step for Odata v3, otherwise
if “$filter=geo.Distance...” is passed to controller then it would throw an exception.
The controller refuses to let the url go as this new function is not part of Odatav3
dictionary of $filter query functions.
Note: Make sure if the $filter query has other functions with logical operators then keep them there after cleansing example
URL Before:
http://localhost:3665/OData/Resturants()?$filter=startswith(ResturantName,’cafe’)
eq true and geo.Distance(Position,geography'
Point(-122.03547668457 47.6316604614258)') lt 900&$Select=ResturantName
URL After:
http://localhost:3665/OData/Resturants()?$filter=startswith(ResturantName,’cafe’)
eq true&$Select=ResturantName&geo.Distance(Position,geography'
Point(-122.03547668457 47.6316604614258)') lt 900
Activate the controller to act as soon as it encounters
geo.distance in uri
Next step is very simple i.e on the Restaurants Controller
override the GET method and again
listen for url. If URL contains the geo.Distance function then parse the
following information from the url
Longitude: -122.03547668457
Latitude: 47.6316604614258
Radius: 900
Create a point object using Lat/Long
Var loc = dbGeorgraphy.PointFromText( “Point(-122.03547668457 47.6316604614258 )” ,4326)
And apply the linq distance function, example:
From a in db.Resturnats
Orderby a.Position.Distance(loc)
Select a
If the radius is known then use the following
instead:
From a in db.Resturnats
orderby a.Position.Distance(loc)
where a.Position.Distance <= 900
Select a
Obviously
the query has potential of returning a lot of results so make that as list and
return as queryable :
List<Resturants>
r = New List<Resturnats>();
R = (
From a in db.Resturnats
orderby a.Position.Distance(loc)
where a.Position.Distance <= 900
Select a ).toList<Resturants>
return r.asQueryable()
This will enable you return the geographic specific results and
will also allow you to use rest of Odata queries like $select, $format,$orderby
etc.
Hope this helps
