PostGIS and Entity Framework

Introduction

In this article we consider how to build Entity Framework models using PostGIS objects in Entity Developer. PostGIS is a popular spatial extension for PostgreSQL that gives one an opportunity to work with geographic objects. To create and use such models we will:

• use a .NET wrapper for PostGIS (SharpMap)
• build a new Entity C# code generation template in order to enable geometry types support
• write some code dealing with change tracking

Preconditions:

You need to have PostgreSQL server (version 8.0 or higher is required) with PostGIS enabled (http://postgis.refractions.net/), dotConnect for PostgreSQL Professional and VS 2008 SP1 for using PostGIS with Entity Framework.

Using a .NET wrapper for PostGIS

For efficient use of geometry types you need to have a set of .NET classes representing them. For example, let it be SharpMap. As an alternative, GeoTools.NET or .NET Topology Suite can be used.

To prepare SharpMap for using with Entity Framework you have to perform some additional actions:

• Open the Trunk part of the project.
• Check the occurence of the PostGIS.cs file in the SharpMap/Data/Providers folder. Copy the PostGIS.cs file from Sharpmap.Extensions/Data/Providers to SharpMap/Data/Providers, if it is missing
• Replace “using Npgsql;” with “using Devart.Data.PostgreSql;” and add references Devart.Data.dll and Devart.Data.PostgreSql.dll to the SharpMap.VS2008 project
• Remove all projects and leave only SharpMap.VS2008. Go to the PostGIS.cs file and run Find And Replace dialog. Change all Npgsql entries with PgSql.
• Rebuild the solution.

SharpMap is ready for work with Devart Entity Model now.

Creating a New Entity C# Code Generation Template

You have to complete following steps for Devart Entity Model creation:

• Create a new project in Visual Studio.
• Add the compiled SharpMap.dll to project references.
• Right click the Solution Explorer and select Add new item. In the appeared dialog choose Devart Entity Model from the Data page.
• Create database connection in Database explorer and drag&drop objects, contained geometry data, to the diagram. In our example we use table Towns which contains the_geom column.

Note: You can build Devart Entity Data Model in a stand-alone EntityDeveloper application or in the application integrated to the Visual Studio. The screenshot above applied to the stand-alone application.

As we can see, the geometry column in SSDL is already mapped to Binary column in CSDL. So, we are ready to work with C# code templates.

Choose Templates from the View menu for Tempates Browser viewing. Create a new template by clicking the “New…” button (call it “PostGIS Entity C#”, for example) and select Entity C# in the “Load from existing template” drop-down list.

Don’t forget to add SharpMap.Geometries and SharpMap.Converters.WellKnownBinary to the using list.

Add a method GeneratePropertyWrapper (it allows us to work with geometry field in .NET and Entity Framework) to your C# template as following:

and don’t forget to add a call to this method here:

Here is the code of GetStoreTypeName method which returns name of store type:

We have finished code template creation.

Don’t forget to set up your custom Entity template as the common template of the model (to do it open “Project Properties” dialog and select this template in the “Template” drop-down list). You can generate source code now (just save your model in VS or click “Generate” button in a stand-alone mode).

Tracking Changes

However, we still have a problem. The changes made to our Geometry entities will not be tracked. The reason is that Geometry wrapper property is not a primitive type property and thus don’t support the default change tracking.
So, we’ll have to work around this situation. The first way is to call the setter of the property explicitly in our code like in the following sample:

Town town = GeometryEntities.Towns.First();
Geometry townGeometry = town.TheGeomWrapper;
//some code to change the "g" value
//This is the explicit call of TheGeom1 setter to fire the PropertyChanged event
town.TheGeomWrapper = townGeometry;
db.SaveChanges();

The second way is to use the SavingChanges event to iterate through all the Unchanged entities and check which of them has changes in the binary underlying property. This approach is really slow, but it does not make user to seek for the every possible change in the code.

 public static void db_SavingChanges (object sender, EventArgs e) 
 {
       //for each object in collection
       //which does not contain explicit changes 
       foreach (Town t in ((DataSourceModel1Entities)sender).Towns)
        if (t.EntityState == System.Data.EntityState.Unchanged)
        {
          //The temporary binary view is created. Comparing the binary view 
          // and the underlying binary property
          byte[] wkb = t.TheGeomWrapper.AsBinary();
          if (!CheckEquality(t.TheGeom, wkb))
              t.TheGeom = wkb;//if they not equal, changing the underlying property
        }
 }

Here is equality comparer (CheckEquality method) for two byte arrays:

    public static bool CheckEquality (byte[] obj1, byte[] obj2) {
      if (obj1 == null && obj2 == null)
        return true;
      if (obj1 == null || obj2 == null)
        return false;
      if (obj1.Length != obj2.Length)
        return false;
      for (int i = 0; i < obj1.Length; i++)
        if (obj1[i] != obj2[i])
          return false;
      return true;
    }

And don’t forget to add the handler for the SavingChanges event.

 db.SavingChanges += new EventHandler(db_SavingChanges);

So, now your application is ready to work with geometry objects. Give it a try!