JSON Support in Entity Framework

In This Topic

dotConnect for Oracle provides support for JSON functionality in Oracle 12c. It allows using Oracle JSON functions in LINQ to Entities queries via the OracleJsonFunctions class. They are supported in Entity Framework v4, v5, and v6.

Model

Here is a code that maps an entity to a simple table with a JSON column. The column is mapped to a property of the System.String type. In Oracle, JSON data is stored in usual CLOB and NCLOB columns.

C#	Copy Code
public class MyContext: DbContext { public DbSet<JsonTable> JsonTables { get; set; } } public class JsonTable { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } // The JSON column [Column(TypeName = "nclob")] public string JObject { get; set; } public string Text { get; set; } }

Copy Code

public class MyContext: DbContext {
 
  public DbSet<JsonTable> JsonTables { get; set; }
 
}
 
public class JsonTable {
 
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
 
  // The JSON column
  [Column(TypeName = "nclob")]
  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:

C#	Copy Code
var ctx = new MyContext(); ctx.Database.Create();

This code will create the following table:

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")
)

JSON Features in LINQ to Entities

The OracleJsonFunctions class contains the following methods:

OracleJsonFunctions methods	Corresponding Oracle Functions and Operators	Description
IsJson	IS JSON	Checks whether some data is well-formed JSON data.
Exists	JSON_EXISTS	Checks whether a particular value exists within some JSON data.
TextContains	JSON_TEXTCONTAINS	Checks whether a specified string exists in JSON property values.
Value	JSON_VALUE	Select a scalar value from JSON data as an SQL value.

Query Example

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

C#	Copy Code
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 query:

Test note.

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

[email protected]