Click here to Skip to main content
13,192,207 members (36,045 online)
Click here to Skip to main content
Add your own
alternative version

Stats

23K views
175 downloads
23 bookmarked
Posted 17 Jan 2017

Entity Framework: Storing complex properties as JSON text in database

, 18 Jan 2017
Rate this:
Please Sign up or sign in to vote.
Complex properties in entity Framework models such as arrays, dictionaries, and objects can be serialized in SQL Server database in JSON format. In this article I will show you how to map your properties in EF model to database columns that contain JSON.

Background

Entity Framework enables you to map you 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, lets 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 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 that 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.

Intro

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, lets 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 mapping s 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 an utility class that is used to define properties in Owner property, it is not mapped to database table. This table will be serializes/de-serializes 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 you 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 APS.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 are 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 parser 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 might 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 are space for further improvements in Entity Framework model. I will mention two things that are 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 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, have in something as [Serialization(JSON)] attribute on a field would be 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:
_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:

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.

  1. 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.
_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:

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 chose 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 gives you more freedom in Entity Framework models. You can find a source code from the article here: Download EF-JSON.zip

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jovan Popovic
Program Manager Microsoft
Serbia Serbia
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

You may also be interested in...

Pro

Comments and Discussions

 
QuestionEF6? Pin
Member 1236169429-May-17 4:00
memberMember 1236169429-May-17 4:00 
QuestionPossible to add a project solution? Pin
HotFrost19-May-17 7:33
memberHotFrost19-May-17 7:33 
First of all, thanks for writing this article. It helps to understand better how to work in JSON and EF.

The problem is.. I am not able to make it work by following the code described in this article.
The main issue is that EF says I can't filter on the properties that are not mapped.

May be I am missing something.. for this.. I wonder if it is possible to include the working project in this article?

thank you very much,
Alexey
QuestionI can't see the article!!! Pin
girlprogrammer16-Mar-17 2:38
membergirlprogrammer16-Mar-17 2:38 
PraiseGreat! Pin
Jo9021-Jan-17 11:55
memberJo9021-Jan-17 11:55 
QuestionHot topic Pin
zpaulo_carraca19-Jan-17 23:34
memberzpaulo_carraca19-Jan-17 23:34 
PraiseAwesome Pin
Ankit Rana18-Jan-17 22:32
memberAnkit Rana18-Jan-17 22:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171017.2 | Last Updated 18 Jan 2017
Article Copyright 2017 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid