Entity Framework: Storing complex properties as JSON text in database
Jovan Popovic (MSFT) - 18 Jan 2017
Jovan Popovic (MSFT) - 18 Jan 2017
[SHOWTOGROUPS=4,20]
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:
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:
Database setup
First, lets look at the table schema of Blogs table:
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:
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:
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:
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.
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:
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:
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.
[/SHOWTOGROUPS]
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:
- Added string[] Tags property in EF model that represents tags associated to the blog. Tags are stored in database as json array od string.
- 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
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":"[email protected]"}'),
('http://blogs.msdn.com/webdev', '[".Net", "Core", "ASP.NET"]','{"Name":"Jane","Surname":"Doe","Email":"[email protected]"}'),
('http://blogs.msdn.com/visualstudio', '[".Net", "VS"]','{"Name":"Jack","Surname":"Doe","Email":"[email protected]"}');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.
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.
[/SHOWTOGROUPS]
