Entity Framework Core Spatials Support in dotConnect ADO.NET Providers

The new releases of dotConnect ADO.NET providers for Oracle, MySQL, PostgreSQL, and SQLite support Spatial data in Entity Framework Core 3.

Entity Framework Core 3 does not limit entities to properties of primitive System types, like, String, Int32, DateTime. It allows using a wide range of classes, so you can now work with Spatial data in Entity Framework Core via the NetTopologySuite GIS library.

Note for Users Who Upgrade to EF Core from EF5 or EF6

Entity Framework v5 and v6 support spatial data types. These types 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). However, these classes themselves were not independent, but they were rather wrappers for full-featured classes from some third-party GIS library.

dotConnect providers for Oracle, MySQL, and PostgreSQL supported spatial data for Entity Framework v5 and v6 via the NetTopologySuite 1.x library. Upgrade process would be complicated not just because you need to use the corresponding NetTopologySuite classes (Geometry, Point, LineString, Polygon, etc.), but also because of partial incompatibility between EF Core and EF v5 and v6.

Note for Users Who Upgrade dotConnect Providers for MySQL or SQLite from Earlier Versions

If you had dotConnect for MySQL v 8.17 / dotConnect for SQLite v 5.15 or earlier, you need to reset type mapping rules for the provider in Entity Developer when updating to new provider versions in order to have the type new mapping rules for spatial types added.

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

NetTopologySuite (NTS) Spatial Library

Linking NetTopologySuite to Provider

If you create an Entity Framework Core model in Entity Developer via the database-first approach, it downloads all the necessary NuGet packages and links assemblies automatically. If you use code-first approach, and write the classes and mapping code yourself, you will need to perform additional actions.

.NET Core 3 or Higher

If you target .NET Core 3 or .NET 5, and you use dotConnect NuGet packages, all the necessary assemblies are loaded automatically. Just install the NuGet package of the corresponding provider:

• Devart.Data.Oracle.EFCore.NetTopologySuite
• Devart.Data.MySql.EFCore.NetTopologySuite
• Devart.Data.PostgreSql.EFCore.NetTopologySuite
• Devart.Data.SQLite.EFCore.NetTopologySuite

Full .NET Framework

If you target Full .NET Framework, and you use assemblies, installed by dotConnect installer, you need to add the Devart.Data.<provider>.Entity.EFCore.NetTopologySuite.dll assembly from the Entity/EFCore3 subfolder of the provider installation folder to the project references.

Additionally, you need to install the NetTopologySuite NuGet package of version 2.1.0.

dotConnect for SQLite uses SpatiaLite SQLite extension for working with spatial data. To get SpatiaLite, you need to install the following NuGet packages:

• mod_spatialite 4.3.0.1
• NetTopologySuite.IO.SpatiaLite 2.0.0

NetTopologySuite Configuration

Call the UseNetTopologySuite() method for DbContext options builder of the corresponding provider to link NetTopologySuite to the provider and enable the ability to map properties to spatial data types. Here are examples for Oracle, MySQL, PostgreSQL and SQLite:

optionsBuilder.UseOracle(
  @"Host=orcl;User Id=scott;Password=tiger;",
  x => x.UseNetTopologySuite());

optionsBuilder.UseMySql(
  @"Host=db;Port=3306;User Id=root;Password=root;Database=test;",
  x => x.UseNetTopologySuite());

optionsBuilder.UsePostgreSql(
  @"Host=db;Port=5432;User Id=postgres;Password=postgres;Database=test;",
  x => x.UseNetTopologySuite());

optionsBuilder.UseSQLite(@"Data Source=database.db;FailIfMissing=False;",
  x => x.UseNetTopologySuite());

Supported NetTopologySuite Data Types

Our Entity Framework Core provider supports a number of NetTopologySuite data types. Geometry is the base type for them, and you can use it in your application. However, you may use specific data types for properties if the corresponding database column stores only corresponding spatial figures:

Mapping NetTopologySuite Types to Database Data Types

Suppose, we have the following class:

  public class City {
     public int Id { get; set; }
     public Point Geometry { get; set; }
     [MaxLength(200)]
     public string Name { get; set; }
  }

Oracle Mapping

It’s not necessary to specify the column type for spatial properties, because Oracle uses only one spatial type – SDO_GEOMETRY, but you may specify it anyway:

  modelBuilder.Entity()
    .Property(p => p.Geometry)
    .HasColumnType("sdo_geometry");

MySQL Mapping

You can specify any geometry type, supported by MySQL: geometry, geometrycollection, linestring, point, polygon, multilinestring, multipoint, multipolygon.

   modelBuilder.Entity()
    .Property(p => p.Geometry)
    .HasColumnType("point");

PostgreSQL Mapping

You can specify geometry or geography type:

   modelBuilder.Entity()
    .Property(p => p.Geometry)
    .HasColumnType("geography");

SQLite Mapping

You can specify any supported geometry type: geometry, geometrycollection, linestring, point, polygon, multilinestring, multipoint, multipolygon.

Additionally, it is better to specify SRID (in this example, 4326 is a WGS 84 identifier, that is used for geographical calculations on the Earth surface):

   modelBuilder.Entity()
    .Property(p => p.Geometry)
    .HasColumnType("point")
    .HasSrid(4326);

Conclusion

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