Support for New Data Types and Other Improvements in dotConnect for Oracle 9.16

The new version of Devart dotConnect for Oracle contains improvement of Entity Framework Core: the support of new data types Uri, IPAddress, PhysicalAddress, and expanded ability to translate LINQ queries into SQL. Further, the support for Entity Framework Core Spatial Data functionality used to work with Oracle Spatial and Graph has expanded.

EF Core Spatial Data Mapping

Previously, we had mapping support for the MDSYS.SDO_GEOMETRY data type of Oracle only to the data types of the NetTopologySuite GIS library (classes Geometry, Point, Line, Polygon, etc.) in our EF Core 3, EF Core 5, and EF Core 6. 

public class Region { 

 public int Id { get; set; }

 [MaxLength(100)]
 public string Name { get; set; }
 
 public NetTopologySuite.Geometries.Geometry Geometry { get; set; }
}

CREATE TABLE "Regions" (
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  "Name" NVARCHAR2(100) NOT NULL,
  "Geometry" SDO_GEOMETRY NOT NULL,
  PRIMARY KEY ("Id")
)

It is convenient and almost universal. However, the conversion from Oracle Spatial and Graph to the NetTopologySuite classes of the library is done via the Well-known text (WKT) representation using Oracle’s SDO_UTIL package that has problems with functionality in some edge cases. Moreover, the functionality is overhead in terms of performance. Also, the NetTopologySuite library, like any library, is not ideal and has usage restrictions.

To eliminate the impact of the WKT transformation mechanism, both on functionality and performance, we have added the ability to map MDSYS.SDO_GEOMETRY to the Devart.Data.Oracle.OracleObject class, previously available only to our users who used only pure ADO.NET functionality. A bonus from Oracle Object is that EF Core has added support not only for mapping MDSYS.SDO_GEOMETRY to OracleObject, but also for any Oracle user-defined object data types (except for collections), since the OracleObject class supports working with UDTs. 

public class Region {

  public int Id { get; set; }

  [MaxLength(100)]
  public string Name { get; set; }
  public Devart.Data.Oracle.OracleObject Geometry { get; set; }
}

Due to the peculiarities of the current implementation, it is required to specify the mapping for the property of OracleObject type in the OnModelCreating() method of the DbContext class: 

protected override void OnModelCreating(ModelBuilder modelBuilder) {

   modelBuilder.Entity<Region>()
    .Property(x => x.Geometry)
    .HasColumnType(@"SDO_GEOMETRY");
}

EF Core Spatial Data Queries

Some operations in Oracle Spatial and Graph can be performed in several ways. Thus, we have added a new configuration option config.SpatialOptions.PreferSpatialOperators (default value is False) to provide the possibility of using spatial operators instead of functions from the SDO_GEOM package.

var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.SpatialOptions.PreferSpatialOperators = true;

The value of this option affects the SQL generation when translating in a LINQ query such methods of the Geometry class from the NetTopologySuite spatial library:

• Contains
• Disjoint
• EqualsTopologically
• Overlaps
• Relate
• Touches
• Within

For example, we have a LINQ query:

var point = new GeometryFactory().CreatePoint(new Coordinate(20, 50));
var query = context.Regions.Where(t => t.Geometry.Contains(point)).Select(t => t.Name).ToList();

With PreferSpatialOperators = false, SQL will be generated using the SDO_GEOM.RELATE function:

SELECT "r"."Name"
FROM "Regions" "r"
WHERE (CASE WHEN SDO_GEOM.RELATE("r"."Geometry", 'CONTAINS',
SDO_GEOMETRY(:p__8__locals1_point_0, :p__8__locals1_point_0_srid),
0.005) = 'CONTAINS' THEN 1 ELSE 0 END) = 1

With PreferSpatialOperators = true, SQL will be generated using the SDO_RELATE spatial operator: 

SELECT "r"."Name"
FROM "Regions" "r"
WHERE (CASE WHEN SDO_RELATE("r"."Geometry", SDO_GEOMETRY(:p__8__locals1_point_0, :p__8__locals1_point_0_srid),
'mask=CONTAINS') = 'TRUE' THEN 1 ELSE 0 END) = 1

LINQ to Entities Improvements

The SQL translation of the following class members used in the LINQ query is supported in EF Core 3, EF Core 5, EF Core 6:

• static method IsNullOrWhiteSpace() of the String class
• static property Today and instance property DayOfWeek of the DateTime class
• static methods Sqrt(), Max(), Min(), Log(), Log10(), Sin(), Cos(), Tan(), Asin(), Acos(), Atan(), Atan2(), Sinh(), Cosh(), Tanh() of the Math class

The SQL translation of the following class members used in the LINQ query is supported in EF Core 5 and EF Core 6:

• static methods Abs(), Round(), Truncate(), Floor(), Ceiling(), Pow(), Sqrt(), Max(), Min(), Log(), Log10(), Sin(), Cos(), Tan(), Asin(), Acos(), Atan(), Atan2(), Sinh(), Cosh(), Tanh() of the MathF class

The SQL translation of the following class members used in the  LINQ query is supported in EF Core 6: 

• static method Log2() of the Math and MathF classes

Uri data type mapping

For Entity Framework Core 3, 5, and 6, dotConnect for Oracle now supports mapping the internet/intranet System.Uri  type to Oracle string data types.

public class Blog { 

  public int Id { get; set; }

   [MaxLength(2000)]
   [Column(TypeName = "NVARCHAR2")] 
  public Uri Url { get; set; }
}

CREATE TABLE "Blog" ( 
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL, 
  "Url" NVARCHAR2(2000) NULL, 
  PRIMARY KEY ("Id")
)

IPAddress and PhysicalAddress data type mapping

For Entity Framework Core 5 and 6, dotConnect for Oracle now supports mapping network types System.Net.IPAddress and System.Net.NetworkInformation.PhysicalAddress to Oracle string data types.

public class AccessLog { 
  public int Id { get; set; } 
  public Uri Url { get; set; }
  public IPAddress IP { get; set; } 
  public DateTime Timestamp { get; set; } 
}

CREATE TABLE "AccessLog" ( 
 "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL, 
 "Url" NCLOB NOT NULL, 
 IP VARCHAR2(45 CHAR) NOT NULL, 
 "Timestamp" TIMESTAMP(7) NOT NULL, PRIMARY KEY ("Id")
 )

Conclusion

We are pleased to present a Devart dotConnect update for Oracle with new Entity Framework Core functionality. In the future, we plan to continue developing EF Core, expanding support for LINQ queries, improving support for Oracle Spatial and Graph, and adding support for mapping new data types.

You are welcome to download the updated versions of dotConnect for Oracle and send feedback.