Dealing with spatial data in OData
At Craze we’ve been using OData for a while now and are generally happy with how it offloads the query creation logic from the server to the client. One of the more unpleasant aspects though, is working with spatial data. We’re using EntityFramework and SQL Server for persistence and they have out of the box support for spatial types like DbGeography, but they are different from what OData uses — GeographyPoint, etc. This makes it especially annoying when you want to query for all entities within a certain area. So, a while back, @fealebenpae and I came up with a solution which allowed us to write distance OData queries that get properly translated to EF spatial queries and executed by SQL Server. Without further ado, let’s see the code:
Model
First, let us define an entity that has location:
As obvious from the comments, we need to have two separate properties to satisfy OData and EF, and the DbGeography one should be the source of the truth. Let’s update our DbContext and EdmModel now:
We simply tell EF and OData to ignore things they don’t understand. Now we get the benefit that the entity will be properly serialized in responses — OData will use the GeographyPoint property which serializes nicely (DbGeography json serialization is unpleasant at best). The next step is to enable OData queries over the location. Enter
The Visitor
We will create an expression visitor that rewrites the expression generated by OData and replaces OData spatial methods with EF ones. Luckily they translate very nicely, which makes me wonder why the OData team hasn’t done this already. We’ll implement it for Distance, but it can be similarly done for any other method that exists for both types.
Most of the interesting points have comments. The high level overview is: we figure out we’re dealing with GeographyPoint.Distance call, we replace the GeographyPoint property with the DbGeography one, and we convert the GeographyPoint constant to DbGeography instance and rewrite the expression to use those. Then this expression is passed to EF, and it correctly executes it on SQL Server.
Also, note that this is a fairly simple example, where we know the name of the DbGeography property. If you don’t or have multiple properties that you want to query against, you’ll have to come up with a convention, e.g.:
Wiring it together
Finally, we need to execute our visitor, for OData queries. Easiest way to do so is to define an ActionFilterAttribute:
You can now apply this attribute over methods you know might return spatial data, or add it globally in WebApiConfig:
Almost done
In a perfect world, that would be enough. If you try to execute a distance query you’ll notice that OData will let you know it doesn’t understand distance. Sadly, it seems the OData team has left out spatial methods out of the filter binders. Luckily, OData is open source and we can easily rectify this omission. Head over to https://github.com/OData/WebApi/ and pull the latest sources. Then, find ClrCanonicalFunctions and FilterBinder (there are three of them — you’ll want System.Web.OData if you’re using v4 and System.Web.Http.OData if you’re on v3) and make the following changes:
Basically, we’re telling OData to properly handle the geo.distance query. Similarly, you might want to add any other methods you plan to use. Build it and reference the modified version in your project. And that’s it :) You can now easily write queries like
http://myservice.com/api/MyEntities?$filter=geo.distance(Coordinates,geography’SRID=4326;POINT (12.345 7.890)’) lt 1000.0Conclusion
There are other workarounds out there that deal with the unfortunate incompatibility between EF and OData spatial types, most notably, the ODataSpatialSample by Sam Xu. I, however, feel that this sample is rather hacky and inapplicable to real world scenarios. The nice thing about the proposed solution is that it is very general and does not pollute your controllers. Basically, you plug it in, and, as long as you follow your conventions, you might as well forget it exists.
