JSON Support in Entity Framework v4 – v6 in dotConnect for Oracle, MySQL, and PostgreSQL

Recent versions of MySQL and PostgreSQL databases provide JSON functionality support. They introduced special data types, storing JSON data – json data type in MySQL (since MySQL 5.7.8), and json and jsonb data types in PostgreSQL (json introduced in PostgreSQL 9.2, and jsonb introduced in PostgreSQL 9.4).

In Oracle, some JSON-related functionality was added in version 12c. Oracle Database currently does not offer a specific data type, and you should store JSON in usual CLOB and NCLOB columns.

Among other improvements, the new versions of dotConnect for Oracle, dotConnect for MySQL, and dotConnect for PostgreSQL offer support of JSON-related functionality of these databases in Entity Framework. In Entity Framework v4, v5, and v6, you can call database-specific JSON-related functions and operators in LINQ to Entities queries.

Let’s see how to do it on a simple examples:

Model

Here is the code of the model, which maps an entity to a simple table with a JSON column. The column is mapped to a property of the System.String type.

public class MyContext: DbContext {
 
  public DbSet JsonTables { get; set; }
 
}
 
public class JsonTable {
 
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
 
  // Mapping for Oracle
  [Column(TypeName = "nclob")]
  // Mapping for MySQL 5.7.8 and higher
  [Column(TypeName = "json")]
  // Mapping for PostgreSQL 9.4 and higher
  [Column(TypeName = "jsonb")]
  // Mapping for PostgreSQL 9.2 and higher
  [Column(TypeName = "json")]
  public string JObject { get; set; }
 
  public string Text { get; set; }
 
}

Database Table Creation

To create the corresponding table in the database, use the following code:

var ctx = new MyContext();
ctx.Database.Create();

This code will create the following table

Oracle

CREATE TABLE "JsonTable" ( 
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  "JObject" NCLOB NULL,
  "Text" NCLOB NULL,
  CONSTRAINT "PK_JsonTable" PRIMARY KEY ("Id")
)

MySQL

CREATE TABLE JsonTable ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  JObject json NULL,
  `Text` longtext NULL,
  CONSTRAINT PK_JsonTable PRIMARY KEY (Id)
)

PostgreSQL

CREATE TABLE "JsonTable" ( 
  "Id" serial NOT NULL,
  "JObject" jsonb NULL,
  "Text" text NULL,
  CONSTRAINT "PK_JsonTable" PRIMARY KEY ("Id")
)

LINQ to Entities Queries

Oracle

dotConnect for Oracle allows using Oracle JSON functions in LINQ to Entities queries via the OracleJsonFunctions class. The OracleJsonFunctions class contains the following methods:

For example, the following query retrieves rows where the specified JSON value (named “a”) exists in a stored JSON object and selects this value.

var query = context.JsonTables
  .Where(t => OracleJsonFunctions.Exists(t.JObject, "$.a"))
  .Select(t => new
  {
    Id = t.Id,
    Json = t.JObject,
    Value = OracleJsonFunctions.Value(t.JObject, "$.a")
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
"Extent1"."Id",
"Extent1"."JObject",
JSON_VALUE("Extent1"."JObject", '$.a') AS C1
FROM "JsonTable" "Extent1"
WHERE JSON_EXISTS("Extent1"."JObject", '$.a')

MySQL

dotConnect for MySQL allows using MySQL JSON functions in LINQ to Entities queries via the MySqlJsonFunctions class. The MySqlJsonFunctions class contains the following methods:

For example, the following query demonstrates using various MySQL specific JSON-related functions and operators.

string jsonValue = "{f0:0,f1:12}";
 
var query = context.JsonTables
  .Where(t => MySqlJsonFunctions.ExtractUnquotedText(t.JObject, "$.a") == "foo")
  .Select(t => new
  {
    Id = t.Id,
    Json = t.JObject,
    Depth = MySqlJsonFunctions.Depth(t.JObject),
    Length = MySqlJsonFunctions.Length(t.JObject),
    Field = MySqlJsonFunctions.Extract(t.JObject, "$.a"),
    UnquotedField = MySqlJsonFunctions.ExtractUnquotedText(t.JObject, "$.a"),
    ObjectType = MySqlJsonFunctions.Type(t.JObject),
    FieldType = MySqlJsonFunctions.Type(MySqlJsonFunctions.Extract(t.JObject, "$.a")),
    Quote = MySqlJsonFunctions.Quote(jsonValue),
    Valid = MySqlJsonFunctions.Valid(jsonValue),
    ValidQuoted = MySqlJsonFunctions.Valid(MySqlJsonFunctions.Quote(jsonValue))
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
Extent1.Id,
Extent1.JObject,
json_depth(Extent1.JObject) AS C1,
json_length(Extent1.JObject) AS C2,
json_extract(Extent1.JObject, '$.a') AS C3,
json_unquote(json_extract(Extent1.JObject, '$.a')) AS C4,
json_type(Extent1.JObject) AS C5,
json_type(json_extract(Extent1.JObject, '$.a')) AS C6,
json_quote(:p__linq__0) AS C7,
json_valid(:p__linq__1) AS C8,
json_valid(json_quote(:p__linq__2)) AS C9
FROM JsonTables AS Extent1
WHERE (json_unquote(json_extract(Extent1.JObject, '$.a'))) = 'foo'

PostgreSQL

dotConnect for PostgreSQL allows using PostgreSQL JSON functionality of json and jsonb data types in LINQ to Entities queries via the PgSqlJsonFunctions and PgSqlJsonbFunctions classes respectively.

Please note that the PgSqlJsonFunctions class is supported for columns of json types on PostgreSQL 9.2 and higher, and PgSqlJsonbFunctions class is supported for columns of jsonb type on PostgreSQL 9.4 and higher.

The PgSqlJsonFunctions class has the following methods:

The PgSqlJsonbFunctions class has the following methods:

The following query demonstrates using various PostgreSQL specific JSON-related functions and operators.

var query = context.JsonTables
  .Where(t => PgSqlJsonbFunctions.GetObjectFieldAsText(t.JObject, "a") == "foo")
  .Select(t => new {
     Id = t.Id,
     Json = t.JObject,
     ObjectType = PgSqlJsonbFunctions.TypeOf(t.JObject),
     FieldType = PgSqlJsonbFunctions.TypeOf(PgSqlJsonbFunctions.ExtractPath(t.JObject, "a")),
     ExtractPath = PgSqlJsonbFunctions.ExtractPath(t.JObject, "a"),
     ExtractPathText = PgSqlJsonbFunctions.ExtractPathText(t.JObject, "a"),
     GetObjectField = PgSqlJsonbFunctions.GetObjectField(t.JObject, "a"),
     GetObjectFieldAsText = PgSqlJsonbFunctions.GetObjectFieldAsText(t.JObject, "a")
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
"Extent1"."Id",
"Extent1"."JObject",
jsonb_typeof("Extent1"."JObject") AS "C1",
jsonb_typeof(jsonb_extract_path("Extent1"."JObject", 'a')) AS "C2",
jsonb_extract_path("Extent1"."JObject", 'a') AS "C3",
jsonb_extract_path_text("Extent1"."JObject", 'a') AS "C4",
"Extent1"."JObject" -> 'a' AS "C5",
"Extent1"."JObject" ->> 'a' AS "C6"
FROM "JsonTables" AS "Extent1"
WHERE ("Extent1"."JObject" ->> 'a') = 'foo'