Add MS SQL Server JSON Support To Entity Framework Core | Khalid Abuhakmeh_Code (2023)

While other database providers have made their place known within the .NET ecosystem, Microsoft’s SQL Server (MSSQL) andAzure flavor are still likely the most popular choice for .NET developers. The engine’s popularity is because Microsoft’s SQL Server has long been a reliable choice with Microsoft support and a large tooling ecosystem, making it a safeoption for developers and database administrators. As a result, there are few surprises when choosing MSSQL, but don’tconfuse that with being “boring”.

One of MSSQL’s hidden gems is its ability to allow you to store and query JSON data in existing tables. Storinginformation as JSON can help reduce interaction complexity between your application and SQL, with storage objectstypically a combination of identifier, sorting columns, and then a JSON column. JSON also lets you be more flexibleabout what you store, allowing you to store dynamic data that isn’t easy with relational database schemas.

.NET Developers also utilize Entity Framework Core to access their database, allowing the library to generate SQL andmap data into C# objects.

In this post, you’ll see how to modify Entity Framework Core to support SQL Server’s JSON_VALUE and JSON_QUERY inyour LINQ queries.


In MSSQL, you store JSON as nvarchar(max) or some variation of nvarchar , but storage is only half of the JSON story.What about querying your JSON or retrieving the data from the text column? While JSON is typically structured, it isn’ta rigid structure like you’d expect from a relational table. Your JSON data can have fields that are scalar values,arrays, or nested objects. With JSON’s infinite possibilities, you need mechanisms to access the data appropriately, andthat’s when you use JSON_VALUE and JSON_QUERY .

JSON_VALUE allows you to extract a scalar value (think numbers, strings values, etc.) from an existing JSON column.You can use the database function to add JSON values to a current SQL query.

Our JSON column might store a JSON object like the following example.

