Some developers used EF Core JSON Columns in their DbContext when using my EfCore.SchemaCompare library, then they had a problem. That’s because I hadn’t added support for JSON Columns to my EfCore.SchemaCompare library (this library compares a EF Core’s Model of the database against a database’s schema). When I added support for JSON Columns feature to the library, I found it had a rather complex internal design. In fact Shay Rojansky talks about this complex design in the EF Core 9 release EF Core community standup (click this to hear what he says) and they want to improve it in EF Core 10.
So the combination of a complex design, not much documentation, and my dementia (See the ENDNOTE dementia and programming section at the end of this article) made it pretty hard for me to work out what to do. Thankfully the EF Core team responded to my issue, which helped me a lot.
So, I decided to create this article to provide more documentation to help other developers when using JSON Columns. The article compares the EF Core normal approach of using SQL Tables, columns, Indexes, etc., (shortened to “Tables/Indexes approach” in this article) against the JSON Columns approach so that you can compare / contrast the two approaches.
TL;DR; – Summary of this article
- EF Core added a feature called JSON Columns in .NET 7, which stores data in an JSON string, and was also updated in EF Core’s .NET 8 release.
- This article compares the JSON Columns approach against the normal Tables/Indexes approach.
- I use a “list of books” app using both the JSON Columns approach and the Tables/Indexes approach
- The way relationships are configured and stored are different – The JSON Columns’ relationships are held inside the its JSON string, while the the Tables/Indexes approach has multiple tables, linked by Indexes.
- The create and read of both approaches are (nearly) the same, while selecting particular entries is different in the two approaches.
- There is a large section looking at the performance of eight tasks with different numbers of the entities to give you an idea where JSON Columns would work in your application.
- At the end I give you my, and EF Core’s, view of what sort of data that works with JSON Columns: The data must be “Coherent”, i.e. there no links outside the data. Logging data is one example where JSON Columns would work well.
Introduction of storing data in a database using JSON
JSON (Full name: JavaScript Object Notation) is a lightweight format for storing and transporting data in strings. The JSON’s format has a beautifully simple design, which makes it easy to understand and use. JSON is used everywhere, for instance Web Applications to send data from the server to the client and to send back inputs from the client.
I also used JSON in my early use of EF Core by using the JsonSerializer.Serialize and JsonSerializer.Deserialize methods to store data in a database. But in the .NET 7 release of EF Core added dedicated code to convert data into a JSON string (this is known as “JSON Columns” in EF Core). In .NET 8 release of EF Core to improve its performance and added JSON Columns to SQLite. And Shay Rojansky, who is part of the EF Core team, said they will improve how JSON Columns in .NET 10 EF Core.
The JSON Columns feature allows you to put some code in your EF Core’s DbContext’s OnModelCreating method to automatically convert a class’s properties into a JSON string. It also allows multiple classes, including relationships, to be stored in one JSON string.
The .NET 7 what’s new JSON Columns section says “JSON columns allow relational databases to take on some of the characteristics of document databases, creating a useful hybrid between the two.“ JSON Columns can contain any basic types: e.g., string, int, bool, DateTime and relationships, although JSON Columns’ relationships are handed differently to the normal Tables / Index approach.
Creating an example application
I wanted an example application that to covers relationships, configured, read/write and performance. I also I wanted an application that both the Tables/Indexes approach and JSON Columns approach can use. I choose a “list of books” app (think a very simple Amazon book app).
I have to say that the “list of books” app is NOT something that I would build using JSON Columns, but it does show all the JSON Columns’ relationships. Also, the “list of books” app provides a good way to compare the JSON Columns’ performance against the Tables/Indexes approach.
NOTE: I used a “list of books” app in my Entity Framework Core in Action (2nd. Edition). In chapter 2 I used the “list of books” app to show the main types of EF Core’s relations, and in chapter 15 looked at performance and chapter 16 using Cosmos DB.
I created a GitHub called ExploringJsonColumns which holds the code to check that the code was correct, and also to run performance tests to compare the two approaches. The sections in this article are:
- An overview of the “list of books” data
- The relationships and how they are configured
- Writing into the database
- Selecting data from the database
- Reading data from the database
- Performance comparisons
- My thoughts on where JSON Columns are a good fit
1. An overview of the “list of books” data
My ExploringJsonColumns solution holds two versions of the “list of books” app code: one using the normal Tables/Indexes approach and the other the JSON Columns approach, and provides the same output into common class called BookListDto (The BookListDto class contains all the data to show a book’s details, e.g., book title, author(s), price, star ratings, etc.. This ensures that we can compare their performances the results because both approaches are the same data. The screenshot below shows one (fictional) book showing the book’s data.
2. The relationships and how they are configured
The “list of books” app has the three types of relationships, and the diagram below shows the Tables/Indexes approach on the left and the JSON Columns approach on the right.
NOTE: click on the picture below to get a bigger picture.

| See Tables/Indexes classes in this folder. | See JSON Columns classes in this folder. |
The JSON Columns part of the diagram above isn’t as clear as the Tables/Indexes approach, so the screenshot below shows the JSON in the BookData’s nvarchar(max) string.

The configuring of JSON Columns is added in your DbContext’s OnModelCreating method – see the diagram below which shows the BookJsonContext class with comments about where the JSON Columns’ OwnsOne and OwnsMany methods are used

Here is a link to another the JsonCustomerContext DbContext which I used in testing my JSON Columns code added to my EfCore.SchemaCompare library. This shows how you can configure multiple layers (e.g., grandfather, father, son) relationships and handing multiples JSON Columns’ entities.
The differences between Tables/Indexes and JSON Columns version’s “many” relationships
When showing a book with multiple authors you must ensure the authors’ names are in the correct order. In the Tables/Indexes approach: the BookAuthor class has an byte “Order” column to make sure that Author’s Names are shown in the correct order.
But in the JSON Columns approach the JsonAuthor class doesn’t need a “Order” property because the List<JsonAuthor> holds the authors’ names in the order they were added. Therefore you don’t need a “Order” property.
4. Writing into the database
From the developer’s perspective the writing of data to the database uses the same process for the Table / Index approach and JSON Columns – you put data into a class and EF Core will store it into the database. EF Core will store Table / Index data into tables and columns, while with JSON Columns EF Core will store the data in a JSON string.
5. Selecting data from the database
In the first version (EF Core 7) of the JSON Columns the only way to select data is using the Table / Index approach, i.e. by using column(s) to in the top entity, for instance in the “lists of books” you could its Id (Index) in the BookTop class.
But in EF Core 8 there was a new Enhancements to JSON column mapping” feature which enables you to filter each entry (i.e. book) using data point(s) in the JSON string. I use this feature in my performance tasks to find all the books that has a specific author’s name. You can find that code here.
6. Reading data from the database
The reading of JSON Columns’ data is very easy – you simply access the classes / properties just like the Table / Index approach. The only small difference is that any relationship in the JSON Columns is immediately available, so you don’t need to add code to load relationships (e.g., context.Books.Include(book => book.Reviews)… isn’t needed).
NOTE: There are some rare situations when you need to a) read in many JSON Columns entities, and b) combine data, or you only need a subset of the data points, then using IEnumerable<> input and an IEnumerable<> out can make the process (a bit) quicker (see MapJsonBooks for an example). I found this when reading all the books ordered by each book’s star rating, because I had two ToList’s – using IEnumerable<> in/out removes one of the ToList method.
4. Performance comparisons
I had a project called ConsoleBenchmark in my ExploringJsonColumns solution which used BenchmarkDotNet to get the performance of the two approaches, i.e., the normal Tables/Indexes approach and the JSON Columns approach. There are four tasks to compare, with two types of data:
- Simple data: This has the least relationships, with each book having one Author’s Name and no reviews or promotions.
- Complex data: This has many relationships, with each book having two Author’s Name and many reviews and a few promotions.
| Task name | What is does |
| 1. Add | Add N sample data to an empty SQL database |
| 2. Read | Read N sample data from the SQL database |
| 3. Order | Read N sample data and order the books using their star rating |
| 4. Author | Find all the books that has a specific author’s name in the N sample data. |
The first two tasks (Add, Read) don’t favour either approach, but last two tasks (Order, Author) should favour the Tables/Indexes approach because it can use indexes to get to the information more easily. NOTE: that the “Order” and “Author” tasks don’t work in the Simple type.
Below are the benchmark results for the two setups: Simple and Complex, with a performance summary after the BenchmarkDotNet’s results.


