Entity Framework: Storing Complex Properties as JSON Text in Database

• Download source - 791.9 KB

Background

Entity Framework enables you to map your C# model classes to SQL relational tables. Usually, there is one-to-one relationship between model classes and relational tables. If you have properties in the model classes with simple/value types (e.g., int, double, string, boolean), every value type can be easily mapped into columns of the underlying table.

However, what can we do with the complex properties in the model classes, such as arrays and object?

Imagine that you have Blog class in your model that has few value-type properties such as BlogId (int), Url of the blog (string), and relationship to the Post class. Both classes are persisted in database as Blogs and Posts tables.

Now imagine that you want to add in the Blog class some complex properties such as tags (array of strings), or information about the blog owner (separate class).

Usually, you need to map them to separate tables because you cannot store string array in the table column. However, let's assume that you don’t want a separate table for every non-trivial property, so you don’t want to create additional BlogsTags, Tags, and Person tables for these properties.

SQL Server 2016 and Azure SQL Database (I will call them SQL Database in the following sections) enables you to store hybrid data with both relational tables and columns, but also you can store some properties as complex structures formatted as JSON text.

You can create a table that can have both standard columns and relationships to other tables, but also some semi-structured information formatted as JSON in standard NVARCHAR columns:

This might be a nice solution for some lightweight objects, such as simple arrays or key:value dictionaries, that you want to keep as part of your main entity.

Now, the main question is how can we use those kind of fields in Entity Framework to serialize some structures as arrays and smaller objects?

In this article, I will shown you how to map JSON fields from the SQL tables into the EF model classes.

Introduction

This sample is built on top of the standard Blog sample that is used in EF blog. We will start with a simple Blog/Post classes in the EF model:

public class Blog
{
    public int BlogId { get; set; }

    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
        public int PostId { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

        public int BlogId { get; set; }

        public Blog Blog { get; set; }

}

Blog and Post model classes are mapped to Blogs and Posts tables in SQL database.

In this sample, the following changes are added in the Blog model class:

1. Added string[] Tags property in EF model that represents tags associated to the blog. Tags are stored in database as json array od string.
2. Added Owner property with type Person that contains information about owner name, surname and email address. This object is stored in database as JSON object with keys Name, Surname, and Email.

In the following section, you can see how you can use these properties.

Database Setup

First, let's look at the table schema of Blogs table:

CREATE TABLE Blogs (
    BlogId int PRIMARY KEY IDENTITY,
    Url nvarchar(4000) NOT NULL,
    Tags nvarchar(4000),
    Owner nvarchar(4000)
);

INSERT INTO Blogs (Url, Tags, Owner) VALUES
('http://blogs.msdn.com/dotnet', '[".Net", "Core", "C#"]',
'{"Name":"John","Surname":"Doe","Email":"john.doe@contoso.com"}'),
('http://blogs.msdn.com/webdev', '[".Net", "Core", "ASP.NET"]',
'{"Name":"Jane","Surname":"Doe","Email":"jane@contoso.com"}'),
('http://blogs.msdn.com/visualstudio', '[".Net", "VS"]',
'{"Name":"Jack","Surname":"Doe","Email":"jack.doe@contoso.com"}');

This is the same script that is used in EntityFramework sample. The only interesting thing here is the fact that we have additional columns that will contain JSON text for tags and owner information.

Mapping JSON Columns to EF Properties

In order to map Tags and Owner JSON columns, we would need separate properties for them. We will add two internal _Tags and _Owner properties that will be used to map these columns:

public class Blog
{
   // Other properties are not displayed
   internal string _Tags { get; set; }

   internal string _Owner { get; set; }
}

These two fields will contain JSON text taken from database. Since we will not use original JSON text in our C# app, these properties are marked as internal. EF ignores internal fields in mappings we would need to define in OnModelCreating method that these properties should be used, and that they should be mapped to the columns, tags and Owner:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b._Tags).HasColumnName("Tags");

    modelBuilder.Entity<Blog>()
        .Property(b => b._Owner).HasColumnName("Owner");
}

We will create two wrapper properties around them that will de-serialize JSON text into string[] and Person object, and store serialized JSON representation when someone sets the values. These will be public properties that return actual types that will be used in our app:

