Enhanced Entity Framework Spatials support for Oracle, MySQL and PostgreSQL

Entity Framework v5 and v6 support spatial data types. They are represented as two DbGeometry and DbGeography data types from System.Data.Entity.dll in .NET Framework 4.5 (Entity Framework 5) or from EntityFramework.dll (Entity Framework 6). We have already supported Entity Framework Spatials for Oracle database (see Using Entity Framework Spatials with Oracle Spatial and SharpMap). In the new versions of our providers we have improved Entity Framework Spatials support for Oracle and added spatials support for MySQL and PostgreSQL.

Note for Users Who Upgrade dotConnect Providers from Earlier Versions

Current Entity Framework Spatials mapping is implemented in dotConnect for Oracle v 8.3, dotConnect for MySQL v 8.3, and dotConnect for PostgreSQL v 7.3. If you had earlier version of any of these providers installed, and upgraded to these or later versions, you need to reset type mapping rules for the provider in Entity Developer in order to have the type new mapping rules for spatial types added.

To do it, From the Entity Developer submenu of the Visual Studio Tools menu select Options. In the Options dialog box, expand the Entity Developer -> Servers Options node and select Oracle or MySQL or PostgreSQL options page depending on the provider you have installed. On the selected options page, click the Reset button. The following type mapping rules will be added:

Oracle

• SDO_GEOMETRY (Server Type) -> Data.Spatial.DbGeometry (.NET Type)

MySQL

