Support for New Data Types and Other Improvements in dotConnect for MySQL 8.21

The new version of Devart dotConnect for MySQL contains significant improvements in Entity Framework Core support. We supported new data types Uri, IPAddress, PhysicalAddress and expanded the capabilities for translating LINQ queries into SQL. Besides, we improved support for working with JSON in LINQ queries for EF6.

Uri data type mapping

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

public class Blog {
  public int Id { get; set; }
  public Uri Url { get; set; } 
}

CREATE TABLE Blog ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  Url longtext NOT NULL, 
  PRIMARY KEY (Id) 
)

IPAddress and PhysicalAddress data type mapping

For Entity Framework Core 5 and 6, dotConnect for MySQL now supports mapping network types System.Net.IPAddress and System.Net.NetworkInformation.PhysicalAddress to MySQL 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 int AUTO_INCREMENT UNIQUE NOT NULL, 
  Url longtext NOT NULL, IP varchar(45) NOT NULL, 
  `Timestamp` datetime NOT NULL, 
   PRIMARY KEY (Id)
)

EF Core 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() 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() 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

EF6 LINQ to Entities Improvements for JSON

Despite the EF Core development, EF6 continues to be the current version of the ORM, migrating from which to EF Core is difficult for some projects due to the existing gap in functionality between EF6 and EF Core, as well as differences in behavior. Therefore, we continue to refine the EF6 functionality, primarily based on user requests.

In this version, we have improved support for working with data stored in the MySQL JSON data type.

public class JsonTable { 

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    [Column(TypeName = "json")]
    public string JObject { get; set; } 
}

The MySqlJsonFunctions class contained an Extract() method to get the text value of a field from a JSON value: 

var query = context.JsonTable.Where(t => MySqlJsonFunctions.Extract(t.JObject, "$.name") == "Apple").ToList();

SELECT 
Extent1.Id,
Extent1.JObject 
FROM JsonTable AS Extent1 
WHERE JSON_EXTRACT(Extent1.JObject, '$.name') = 'Apple'

It may also be necessary to work with typed non-string values, so methods have been added to the MySqlJsonFunctions class to get the values of other .NET types: ExtractAsByte(), ExtractAsSByte(), ExtractAsInt16(), ExtractAsInt32(), ExtractAsInt64(), ExtractAsSingle(), ExtractAsDouble(), ExtractAsDecimal(), ExtractAsDateTime().

Now, such a LINQ query can be written:

var query = context.JsonTable 
  .Where(t => MySqlJsonFunctions.ExtractAsInt32(t.JObject, "$.weight") > 10) 
  .Select(t => new { 
    Name = MySqlJsonFunctions.Extract(t.JObject, "$.name"), 
    Weight = MySqlJsonFunctions.ExtractAsDecimal(t.JObject, "$.weight"), 
    Date = MySqlJsonFunctions.ExtractAsDateTime(t.JObject, "$.date") 
}).ToList();
SELECT JSON_EXTRACT(Extent1.JObject, '$.name') AS C1,
JSON_EXTRACT(Extent1.JObject, '$.weight') AS C2,
CAST(JSON_UNQUOTE(JSON_EXTRACT(Extent1.JObject, '$.date')) AS DATETIME) AS C3
FROM JsonTable AS Extent1
WHERE JSON_EXTRACT(Extent1.JObject, '$.weight') > 10

Conclusion

We are pleased to present the updated Devart dotConnect for MySQL with new Entity Framework functionality. Further, it is planned to continue the development of EF and EF Core, expand the support for LINQ queries, and add support for mapping new data types.

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