public class Blog
{
   // Other properties are not displayed
   
   [NotMapped]
   public string[] Tags
   {
       get { return _Tags == null ? null : JsonConvert.DeserializeObject<string[]>(_Tags); }
       set { _Tags = JsonConvert.SerializeObject(value); }
   }

   [NotMapped]
   public Person Owner
   {
       get { return _Owner == null ? null : JsonConvert.DeserializeObject<Person>(_Owner); }
       set { _Owner = JsonConvert.SerializeObject(value); }
   }
}

Note that Tags and Owner properties are not mapped to database table. These properties are just calculated when the backing properties _Tags and _Owner are populated.

Since we have a custom Person class that contains owner information, we need a Person class in the model.

public class Person {
    public string Name { get; set; }

    public string Surname { get; set; }

    public string Email { get; set; }
}

Since this is just a utility class that is used to define properties in Owner property, it is not mapped to database table. This table will be serialized/de-serialized using JsonConvert class in property code of the Blog class. Optionally, you can add some JSON.Net attributes to customize how the fields are serialized into JSON column.

That’s all – whenever your model class is read from database or when context is saved, JSON string in these properties will be stored to JSON columns, and vice versa. Since external code uses only public properties that represent C# equivalents of JSON objects, nobody will know how these Properties are serialized.

ASP.NET MVC App that Works with the JSON Fields

In this part of the article, I will describe a simple ASP.NET MVC application that enables you to:

• Display all blogs with both value-type properties stored in standard table columns, and complex properties formatted as JSON
• Create new Blog by entering both value-type properties and complex properties
• Search for blogs by owner name that is stored in Person class and formatted as JSON key.

Displaying JSON Fields

Once we map model properties to JSON columns, we can display information from the JSON columns.

We need a view that shows Tags and Owner properties. In this example, I will show blogs on Blog/Index.cshtml page in a table. In this table will be shown Id and Url properties from the standard columns, and tags, owner name/surname, and email address that are read from the JSON columns:

<table class="table">
    <tr>
        <th>Id</th>
        <th>Url</th>
        <th>Tags</th>
        <th>Owner</th>
        <th>Email</th>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.BlogId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Url)
            </td>
            <td>
                @(item.Tags==null?String.Empty:String.Join(", ", item.Tags))
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Owner.Name)
                @Html.DisplayFor(modelItem => item.Owner.Surname)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Owner.Email)
            </td>
        </tr>
    }
</table>

BlogId and Url properties are displayed using standard DisplayFor method. Note that even JSON fields Owner.Name, Owner.Surname, and Owner.Email can be shown using this method since they are loaded into standard C# class. View cannot know whether these fields loaded from JSON text or separate Person table. In order to avoid custom template for string[], I have just shown tags separated with comma.

Entity framework will read Blog objects from database and populate internal _Tags and _Owner fields. Tags and Owner properties will de-serialize JSON text taken from database and convert it into string[] and Person properties in the blog class. Therefore, you can shown them on the view as any other field.

Inserting JSON Fields

We can create new Blog and enter fields that will be inserted both in standard columns and JSON fields, as shown in the following figure:

Url is a text input for standard Url property in Blog class. Tags are selected using multi select list, while the fields of the Owner property (that will be stored as JSON object in database) are entered as three separate text fields.

Blogs/Create.cshtml view is shown in the following code:

<form asp-controller="Blogs" asp-action="Create" 
method="post" class="form-horizontal" role="form">

<div class="form-horizontal">
<div class="form-group">

<label asp-for="Url" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Url" class="form-control" />
</div>

<label asp-for="Tags" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <select name="Tags[]" class="form-control" multiple>
        <option value="C#">C#</option>
        <option value="ASP.NET">ASP.NET</option>
        <option value=".NET Core">.NET Core</option>
        <option value="SQL Server">SQL Server</option>
        <option value="VS">Visual Studio</option>
        <option value="Azure">Azure</option>
    </select>
</div>
<label asp-for="Owner.Name" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Name" class="form-control" />
</div>
<label asp-for="Owner.Surname" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Surname" class="form-control" />
</div>
<label asp-for="Owner.Email" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Email" class="form-control" />
</div>
</div>
<div class="form-group">
    <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="Create" class="btn btn-default" />
    </div>