• curve (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• geometry (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• geometrycollection (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• linestring (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• multicurve (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• multilinestring (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• multipoint (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• multipolygon (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• multisurface (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• point (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• polygon (Server Type) -> Data.Spatial.DbGeometry (.NET Type)
• surface (Server Type) -> Data.Spatial.DbGeometry (.NET Type)

PostgreSQL

• geography (Server Type) -> Data.Spatial.DbGeography (.NET Type)
• geometry (Server Type) -> Data.Spatial.DbGeometry (.NET Type)

Note that when resetting type mapping rules, any customizations you have made earlier will be lost. If you don’t want to reset type mapping rules, you may need to add the rules listed above manually to work with spatial types via Entity Framework.

Common Functionality

Spatial services and GIS libraries

A set of spatial-specific configuration settings was implemented. All these settings are optional except for the spatial service that will be used for reading spatial objects from the database, saving them to the database, and run-time support for the functionality of DbGeometry/DbGeography classes.

The following spatial services were implemented:

• NetTopologySuite spatial service
• Well-Known Text (WKT) spatial service
• Extended Well-Known Text (EWKT) spatial service
• Well-Known Binary (WKB) spatial service
• OracleObject spatial service (Devart dotConnect for Oracle only)
• Old SharpMap v0.9 spatial service (Devart dotConnect for Oracle only, outdated and not recommended to use)

NetTopologySuite spatial service provides the richest functionality out-of-the-box. Other spatial services can be used together with user/3rd-party GIS libraries if you implement the interaction between them.

SharpMap

The new version of geospatial mapping library SharpMap 1.1 is now supported. New SharpMap release uses geometry types from the NetTopologySuite instead of the old SharpMap-specific implementation from SharpMap v0.9, so we supported NetTopologySuite GIS-library too. Migration from SharpMap v0.9 to NetTopologySuite significantly extends the supported functionality of DbGeometry/DbGeography classes.

dotConnect for Oracle retains the support of old geometry types of SharpMap GIS-library for compatibility, but this support is deprecated and not recommended to use in new projects. dotConnect for MySQL and dotConnect for PostgreSQL don’t support SharpMap v0.9 at all, they only support NetTopologySuite GIS-library.

NetTopologySuite

Support for NetTopologySuite geospatial mapping library is added to dotConnect for Oracle, MySQL, and PostgreSQL. Currently, the 1.13.2 version is supported.

You need to deploy the following assemblies with the application in order to use NetTopologySuite together with SharpMap 1.1:

• SharpMap.dll
• NetTopologySuite.dll
• GeoAPI.dll
• PowerCollections.dll

You may enable using NetTopologySuite in the application config file (the example here is for dotConnect for Oracle and Entity Framework 6):

  <configSections>
    <section name="Devart.Data.Oracle.Entity" type="Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfigurationSection, Devart.Data.Oracle.Entity, Version=7.7.278.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </configSections>
  <Devart.Data.Oracle.Entity xmlns="http://devart.com/schemas/Devart.Data.Oracle.Entity/1.0">
    <SpatialOptions SpatialServiceType="NetTopologySuite" />
  </Devart.Data.Oracle.Entity>

Or you may enable using NetTopologySuite in the application code:

var config = OracleEntityProviderConfig.Instance;
config.SpatialOptions.SpatialServiceType = SpatialServiceType.NetTopologySuite;

NetTopologySuite functionality support is implemented in the following assemblies:

• Devart.Data.Oracle.Entity.Spatials.dll (dotConnect for Oracle)
• Devart.Data.MySql.Entity.Spatials.dll (dotConnect for MySQL)
• Devart.Data.PostgreSql.Entity.Spatials.dll (dotConnect for PostgreSQL)

You need to add a reference to the corresponding assembly to your project in order to use NetTopologySuite functionality in it.

Increased Performance of Reading EWKT Representation of Spatial Data

The new versions of our providers implement new Entity Framework provider options:

• config.SpatialOptions.AlwaysUseGeometryDefaultSrid – forces the provider to always use the config.SpatialOptions.GeometryDefaultSrid value instead of reading the actual SRID for the DbGeometry spatial from the database. Default value is false.
• config.SpatialOptions.AlwaysUseGeographyDefaultSrid – forces the provider to always use the config.SpatialOptions.GeographyDefaultSrid value instead of reading the actual SRID for the DbGeometry spatial from the database. Default value is false.

Enabling these options may be useful for EWKT, NetTopologySuite, and SharpMap spatial services in case all the database objects have the same SRID value. Performance gain is the most significant for Oracle and MySQL as they don’t have the built-in functions for retrieving EWKT values, and complex SQL statements are generated for retrieving these values by default. It also reduces traffic a bit for all databases, which is useful when materializing a large number of spatial objects.

Database-Specific Functionality

Oracle

The support for some database-specific Oracle Spatial functions in Entity Framework is added.

• SDO_FILTER
• SDO_NN
• SDO_RELATE
• SDO_WITHIN_DISTANCE

You can find information on these functions in the Spatial Operators section of Oracle Spatial Developer’s Guide.
These functions are supported in LINQ to Entities (via methods of the new Devart.Data.Oracle.Entity.OracleSpatialFunctions class), and in EntitySQL.

For example, to create a LINQ to Entites query similar to the following SQL statement:

SELECT *
  FROM SPATIAL_TABLE s
 WHERE SDO_RELATE(s.SPATIAL_COLUMN, :spatial_parameter, 'mask=touch+coveredby') = 'TRUE'

you may use the following code:

      var spatialValue = DbGeometry.FromText("...");

      var query = context.SpatialTable
        .Where(c => OracleSpatialFunctions.SdoRelate(c.SpatialColumn, geometryValue, "mask=touch+coveredby") == OracleSpatialFunctions.True)
        .ToList();

The following SQL statement is actually generated for this LINQ to Entities query:

SELECT 
Extent1.ID,
(CASE WHEN Extent1.SPATIAL_COLUMN IS NULL THEN NULL ELSE 'SRID=' || NVL(Extent1.SPATIAL_COLUMN.SDO_SRID, '0') || ';' || SDO_UTIL.TO_WKTGEOMETRY(Extent1.SPATIAL_COLUMN) END) AS "SpatialColumn",
Extent1.NAME
FROM SPATIAL_TABLE Extent1
WHERE ((SDO_RELATE(Extent1.SPATIAL_COLUMN, SDO_GEOMETRY(:p__linq__0, :p__linq__0_srid), 'mask=touch+coveredby')) = 'TRUE')

MySQL

MySQL provides only basic spatial functionality, however it can be sufficient for some tasks. dotConnect for MySQL provides the best possible support for existing MySQL spatial features and additionally implements the calculation of distances on the surface of a spheroid, which is not supported in MySQL out-of-the-box.

MySQL Database-specific Functions

The following database-specific MBR-based MySQL functions are supported in Entity Framework:

• MBRContains
• MBRDisjoint
• MBREqual
• MBRIntersects
• MBROverlaps
• MBRTouches
• MBRWithin

You can find information on MySQL MBR-based (Minimal Bounding Rectangles) spatial functions in the Functions for Testing Spatial Relations Between Geometric Objects section of MySQL 5.6 Reference Manual. These functions are supported in LINQ to Entities (via methods of the new Devart.Data.MySql.Entity.MySqlSpatialFunctions class), and in EntitySQL.

When using DbGeometry/DbGeography methods in LINQ to Entities, shape-based spatial functions are used for MySQL 5.6 and higher by default. If you want to use shape-based spatial functions for some cases and MBR-based spatial functions for other cases, use DbGeometry/DbGeography methods for cases where shape-based spatial functions must be used, and for cases where MBR-based spatial functions must be used, use MySqlSpatialFunctions methods. If you want to use only MBR-based spatial functions, you may configure DbGeometry/DbGeography behaviour so that it will always generate MBR-specific SQL with the by setting config.SpatialOptions.UseObjectShapeBasedFunctions to false.

Calculating Distances for DbGeography

Devart dotConnect for MySQL implements correct calculation of distances on the surface of a spheroid (i. e. on the Earth surface) for the Distance method of the DbGeography class.

This behaviour can be customized with the config.SpatialOptions.GeographyDistanceUnit Entity Framework provider configuration option. With this option you can specify the distance unit to return the result in. The following units are available:

• Meter (default unit)
• Kilometer
• Mile
• Nautical mile
• Yard
• Foot

PostgreSQL

PostGIS provides very rich spatial functionality, and we have done our best to provide support for all the main spatial features for Entity Framework. Postgis version 2.0 (or higher) is required for working with Entity Framework Spatials. You can check the version by executing “select postgis_version()” in the database

PostgreSQL Database-specific Functions

The following database-specific PostgreSQL functions are supported in Entity Framework:

• ST_AsGML
• ST_AsLatLonText
• ST_AsKML
• ST_AsSVG
• ST_AsX3D
• ST_Affine
• ST_HausdorffDistance

You can find information on these PostGIS functions in PostGIS Special Functions Index.

Calculating Distances and Areas for DbGeography

The behaviour of Distance, Length, and Area methods of the DbGeography class can be customized with the config.SpatialOptions.GeographyDistanceUnit and config.SpatialOptions.GeographyAreaUnit Entity Framework provider configuration options. With these options you can specify the distance and area units to return the result in.

The following distance units are available for config.SpatialOptions.GeographyDistanceUnit:

• Meter (default unit)
• Kilometer
• Mile
• Nautical mile
• Yard
• Foot

The following area units are available for config.SpatialOptions.GeographyAreaUnit:

• Square meter (default unit)
• Square kilometer
• Square mile
• Square yard
• Square foot
• Acre
• Hectare

You may also enable the simple mode of distance and area calculations by setting the config.SpatialOptions.UseGeographySpheroidMeasurement option to False. This mode is faster but less precise.

Demo

We have prepared an updated demo project, based on the demo project from the previous article Using Entity Framework Spatials with Oracle Spatial and SharpMap. The updated sample works with Oracle, MySQL, and PostgreSQL.

Demo project changes:

• Entity Framework 6 is used (previous demo project used Entity Framework 5)
• SharpMap 1.1 Final and NetTopologySuite are used (previously SharpMap v0.9 was used)
• Demo project can be opened with Visual Studio 2010 (the previous one only worked in Visual Studio 2012)
• Now target framework is .NET Framework 4.0 (previously, .NET Framework 4.5)

For MySQL, demo project does not calculate region areas on the surface of a spheroid correctly. For PostgreSQL, all demo project features work correctly.

Download DevartSharpMapDemo sources

Conclusion

We are glad to provide Entity Framework support improvements in dotConnect for Oracle, dotConnect for MySQL, and dotConnect for PostgreSQL to our users. As for our future plans, further development of spatial functionality of our Entity Framework providers will depend on the feedback of our users.

Update

Please note that Spatial functionality has changed in Entity Framework 6.3/Entity Framework 6.. For more information about these updates, please see our newer article: https://blog.devart.com/entity-framework-6-3-and-net-core-3-support.html article.