"info" : { "type" : 1 , "address" : { "town" : "Bristol" , "county" : "Avon" , "country" : "England" "tags" : [ "Sport" , "Water polo" "type" : "Basic"

You can query the town value from the JSON column with JSON_VALUE .

SELECT FirstName, LastName, JSON_VALUE(jsonInfo, '$') AS TownFROM Person.PersonWHERE JSON_VALUE(jsonInfo, '$.info.address.state') LIKE 'US%'ORDER BY JSON_VALUE(jsonInfo, '$')

Note that Town is a string value, not a complex object. If you need complex objects or to access arrays, you’ll needto use JSON_QUERY.

JSON_QUERY allows you to return a complex JSON structure so your consuming client can process that complexity,typically in the form of deserialization. In our previous JSON sample, you’ll note that tags is an array. Let’s seewhat the results of querying that value would look like in our results.

DECLARE @jsonInfo NVARCHAR(MAX)SET @jsonInfo=N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" "tags":["Sport", "Water polo"] "type":"Basic" Select JSON_QUERY(@jsonInfo, '$.info.tags') as Tags

The result of our query is a JSON Array.

["Sport", "Water polo"]

You typically won’t see JSON_QUERY in your Where clauses unless it’s matched with other functions to get to aprecise set of values.

Select JSON_QUERY(@jsonInfo, '$.info.tags')Where JSON_QUERY(@jsonInfo, '$.info.tags') like '%Sport%'

Now that you know how JSON_VALUE and JSON_QUERY work, how do we get access to this MSSQL from Entity Framework Core?

Entity Framework Core Configuration With JSON Columns

I’ve previously written about using DbFunctions with Entity Framework Core, so I suggest reading that at some point,but for brevity, I’ll show you want my OnModelCreating method looks like and what additional static methods you’llneed.

In your DbContext implementation, Add the following lines of code, or modify your OnModelCreating method to includethe additional configuration lines.

protected override void OnModelCreating(ModelBuilder modelBuilder) modelBuilder.HasDbFunction( typeof(Database).GetMethod(nameof(JsonValue))! ).HasName("JSON_VALUE").IsBuiltIn(); modelBuilder.HasDbFunction( typeof(Database).GetMethod(nameof(JsonQuery))! ).HasName("JSON_QUERY").IsBuiltIn();public static string JsonValue(string column, [NotParameterized] string path) => throw new NotSupportedException();public static string JsonQuery(string column, [NotParameterized] string path) => throw new NotSupportedException();

You must use the IsBuiltIn method, as you tell EF Core that this database function is not part of any existing schemalike dbo. The static methods will allow you to use C# methods as markers for EF Core’s translation process. We don’tneed any implementation in our methods, but you could certainly add validation logic that runs before the translationprocess occurs. I don’t see the need to do so, but you are welcome to modify it yourself.

Let’s see what writing a LINQ query is like using the JSON_VALUE method. But, first, let’s get an idea of our datamodels used in the query.

public class Product public int Id { get; set; } // serialized ProductInfo public string Json { get; set; } = "{ }";public class ProductInfo public string Name { get; set; } = ""; public decimal Price { get; set; }

Now, what’s a LINQ Query going to look like in your codebase?

using Microsoft.EntityFrameworkCore;// project namespaceusing SqlServerJson;using static SqlServerJson.Database;using PI = SqlServerJson.ProductInfo;var database = new Database().TrySeed();var expensive = database.Products .Select(p => new { p.Id, Name = JsonValue(p.Json, $"$.{nameof(PI.Name)}"), Price = Convert.ToDecimal(JsonValue(p.Json, $"$.{nameof(PI.Price)}")) .Where(x => x.Price > 800) .OrderByDescending(x => x.Price) .Take(10);Console.WriteLine(expensive.ToQueryString() + "\n");foreach (var product in expensive) Console.WriteLine($"{product.Name} ({product.Price:C})");

Note that the JsonValue method takes the string property on our model, and not a string of the column name. The EF Core translator will use the property name when converting LINQ to our SQL Query. An essential part of the query is the usage of Convert. EF Core’s query syntax can translate the Convert call intothe appropriate SQL cast.

You’ll see the SQL query and the results running the sample. **Your results will depend on the data in your databaseinstance.

DECLARE @__p_0 int = 10;SELECT TOP(@__p_0) [p].[Id], JSON_VALUE([p].[Json], N'$.Name') AS [Name], CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) AS [Price]FROM [Products] AS [p]WHERE CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) > 800.0ORDER BY CONVERT(decimal(18, 2), JSON_VALUE([p].[Json], N'$.Price')) DESCSleek Steel Table ($999.74)Ergonomic Fresh Cheese ($999.73)Awesome Frozen Gloves ($999.62)Incredible Concrete Mouse ($998.12)Intelligent Plastic Computer ($997.78)Generic Steel Car ($996.51)Intelligent Fresh Keyboard ($995.12)Sleek Granite Hat ($994.15)Rustic Rubber Ball ($992.98)Generic Soft Cheese ($992.04)

As you’ll notice, the SQL query is quite readable, and you can convert a JSON value into any database type for use infiltering and ordering your results. Awesome!

I decided to use a projection, but if you’d prefer to pull back the complete JSON object as part of your model, you canuse the following approach.

public class Product public int Id { get; set; } // serialized ProductInfo public string Json { get; set; } = "{ }"; [NotMapped] public ProductInfo? Info get => JsonSerializer.Deserialize<ProductInfo>(Json); set => Json = value is {} ? JsonSerializer.Serialize(value) : "{}";

Note, you will need to create a new ProductInfo every time and call the set on your property to take advantage ofserialization.

var newProduct = new Product { Info = new ProductInfo { Name = "Banana", Price = 3m }// correctnewProduct.Info = new() { Name = "Banana", Price = 4m };// incorrect (won't serialize when setting the value)newProduct.Info.Price = 4m;

You also won’t be able to use the ProductInfo property in your queries if you use NotMappedAttribute.

👋 To see a complete sample of this blog post, head to my GitHub repository.

Conclusion and Thoughts about JSON with SQL Server

While many folks use Entity Framework Core with SQL Server, I suspect many don’t use its JSON capabilities due to thelack of first-class support. But, as you’ve seen in this post, it only takes a handful of lines to get JSON querysupport added to Entity Framework Core. Additionally, future versions of Entity Framework Core will likely support JSONwithout the additional need for configuration. Suppose you’re struggling with sprawling schemas that include manyarray-like tables. In that case, this may be a better approach to reduce database size, query joins complexity, andnetwork IO. I recommend you give it a try, and let me know your thoughts.

Thank you for reading my posts and sharing them with your colleagues. If you have any thoughts or questions, follow meon Twitter at @buhakmeh.


How to connect to SQL Server using Entity Framework Core? ›

In this article
  1. Prerequisites.
  2. Configure the database server.
  3. Create the project.
  4. Add Entity Framework Core to the project.
  5. Add the code to connect to Azure SQL Database.
  6. Run the migrations to create the database.
  7. Test the app locally.
  8. Deploy to Azure App Service.
5 days ago

How to insert JSON object in SQL Server? ›

JSON data import in SQL Server
  1. Step 1: Import file using OPENROWSET. The first step is to load the JSON file content in a table. ...
  2. Step 2: Convert JSON output from the variable into SQL Server tables. We use OPENJSON() function for converting the JSON output from a variable into a tabular format.
Jan 17, 2020

How to install Microsoft EntityFrameworkCore in VS code? ›

Visual Studio NuGet Package Manager Dialog
  1. From the Visual Studio menu, select Project > Manage NuGet Packages.
  2. Click on the Browse or the Updates tab.
  3. To install or update the SQL Server provider, select the Microsoft. EntityFrameworkCore. SqlServer package, and confirm.
Mar 23, 2023

How to insert JSON data in SQL Server using stored procedure? ›

SQL Server Stored Procedure
  1. JSON text must use the NVARCHAR(MAX) data type in SQL Server in order to support the JSON functions.
  2. The OPENJSON function is used to convert the JSON text into a rowset, which is then inserted into the previously created table.
Mar 21, 2018

How to connect to SQL Server database in Entity Framework? ›

Select ADO.NET Entity Data Model

I am using SQL Server authentication. Thus, we need to provide the user name and password-> Select your database-> Test Connection-> OK. It includes the sensitive data in the connection string->next->Choose version of Entity Framework. Include database objects to your model-> Finish.

How to connect .NET core API to SQL Server? ›

How to build an ASP.NET Web API with Entity Framework and retrieve data from an SQL server
  1. Create a Blank Web API project.
  2. Select Web API in the template selection window.
  3. Create a table and Insert data to the database table.
  4. Add a web service.
  5. Add an ADO.NET Entity Data Model.
  6. Create a new connection with SQL server.

How to use JSON query in SQL Server? ›

SQL Server provides the following JSON functions to work with JSON Data:
  1. ISJSON(): we can check valid JSON using this function.
  2. JSON_VALUE(): It extracts a scalar value from the JSON data.
  3. JSON_MODIFY(): It modifies values in the JSON Data. ...
  4. JSON_QUERY: It extracts an array or string from JSON in SQL Server.
May 19, 2020

How to connect SQL with JSON? ›

By using SQL Server built-in functions and operators, you can do the following things with JSON text:
  1. Parse JSON text and read or modify values.
  2. Transform arrays of JSON objects into table format.
  3. Run any Transact-SQL query on the converted JSON objects.
  4. Format the results of Transact-SQL queries in JSON format.
Mar 30, 2023

How to add JSON object to database? ›

Click the Add button and select Column. On the Column element, specify values for the Index and Value attributes. Click the Add button in the sub-menu and select Add Same. Repeat the last two steps to add additional columns and elements from the JSON file.

How do I import Microsoft EntityFrameworkCore? ›

Visual Studio Package Manager
  1. Go to Tools » NuGet Package Manager » Manage NuGet Packages For Solution.
  2. Ensure that Browse is selected and type "entityframeworkcore" into the search box.
  3. Click on the provider that you want to install. ...
  4. Check the project that you want to install the package into, then click Install.
Feb 20, 2023

What is the use of Microsoft EntityFrameworkCore? ›

Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology. EF Core can serve as an object-relational mapper (O/RM), which: Enables .NET developers to work with a database using .NET objects.

Where to run scaffold DbContext command? ›

It can be performed using the Scaffold-DbContext command of the EF Core Package Manager Console (PMC) tools or the dotnet ef dbcontext scaffold command of the . NET Command-line Interface (CLI) tools.

How to connect SQL database in .NET core? ›

How to Connect to MySQL from . NET Core
  1. Install MySqlConnector. First, install the MySqlConnector NuGet package. ...
  2. Connection String. A typical connection string for MySQL is: Server=YOURSERVER;User ID=YOURUSERID;Password=YOURPASSWORD;Database=YOURDATABASE. ...
  3. Configure Service (ASP.NET Core) ...
  4. Open and Use the Connection.

How to call SQL function in Entity Framework Core? ›

Let's say you have a SQL function.
  1. CREATE FUNCTION dbo.IsStrogestAvenger(@Superhero varchar(100)) RETURNS bit AS BEGIN declare @result bit=0 if(@Superhero ='Thor') set @result=1 else set @result=0 RETURN @result END. ...
  2. public class IsStrongestAvengerResult { public bool IsStrongestAvenger { get; set; } }
Sep 30, 2020

How to connect code using Entity Framework? ›

In this tutorial, you:
  1. Create an MVC web app.
  2. Set up the site style.
  3. Install Entity Framework 6.
  4. Create the data model.
  5. Create the database context.
  6. Initialize DB with test data.
  7. Set up EF 6 to use LocalDB.
  8. Create controller and views.
Sep 29, 2022

Which is used for Entity Framework Core with SQL Server? ›

Entity Framework Extensions EF Core - SQL Server Provider
  • It is the default database provider which is available when you install Entity Framework Extensions.
  • It allows Entity Framework Core to be used with Microsoft SQL Server (including SQL Azure).
Feb 27, 2023

How to connect to SQL Server database from command line? ›

Open a Command Prompt window, and type sqlcmd -SmyServer\instanceName. Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to. Press ENTER. The sqlcmd prompt (1>) indicates that you are connected to the specified instance of SQL Server.

How do I add Connection String to .NET core? ›

json file, right click on the Project in Solution Explorer. Then click Add, then New Item and then choose App Settings File option (shown below) and click Add button. Once the File is created, it will have a DefaultConnection, below that a new Connection String with SQL Server Authentication is added.

How to connect to SQL Server from C# console application? ›

We can use the following code, to connect to the SQL Database,
  1. using(SqlConnection conn = new SqlConnection()) { conn. ...
  2. using (SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=Northwind")) { connection. ...
  3. SqlConnection conn = new SqlConnection(); conn.
Sep 30, 2014


Top Articles
Latest Posts
Article information

Author: Barbera Armstrong

Last Updated: 11/11/2023

Views: 6068

Rating: 4.9 / 5 (59 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Barbera Armstrong

Birthday: 1992-09-12

Address: Suite 993 99852 Daugherty Causeway, Ritchiehaven, VT 49630

Phone: +5026838435397

Job: National Engineer

Hobby: Listening to music, Board games, Photography, Ice skating, LARPing, Kite flying, Rugby

Introduction: My name is Barbera Armstrong, I am a lovely, delightful, cooperative, funny, enchanting, vivacious, tender person who loves writing and wants to share my knowledge and understanding with you.