</div>
</div>
</form>

In this example, I’m using multiselect list to add array of tags, and standard input fields for other properties. There are two important things that you should notice:

1. Multi-select list has name “Tags[]”. This enables ASP.NET to get every selected value as separate element of string array in Tags property.
2. I need to put “Owner.Name”, “Owner.Surname”, and “Owner.Email” names in the fields that are used to enter data from the Owner property. ASP.NET will know how to map them using these paths.

In the controller action, we don’t need any special code:

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(Blog blog)
{
    if (ModelState.IsValid)
    {
        _context.Blogs.Add(blog);
        _context.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(blog);
}

ASP.NET MVC will take all input fields and put them into Blog parameter without any additional code.

Validation

You can just add all necessary annotations in Person class, and this is enough for validation:

public class Person
{
    [Required]
    public string Name { get; set; }

    public string Surname { get; set; }

    [EmailAddress]
    public string Email { get; set; }
}

In the Blogs/Create.cshtml, I need to place standard asp validator tags that reference these fields:

<div asp-validation-summary="All" class="text-danger"></div>
<label asp-for="Url" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Url" class="form-control" />
    <span asp-validation-for="Url" class="text-danger"></span>
</div>

<label asp-for="Tags" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <select name="Tags[]" class="form-control" multiple>
        <option value="C#">C#</option>
        <option value="ASP.NET">ASP.NET</option>
        <option value=".NET Core">.NET Core</option>
        <option value="SQL Server">SQL Server</option>
        <option value="VS">Visual Studio</option>
        <option value="Azure">Azure</option>
    </select>
    <span asp-validation-for="Tags" 
    class="text-danger"></span>
</div>

<label asp-for="Owner.Name" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Name" class="form-control" />
    <span asp-validation-for="Owner.Name" 
    class="text-danger"></span>
</div>

<label asp-for="Owner.Surname" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Surname" 
    class="form-control" />
    <span asp-validation-for="Owner.Surname" 
    class="text-danger"></span>
</div>

<label asp-for="Owner.Email" 
class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Email" 
    class="form-control" />
    <span asp-validation-for="Owner.Email" 
    class="text-danger"></span>
</div>

ASP.NET will apply validation on complex properties before they are serialized as JSON. This way, you can validate that inserted text is correct before you serialize it as JSON.

Note: SQL Database also enables you to add some validations on the JSON text that will be stored in NVARCHAR cell using CHECK constraint, e.g.:

ALTER TABLE Blogs
ADD CONTRAINT BlogsOwnerRules AS
CHECK( ISJSON(Owner) = 1 AND JSON_VALUE(Owner, ‘$.Name’) IS NOT NULL)

However, it is better to validate the fields as soon as possible (i.e., in the view).

Searching by JSON Fields

Finally, we are able to search for blogs using data stored in complex properties. In this example, I will show you how to filter blogs by owner name stored in Owner property. We have two approaches:

1. .NET filtering where you can create LINQ query that will filter Blog model objects by Owner properties.
2. T-SQL filtering where you can create T-SQL query that will search for blogs by JSON properties directly in database.

Filtering using LINQ Queries

Since JSON fields are materialized as C# objects in the model, you can use standard LINQ to search for the blogs by owner name:

public IActionResult Search(string Owner)
{
    // Option 1: .Net side filter using LINQ:
    var blogs = _context.Blogs
                    .Where(b => b.Owner.Name == Owner)
                    .ToList();

    return View("Index", blogs);
}

This code will take all blogs and retain those that satisfy condition that Owner.Name is same as the input parameter.

Filter using T-SQL Queries

Another approach is to write T-SQL query that will search for blogs directly in database using JSON_VALUE T-SQL function. JSON_VALUE is a function that parses JSON text in database and returns a value of the specified JavaScript-like path.

Then you can execute this T-SQL query using ForSQL method:

public IActionResult Search(string Owner)
{
    var blogs = _context.Blogs
                    .FromSql<Blog>(@"SELECT * FROM Blogs
                                    WHERE JSON_VALUE(Owner, '$.Name') = {0}", Owner)
                    .ToList();

    return View("Index", blogs);
}

FromSql method will directly execute T-SQL command on SQL Database and use JSON_VALUE function to get the value of the key $.Name from the JSON stored in Owner column. Then it will compare it with provided owner name and return blogs that have this value in the JSON column.

If you use raw T-SQL, you can even apply indexing in your T-SQL query. In this example, you can add computed column that will expose the value of the owner name and then create index on this computed column:

-- Add indexing on Name property in JSON column:
ALTER TABLE Blogs
       ADD OwnerName AS JSON_VALUE(Owner, '$.Name');

CREATE INDEX ix_OwnerName
       ON Blogs(OwnerName);

Whenever SQL Database finds a query with a predicate that filters blogs by JSON_VALUE(Owner, '$.Name') expression, it will use this index that will speed-up the queries.

Note: There are no guarantees that index will actually be used in every query, since SQL Database may choose different plans. In this example, index will not be used if you have just 3 rows in the Blogs table.

Things That Can Make This Even Better (Wish List)

Although we have a way to bind EF model and SQL JSON, there is space for further improvements in Entity Framework model. I will mention two things that are at the top on my list. In EF Core, GithHub is opened request for adding better support in EF Core https://github.com/aspnet/EntityFramework/issues/4021 so you can add comments there. Here are my two top things.

Declarative Mapping to JSON Columns

It would be really nice if we could have a declarative way for defining that some property should be serialized as JSON. Although it is not hard to create two separate properties and configure them, having something as [Serialization(JSON)] attribute on a field would be an elegant way to map properties to JSON columns without handling conversions with JsonConvert class.

Under the hood, it would probably do the same thing, because it must transform string from database into C# objects. However, it would be more elegant to just add an attribute.

JSON Filter Pushdown with LINQ

The biggest missing feature is still the fact that LINQ queries are not mapped to T-SQL queries, so EF cannot leverage the fact that SQL Database can filter or sort JSON properties in database layer.

SQL Database provides new OPENJSON function that can de-serialize JSON text from a column and return properties from JSON text. These properties can be used in any part of the query. This way, you can easily write T-SQL that filters blogs by some tag or property in Owner column.

An example of LINQ queries and T-SQL equivalents are:

1. Filtering Blogs by tag name – in LINQ, you can write something like:
   C#

   _context.Blogs.Where(b => b.Tags.Contains("C#"));

   Currently, EF will take all blog entries and apply filter in .NET layer. This query might be translated to the following T-SQL query that can be executed in database layer:

   SQL

   SELECT b.*
    FROM Blogs b
         CROSS APPLY OPENJSON(Tags) WITH (value nvarchar(400)) tags
    WHERE tags.value = 'C#'

   OPENJSON function will get the array of JSON strings and convert them into SQL table format so you can join them with the parent row and filter-out the rows that do not satisfy criterion. This way, most of the rows will be filtered-out directly in database layer and will not reach .NET layer at all.

2. Querying blogs by Owner properties – we can try to find 10 blogs using some specific owner status, sort them by Email, and return name from JSON column.
   SQL

   _context.Blogs
            .Where(b => b.Owner.Status == 1)
            .OrderBy(b => b.Owner.Email)
            .Take(10)
            .Select(b => b.Owner.Name);

   This query might be translated to:

   SQL

   SELECT TOP 10 Name
   FROM Blogs b
         CROSS APPLY OPENJSON( b.Owner ) WITH (Status int, Name nvarchar(400))
   WHERE State = 1
   ORDER BY Email

   In this case, OPENJSON function will take the JSON properties specified in the WITH clause, convert them into specified types, so you can filter or sort results using these values.

So, currently we have two options:

1. Use LINQ queries that will take everything and process objects in .NET layer
2. Write T-SQL queries that will filter and sort in database and get only required entries.

Hopefully, these two approaches will be merged in the future, and enable us to write LINQ queries that will be pushed-down to the database layer where it is possible.

Conclusion

The purpose of this article is to show how easily you can combine relational and JSON data in SQL database and use them in Entity Framework models. Now you can choose what is the best way to model both your database and your EF models, and define what properties should be mapped to table columns, and what properties should be formatted as JSON cells.

Integrations of relational and NoSQL concepts in database give you more freedom in Entity Framework models.

History

• 17^th January, 2017: Initial version