Wednesday, April 25, 2012

Entity Framework 5 and Spatial Data (Part 1 - Introduction)

I've blogged a lot on NHibernate Spatial. Although it works nicely, it seems to have been left for dead. It no longer compiles without modifying the source code and has many missing features and bugs.

On the other hand, Entity Framework is finally receiving native Spatial Support on version 5 (it's about time). Although it still is in Beta 2, I believe it's time to check it out and to see how it fancies itself. I'm going to create an example using it against a SQL Server database.

I'm going to use the following model:
  • There are 18 districts, each defined by a polygon
  • The are about 140.000 places, each defined by a geographic point
I've already used this model on a previous blog post.

I'll create two separate projects:
  • Class library with the model
  • Console application with some spatial operations

Step 1. Install the Software Requirements
  • Visual Studio 11 Beta
Could I build this example in VS 2008/2010? Actually no... It seems that the Beta 2 only includes spatial support for .NET 4.5. Anyway, as I'm just testing this, no big deal. Let's install the sucker.


  • SQL Server 2008 or 2012
For this example I'll assume that the SQL Server is a non-named instance on the development machine. If that's not your case just change the connection string accordingly. It works with both SQL 2008 and 2012, although spatial support has evolved a lot in SQL Server 2012.

Step 2. Create a class library for the Model
  • Create a new class library named "SpatialDemo.Model"

  • Use NuGet to install the EntityFramework Beta. Thus, open the "Package Manager Console" and type:
install-package EntityFramework -Pre

Step 3. Create the Model
  • Add a new "ADO.NET Entity Data Model" item to the project. Name it "MainModel.edmx". The name of the model is important because the Entity Framework will determine the correct connection string based on this name.
  • As I already have the database created, I'll generate the Entity Framework model from the database.
  • Fill the connection properties. In my case the server is just "." or "(local)" and the database is "SpatialDemo".
  • Choose the tables. I'll import both and name the namespace as "SpatialDemoModel".
  • The created model should look like this:
  • Now I could just use the classes that are generated from the model. I could, but I won't, so let's clear the "Custom Tool" property of the Model.
  • I prefer to have my domain classes persistence ignorant. Thus, I'll use some Templates provided for  Entity Framework 5 to achieve this. They're in the "online" section. Choose the one named "EF 5.x DbContext Generator for C#". Name it "".
  • Two files are added to the project:
  • Before the templates can generate any code we have to replace the model name inside them. So, replace the token with: "MainModel.edmx".
  • Afterwards, save both files so the source-code generation is triggered.

  • Now, let's create a console application to test it. Name it "SpatialDemo.Console" and add a reference back to "SpatialDemo.Model".

Step 4. Query.
  • The console application will:
    • Load all districts and check that the spatial data is there
    • Load the district that contains a specific point
    • Loads the 10 places that are nearest to a specific point
The complete source-code is:
using System.Data.Spatial;
using System.Linq;

namespace SpatialDemo.Console
    class Program
        static void Main()
            using (var db = new Model.SpatialDemo())
                //1. Load all districts
                var districts = db

                foreach(var district in districts)
                    System.Console.WriteLine("District {0}. Spatial Type: {1}", 

                //2. Load the district that contains a specific point
                DbGeography point = DbGeography.FromText("POINT (-8.5 38)", 4326);

                var matchingDistrict = db.District
                    .SingleOrDefault(d => d.Area.Intersects(point));

                if (matchingDistrict == null)
                    System.Console.WriteLine("No district found ");
                    System.Console.WriteLine("Match: {0}", matchingDistrict.Name);

                //3. Get the first 10 places nearest to a specific point
                var nearestPlaces = db.Place
                    .OrderBy(p => p.Location.Distance(point))

                foreach (var place in nearestPlaces)
                    System.Console.WriteLine("Place {0}", place.Name);

I'm impressed with the easiness of all this... really impressed. But there's some caveats to this approach and in my next post I'm going to make a full "NHibernate Spatial" VS "Entity Framework 5".

Go to Part 2


  1. Ibm cognos framework uses to create a business model of metadata derived for one or more data sources. this is a windos based tool used to publish the business model in cognos. cognos online training


  2. That is nice article from you , this is informative stuff . Hope more articles from you . I also want to share some information about cognos tutorial and sap workflow tutorial

  3. Nice article....Thank you for sharing information.I also want to share some information about Best MicroNutrients Company in India