From the performance of this “list of books” code of you can say:
- Add tests: The JSON Columns is as good, or better than the Table / Index version, and the 1,000 books with JSON Columns is nearly half the time of the Table / Index code. I suspect the number of relationships will have some effect on the Table / Index version’s performance.
- Read tests: The JSON Columns performance is near to the Table / Index version when there aren’t many books, i.e., 10, but once you get to 100 books takes 8 times longer, and with 1,000 test Columns takes 10 times longer.
- Order tests (complex only): I thought the Table / Index version would win every time because it could use the LINQ Average method on all the review’s star rating, but in the “ten books” JSON Columns works well. However, compared against the Table / Index version the 100 books JSON Columns take 7 times longer, and 1,000 JSON Columns takes 7 times longer.
- Author tests (complex only): This task finds all the books that have a specific author’s name. The JSON Columns approach was better than I expected, but it’s always slower than the Table/Index approach. The worse was 1,000 JSON Columns version, which was 8 times slower that the Table/Index approach.
5. My thoughts on where JSON Columns are a good fit
I used the “list of books” code because it covers the JSON Columns’ relationships, configuration, types of reading, etc. which makes a good example. As I said earlier, I was sure that the normal Table / Index approach would have the best performance on the “list of books” tests, and I was right. But I was surprised that the JSON Columns did so well!
So, what types of data is a good fit for using JSON Columns? My take is:
- The data has many parts.
- Each data point can be held in a built-in type, e.g. int, bool, string…
- The data points are “coherent”, i.e. there no links outside this data.
This means the JSON Columns approach is good if you have lots of data about one thing that doesn’t need links to another entity. The .NET 7 release the EF Core documentation gives you examples that work well with JSON Columns, for instance:
- Contact Details: name, phones, address, etc.
- Logging: what, where, when, who…
The other thing to consider is the performance comparison where the JSON Columns approach is better that the Table / Index approach. The Performance comparisons section show that:
- Adding a lot of JSON Columns data is normally faster than the Table / Index approach (I think this because the Table / Index approach of the “”list of books” app has to create more indexing and relationships on adding).
- Do you need to read a lot of entities using JSON Columns, then a JSON Columns can be slow. For instance, “list of books”:
- JSON Columns: 10 takes the same as the Table / Index approach, but…
- JSON Columns: 100 takes 2 times of the Table / Index approach
- JSON Columns: 1,000 takes 8 times of the Table / Index approach
- If there are lookups (e.g., finding all the books for a specific Author) or ordering, then the Table / Index approach, with its links using Indexing, is likely to perform better that JSON Columns approach. For instance, in the Author task the JSON Columns approach has read every AuthorName in each entity, while the Table / Index approach only has to look at the at the links via the BookAuthor class, which are 10.
Conclusion
I hope this article helps you to understand how to setup and use EF Core’s JSON Columns feature. Also the performance figures give you some idea of what JSON Columns will do in add, read, and filter tasks.
Just to end, if your data fits a JSON Columns approach, but performance is critical, then I would recommend Cosmos DB. I used Cosmos DB with the CQRS pattern in chapter 16 of my book Entity Framework Core in Action (2nd. Edition) and I found it very fast. For instance, the Order task in this article was way faster than the normal Tables / Indexes approach.
ENDNOTE dementia and programming
I wrote an article in the end of May 2024 which had an endnote about my dementia, talking about my diagnosis of Alzheimer’s disease in January 2024. Alzheimer’s disease is a progressive disease that worsens over time; this endnote talks about how I am in January 2025.
I am very happy to say my programming skills are better! and that’s what this endnote talks abouts. However I have to say that there are a few negative symptoms as well, like something I call “sensory overload,” where if I do lots of different tasks one after another (or lots of people or sounds), then I feel overloaded. But I’m going to focus on how my programming skills got better, because developers will be reading this.
In my first endnote about my dementia I talked about using lists more to cover the problem of memory loss, and using my version Cognitive Stimulation Therapy (CST) approach. The typical CST ‘s suggestions are to do crossword puzzles, Sudoku, Rubik’s Cube, etc. I do some of these but for me the best CST work is programming! I had to change my approach to programming to counter the dementia’s symptoms – and for me it works.
It turns out in the last three months I have done a lot of programming:
- Added JSON Columns to my EfCore.SchemaCompare (very difficult!)
- Fixed a tricky problem my EfCore.SchemaCompare (complex)
- I updated five these libraries to .NET 9 (mostly easy)
- The ExploringJsonColumns for this article (complex)
Doing all this has helps me to regain some of my programming skills!, like my “holding all of parts of the code in my head” skill back (I talked about this in first endnote). This skill gave a detailed understanding of design of any projects I was working on. This allows me to think about additions or improvements “in my head”, often after work without the code in front of me. I though everyone could do this, but when I lost this gift, I was devastated, but I also realised that my “in my head” skill might be rare. But now I have seen that I can get back my “in my head” skill, it makes it easier to do programming!
However dementia effects many parts of the brain, and the big ones for me are remembering words and spelling. Here are two that I have, with examples from my programming:
- Correct name: For instance, when coding I usually know that I can do a task, but I can’t remember the name of the method I need. For instance, I knew I could select a subset of a list, but didn’t remember its name (it turns out is the LINQ’s Take method). This happened many times on my small ExploringJsonColumns project.
- Correct spelling: The coding is bad, but the real problem is to remember how to spell a word, even when using Word to help me! I can’t put a number of the times I had to type a word, and it wasn’t correct, then I have to try different spelling or look up on the web (I found that Google Search is better that Word when figuring out what my incorrect version of a word should be).
The other things I do when coding are:
- Unit Tests: Indispensable : I have always used unit tests, but now I have dementia I need it more!
- Task list: Now used: Before dementia I could handle many things in my head. Now I must create a task list to help me to not miss something. For this article I wrote the section names first, and then created the code for each section.
- Use two monitors: If I am changing some code it’s useful to remember what the original code was, but my dementia removes the ability to remember things (short-term memory). Therefore I often have the original code on my second monitor while I updating the code using my main monitor.
END























