Devart Blog

PostGIS and Entity Framework

Posted by on February 9th, 2010

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:

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.
Entity Developer Model

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.

Template

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

Template

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

Template

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

We have finished code template creation. You can download it by following link.

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!

4 Responses to “PostGIS and Entity Framework”

  1. Arnaud Buisson Says:

    hello,
    hasn’t the storage part changed from latest version i can’t get the template to compile ( i don’t see TableMappings anymore) i tried to replace it by entityTableMappings without any success as i can’t find PropertyMappings…
    i’m using the last beta version
    cheers

  2. Devart Says:

    Thank you for the comment.
    The correct GetStoretypeName method code at the moment is the following:

    //Method GetStoreTypeName()
    private string GetStoreTypeName(EntityProperty property) {

    return property.GetStorageColumn().DataType;
    }

    We will add the necessary corrections to the article as soon as possible.

  3. Anders Says:

    Hi,

    Any chance this could be updated to recent versions of VS/EF/PostGis?
    The concept is very interesting, but i do not have enough knowledge of EF to figure out what is broken.

  4. Using Entity Framework Spatials with Oracle Spatial and SharpMap Says:

    […] Oracle Spatial with Entity Framework, though users of PostgreSQL, PostGIS, and SharpMap could use this cumbersome and limited workaround. This workaround was even harder to use with Oracle Spatial and was almost not used because of […]