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

Glossary Item Box

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

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

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'