dotConnect for Oracle Documentation
JSON Support in Entity Framework
support@devart.com

Glossary Item Box

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; }
 
}

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:

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