Amazing… EntityFramework 4 + Spatial

Well, to be fair, I did not see this one coming. I can’t believe how easy this stuff worked.

So, I am dabling with new things lately. I haven’t been taking close watch to new technologies so I’m trying to get myself back up with the program. As my main focus (and joy) lies with maps nowadays, I wanted to see if and how Entity Framework would work with a Spatial Database. Now I am not going to trying big things on my first ride, so I used my SQL Server 2010 Express, installed the new Visual Studio 11 beta web and went cracking.

I already had this table in my database filled with a lot of adresses of the Netherlands, so I decided to use that to play with. After adding EF4 to my project, I first created a new model conveniently called “test”. I tested this with and older version before and found out it didn’t have Spatial types. Now, clearly, they do. 🙂

So then I proceded with reverse enginering my database. (it just contains one table with adresses) VS11 had no problems doing that, so I was up and running in no time.

Then, about 10 minutes after googling “EntityFramework for dummies” I had a working demo with live data. I queried my database for all adresses in my street. No problem!

Then, on to Spatial! (Because, well it aint fun if it ain spatial!) The Geometry Type is in fact a special SQL Server type, and it grants you functions als getting a WKT, calculating a distance between geometries, disjoints. I haven’t spend too much time checking out every option, and there is bound to be a factsheet around there somewhere.

So a short piece of testcode where I pull the adresses that are within 50m from my house.

IList adressen = new List();
using (var db = new Model1Container())
{
var homeSweetHome = DbGeometry.FromText(
"POINT (84366.76 454659.73)", 28992);
var adresQuery = from p in db.adres
where p.geometry.Distance(homeSweetHome) < 50 select p; foreach (var adres in adresQuery) { string adresje = string.Format("{0} {1}, {2}", adres.openbareruimtenaam, adres.huisnummer + adres.huisletter, adres.gemeentenaam); adressen.Add(adresje); } }

Safe to say, this worked. Note that Model1Container is my autogenerated code by EF4. DbGeometry can be found in System.Data.Spatial so it appears to be a non-SqlServer specific thing. Also, this is a query that's being run on a database with 8,5 milion adresses. This spatial query took only a few ms of time. In retrospect, this database is running on my local machine, with only me using the database. So I can't tell how it will handle big loads. (yet?) All I can say in retrospect... damn... that was easy.