Issue with spatial types in entity framework, dotconnect for postgreSQL v6.7

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
locus
Posts: 3
Joined: Wed 03 Jul 2013 08:40

Issue with spatial types in entity framework, dotconnect for postgreSQL v6.7

Post by locus » Thu 01 Aug 2013 09:07

I have done database first using the following table with entity framework and dotconncet for postgresql 6.7.

Code: Select all

CREATE TABLE geo 
(
  the_geom geometry,
  id integer NOT NULL,
  CONSTRAINT primary_key PRIMARY KEY (id),
  CONSTRAINT enforce_srid_geometry CHECK (st_srid(the_geom) = 4326)
)
and running the following code the st_srid(the_geom) = 4326 fails.

Code: Select all

class Program {
    static void Main(string[] args) {
        using (test_Model.test_Entities ctx = new test_Model.test_Entities()) {
            var geom = new test_Model.geo();
            geom.id = 0;
            geom.the_geom = DbGeometry.PointFromText("POINT (1 1)", 4326).AsBinary();
            ctx.geos.AddObject(geom);
            ctx.SaveChanges();
    }
}
Is there an alternative approach where the srid data does not disappear (or, is set to 0 before it reaches the database)?

I noticed that with database first approach the type of the geometry data in the model is binary instead of (for example) DbGeometry. I tried to change the type to geometry but then I got the following error

get the following error when I try to change the type of one property in my model to Geometry:

"Member Mapping specified not valid. The type 'Edm.Geometry [Nullable=True, DefaultValue=,SRID=0,IsStrict=False]' of member (...) is not compatible with 'Devart.Data.PostgreSQL.geometry [Nullable=True,DefaultValue=,MaxLength=2147483646,FixedLength=False]' of Member (...)"

isn't there a geometry type on the c# side of things that corrensponds to the Devart.Data.PostgreSQL.geometry?

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: Issue with spatial types in entity framework, dotconnect for postgreSQL v6.7

Post by Shalex » Tue 06 Aug 2013 12:55

locus wrote:isn't there the geometry type on the c# side of things that corrensponds to the Devart.Data.PostgreSQL.geometry?
There is a corresponding geometry type which should be used instead of byte[] on .NET side:
1) .NET 4.0 -> System.Data.Entity.Spatial.DbGeometry in EntityFramework.dll v6
2) .NET 4.5 -> System.Data.Spatial.DbGeometry in System.Data.Entity.dll

You are using Entity Developer (the Devart Entity Model item, *.edml), aren't you?

After installing dotConnect for PostgreSQL v 6.7.287 (or higher), navigate to Visual Studio > Tools > Entity Developer > Options > Servers Options > PostgreSql and press the Reset button. This is necessary so that the new mapping rules were added to your Type Mapping Rules list:
geography (Server Type) -> Data.Spatial.DbGeography (.NET Type)
geometry (Server Type) -> Data.Spatial.DbGeometry (.NET Type)


Now remove the Geo entity from your model and drag&drop the geo table from Tools > Entity Developer > Database Explorer to diagram surface. Open Tools > Entity Developer > Model Explorer and make sure that the type of geomentry property is:
spatial_geometry in SSDL
Geometry in CSDL

Save the model.

Add this entry to your app.config:

Code: Select all

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="GeoAPI" publicKeyToken="a1a0da7def465678" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.7.1.0" newVersion="1.7.1.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
Run the following code:

Code: Select all

class Program {
    static void Main(string[] args) {

        // new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };

        var config = Devart.Data.PostgreSql.Entity.Configuration.PgSqlEntityProviderConfig.Instance;
        config.SpatialOptions.SpatialServiceType = Devart.Data.PostgreSql.Entity.Configuration.SpatialServiceType.NetTopologySuite;

        using (test_Model.test_Entities ctx = new test_Model.test_Entities()) {
            var geom = new test_Model.geo();
            geom.id = 0;
            geom.the_geom = DbGeometry.PointFromText("POINT (1 1)", 4326);
            ctx.geos.AddObject(geom);
            ctx.SaveChanges();
    }
}
We recommend using the dbMonitor tool to enable tracing of the database activity:
http://www.devart.com/dotconnect/postgr ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe

Additional information:
1) the version of SharpMap in your project should be 1.0 RC3 (http://sharpmap.codeplex.com/releases/view/106717). The 1.0 Final version will be supported in dotConnect for PostgreSQL soon
2) please employ Postgis of the 2.0 (or higher) version. You can check the version by executing "select postgis_version()" in the database

The corresponding Devart documentation is available at http://blog.devart.com/enhanced-entity- ... resql.html.

Does this help?

Post Reply