JSON Support in Entity Framework

In This Topic

dotConnect for MySQL provides support for JSON functionality in MySQL 5.7.8 and higher. It allows using MySQL JSON features of the json data type in LINQ to Entities queries via the MySqlJsonFunctions 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.

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 = "json")] 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 = "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:

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

This code will create the following table:

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

JSON Features in LINQ to Entities

The MySqlJsonFunctions class contains the following methods:

MySqlJsonFunctions methods	Corresponding MySQL Functions and Operators	Description
Array	JSON_ARRAY	Returns a JSON array, composed of arguments.
Length	JSON_LENGTH	Returns the number of elements in the outermost JSON array or the number of members in the outermost JSON objects or 1 for a scalar.
Depth	JSON_DEPTH	Returns the maximum depth of a JSON document. Returns NULL if the argument is NULL.
Type	JSON_TYPE	Returns the type of the outermost JSON value as a string.
Extract	JSON_EXTRACT(): equivalent to the -> operator	Returns data from a JSON document by the specified path.
ExtractUnquotedText	JSON_UNQUOTE(JSON_EXTRACT()): equivalent to the ->> operator	Returns unquoted data from a JSON document by the specified path.
Quote	JSON_QUOTE	Quotes the specified string with double quotetion marks and escapes interior quote and other characters in the string in order to make the specified string a valid JSON string literal.
Unquote	JSON_UNQUOTE	Returns an unquoted value of the JSON string literal.
Valid	JSON_VALID	Returns 1 if a valid JSON document is specified, or 0 if an invalid JSON document is specified. Returns NULL if the argument is NULL.

Query Example

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

C#	Copy Code
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)) });

Copy Code

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 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'

[email protected]