Karen Payne

Posted on Mar 25, 2023 • Edited on Mar 27, 2023

EF Core 7: Json columns

#dotnetcore #csharp #database

Note
Most relational databases support columns that contain JSON documents. The JSON in these columns can be drilled into with queries. This allows, for example, filtering and sorting by the elements of the documents, as well as projection of elements out of the documents into results. JSON columns allow relational databases to take on some of the characteristics of document databases, creating a useful hybrid between the two. -From Microsoft.

Important

Rather than use migrations the database was created in SSMS (SQL-Server Management Studio) then reverse engineered with EF Power Tools than changes properties that were for json to point to classes rather than nvarchar type.

If you are fairly new to EF Core than take time to download the sample code, study the code, run the code and understand the code.

When using json columns make sure it's the right fit for your data model rather than simply using it because its new.

Purpose of this article

To provide several clear and concise code samples for working with Json columns as many code samples on the web are not easy to try out.

Example 1

We have a Person table that will store one to many addresses for the person model.

public class Address
{
    public string Company { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public override string ToString() => Company;
}

Person model

public partial class Person
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime DateOfBirth { get; set; }
    public List<Address> Addresses { get; set; }
    public override string ToString() => $"{FirstName} {LastName}";
}

In the database the Address is defined as nvarchar.

To configure EF Core to recognize Json columns for the Address property of the Person model we use the following code where OwnsMany is the key, pointing to the Address property.

Let's add a new record to the database then modify the City property of one of the Addresses.

private static void AddOnePerson()
{
    using var context = new Context();

    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    Person person = new Person()
    {
        Addresses = new List<Address>()
        {
            new()
            {
                Company = "Company1", 
                City = "Wyndmoor", 
                Street = "123 Apple St"
            },
            new()
            {
                Company = "Company2", 
                City = "Portland", 
                Street = "999 34th St"
            },
        },
        FirstName = "Karen",
        LastName = "Payne",
        DateOfBirth = new DateTime(1956, 9, 24)
    };

    context.Add(person);
    context.SaveChanges();

    context.Person.FirstOrDefault()!
        .Addresses
        .FirstOrDefault()
        !.City = "Ambler";

    context.SaveChanges();

}

If you have been working with EF Core for a while the last code block is really no different than not using Json columns.

Let's read the data back.

private static void ReadOnePerson()
{
    using var context = new Context();
    var person = context.Person.FirstOrDefault();
    if (person is Person)
    {
        AnsiConsole.MarkupLine($"[white]{person.Id,-4}{person.FirstName,-10}{person.LastName,-10}{person.DateOfBirth:d}[/]");
        foreach (var address in person.Addresses)
        {
            AnsiConsole.MarkupLine($"\t[green]{address.Company,-10}{address.Street,-15}{address.City}[/]");
        }
    }

    var firstPerson = context.Person.FirstOrDefault(x => x.Id == 1);
    var portlandAddress = firstPerson!.Addresses.FirstOrDefault(x => x.City == "Portland");
    AnsiConsole.MarkupLine($"[white]{firstPerson.LastName,-8}{portlandAddress!.Company}[/]");

}

Suppose there are applications where a developer is not using EF Core for one reason or another, they can still work with this data but requires more work. Here is a read example.

internal class DataProviderOperations
{
    public static void ReadPersonAddress(int index = 0)
    {
        AnsiConsole.MarkupLine($"[cyan]Read data for address {index +1}[/]");
        var statement =
            "SELECT Id, FirstName, LastName, DateOfBirth, " + 
            $"JSON_VALUE(Addresses, '$[{index}].Street') AS Street, JSON_VALUE(Addresses, '$[{index}].City') AS City, JSON_VALUE(Addresses, '$[{index}].Company') AS Company FROM dbo.Person;";

        using SqlConnection cn = new(ConfigurationHelper.ConnectionString());
        using SqlCommand cmd = new() { Connection = cn, CommandText = statement };

        cn.Open();

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine($"{string.Join(", ", row.ItemArray)}");
        }

        Console.WriteLine();
        AnsiConsole.MarkupLine("[cyan]DataTable columns[/]");
        foreach (DataColumn column in dt.Columns)
        {
            Console.WriteLine($"{column.ColumnName,-15}{column.DataType.Name}");
        }
    }
}

Example 2

A developer found a copy of WCAG rules in a json file and would like to store the data in a SQL-Server database table using the following json.

{
    "Section": "1-2-1",
    "id": "media-equiv-av-only-alt",
    "title": "Audio-only and Video-only (Prerecorded)",
    "description": "For prerecorded audio-only and prerecorded video-only media, the following are true, except when the audio or video is a media alternative for text and is clearly labeled as such:",
    "uri": "http://www.w3.org/TR/WCAG20/#media-equiv-av-only-alt",
    "conformance_level": "A",
    "wuhcag_summary": "Provide an alternative to video-only and audio-only content",
    "wuhcag_detail": "\u003Cp\u003E\u003Cstrong\u003EProvide an alternative to video-only and audio-only content\u003C/strong\u003E\u003C/p\u003E\n\u003Cp\u003ESome users will find it difficult to use or understand things like podcasts and silent videos or animations.\u003C/p\u003E\n\u003Ch2\u003EWhat to do\u003C/h2\u003E\n\u003Cul\u003E\n  \u003Cli\u003EWrite text transcripts for any audio-only media;\u003C/li\u003E\n  \u003Cli\u003EWrite text transcripts for any video-only media; or\u003C/li\u003E\n  \u003Cli\u003ERecord an audio-track for any video-only media;\u003C/li\u003E\n  \u003Cli\u003EPlace the text transcript, or link to it, close to the media.\u003C/li\u003E\n\u003C/ul\u003E\n",
    "wuhcag_tips": "\u003Cp\u003EAudio-only and video-only content needs to be supported by text transcripts that convey the same information as the media. Sometimes this is quite simple, other times you have to make a judgement call on what that really means. The best bet is, as always,to be honest with your customers: what does the media convey and does your transcript do the same? Could you swap one for the other?\u003C/p\u003E\n\u003Cp\u003EOne of the most common uses for text transcripts is when a podcast is published online. Embedding a podcast in a page is a great way of sharing your content but no good for your customers with hearing impairments. A text transcript should contain everything mentioned in the recording.\u003C/p\u003E\n\u003Cp\u003ELess commonly, some videos do not have sound. Your customers with visual impairments need help with this kind of content. A text transcript for a video without sound should describe what is going on in the video as clearly as possible. Try to focus on\n  what the video is trying to say rather than getting bogged down with detail.\u003C/p\u003E\n\u003Cdiv class=\u0027mailmunch-forms-in-post-middle\u0027 style=\u0027display: none !important;\u0027\u003E\u003C/div\u003E\n\u003Cp\u003EAs an alternative for video-only content, you could also choose to record an audio track that narrates the video.\u003C/p\u003E\n\u003Cp\u003EFor both audio-only and video-only, create your text transcript and place it either directly beneath the content or insert a link next to the content.\u003C/p\u003E\n",
    "wuhcag_what_to_do": "",
    "wuhcag_exceptions": "\u003Cp\u003EIf the content is itself an alternative (you don\u2019t have to provide a transcript of the audio track you provided to explain the silent video you used).\u003C/p\u003E\n",
    "wuhcag_related": [
      {
        "Section": "1-2-2",
        "conformance_level": "A"
      },
      {
        "Section": "1-2-3",
        "conformance_level": "A"
      },
      {
        "Section": "1-2-5",
        "conformance_level": "AA"
      },
      {
        "Section": "1-2-7",
        "conformance_level": "AAA"
      },
      {
        "Section": "1-2-8",
        "conformance_level": "AAA"
      }
    ],
    "RelatedList": [
      {
        "Section": "\u00221-2-2\u0022",
        "ConformanceLevel": "\u0022A\u0022"
      },
      {
        "Section": "\u00221-2-3\u0022",
        "ConformanceLevel": "\u0022A\u0022"
      },
      {
        "Section": "\u00221-2-5\u0022",
        "ConformanceLevel": "\u0022AA\u0022"
      },
      {
        "Section": "\u00221-2-7\u0022",
        "ConformanceLevel": "\u0022AAA\u0022"
      },
      {
        "Section": "\u00221-2-8\u0022",
        "ConformanceLevel": "\u0022AAA\u0022"
      }
    ]
  }

Note that the above data was not so clean at first and took time to fix it up.

The RelatedList could had been placed into a separate table yet lets consider that the data is not going to change.

Model for RelatedList

public class Related
{
    public string Section { get; set; }
    public string ConformanceLevel { get; set; }
    public override string ToString() => $"{Section, -10}{ConformanceLevel}";
}

Here is the main class/model with attributes so we have well defined property names.

public partial class WebStandards
{
    public int Identifier { get; set; }

    public string Section { get; set; }

    [JsonPropertyName("id")]
    public string Id { get; set; }

    [JsonPropertyName("title")]
    public string Title { get; set; }

    [JsonPropertyName("description")]
    public string Description { get; set; }

    [JsonPropertyName("uri")]
    public string Uri { get; set; }

    [JsonPropertyName("conformance_level")]
    public string ConformanceLevel { get; set; }

    [JsonPropertyName("wuhcag_summary")]
    public string Summary { get; set; }

    [JsonPropertyName("wuhcag_detail")]
    public string Detail { get; set; }

    [JsonPropertyName("wuhcag_tips")]
    public string Tips { get; set; }

    [JsonPropertyName("wuhcag_what_to_do")]
    public string Remedy { get; set; }

    [JsonPropertyName("wuhcag_exceptions")]
    public string Exceptions { get; set; }

    public List<Related> RelatedList { get; set; }
}

The database table model

Configuration in the DbContext

Code to read json from a file

internal class JsonOperations
{
    private static string FileName => "wcagNew.json";
    public static List<WebStandards> Read()
    {
        var jsonString = File.ReadAllText(FileName);
        return JsonSerializer.Deserialize<List<WebStandards>>(jsonString);
    }
}

Code to add contents of the json file to our table and perform several queries.

internal class DataOperations
{

    /// <summary>
    /// Populate table from reading a json file
    /// </summary>
    /// <param name="list">Data from json</param>
    public static void AddRange(List<WebStandards> list)
    {
        using var context = new Context();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
        context.AddRange(list);
        Console.WriteLine(context.SaveChanges());
    }

    /// <summary>
    /// * Read data from database
    /// * Get all AA complaint items
    /// </summary>
    public static void Read()
    {
        using var context = new Context();
        var standards = context.WebStandards.ToList();

        foreach (var standard in standards)
        {
            Console.WriteLine($"{standard.Identifier,-5}{standard.Title}");
            // not all items have related items so assert for null list
            if (standard.RelatedList is not null)
            {
                foreach (var related in standard.RelatedList)
                {
                    Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
                }
            }
        }

        var aaStandards = standards.Where(x => x.ConformanceLevel == "AA");

        AnsiConsole.MarkupLine("[cyan]ConformanceLevel AA[/]");
        Console.WriteLine(aaStandards.Count());

        AnsiConsole.MarkupLine("[cyan]Keyboard traps[/]");
        var keyboardTraps = standards.FirstOrDefault(x => x.Title == "No Keyboard Trap");
        Console.WriteLine(keyboardTraps.Description);
        Console.WriteLine(keyboardTraps.Uri);

        foreach (var related in keyboardTraps.RelatedList)
        {
            Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
        }

        Console.WriteLine();
    }
}

Calling the above from a console project.

internal partial class Program
{
    static void Main(string[] args)
    {
        DataOperations.AddRange(JsonOperations.Read());

        DataOperations.Read();
        AnsiConsole.MarkupLine("[yellow]Done[/]");
        Console.ReadLine();
    }
}

Example 3

In the prior examples we used OwnMany, in this example we have a main model Applications setup with two json columns, one for mail information and one for general settings.

public partial class Applications
{
    public int ApplicationId { get; set; }

    /// <summary>
    /// Application identifier
    /// </summary>
    public string ApplicationName { get; set; }

    /// <summary>
    /// Contact name
    /// </summary>
    public string ContactName { get; set; }

    /// <summary>
    /// For sending email messages
    /// </summary>
    public MailSettings MailSettings { get; set; }
    public GeneralSettings GeneralSettings { get; set; }
}
public partial class GeneralSettings
{
    public required string ServicePath { get; set; }
    public required string MainDatabaseConnection { get; set; }
}
public partial class MailSettings
{
    public required string FromAddress { get; set; }
    public required string Host { get; set; }
    public required int? Port { get; set; }
    public required int? TimeOut { get; set; }
    public required string PickupFolder { get; set; }
}

Then in the DbContext

And finally code to populate and read back data.

namespace HybridTestProject
{
    /// <summary>
    /// Fast and dirty, not true test
    /// </summary>
    [TestClass]
    public partial class MainTest : TestBase
    {
        [TestMethod]
        [Ignore]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void AddRecordsTest()
        {
            using var context = new Context();
            Applications application1 = new()
            {
                ApplicationName = "ACED",
                ContactName = "Kim Jenkins",
                MailSettings = new MailSettings()
                {
                    FromAddress = "FromAddressAced", 
                    Host = "AcedHost", 
                    PickupFolder = "C:\\MailDrop", 
                    Port = 15, 
                    TimeOut = 2000
                },
                GeneralSettings = new GeneralSettings()
                {
                    ServicePath = "http://localhost:11111/api/",
                    MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithDate;Integrated Security=True;Encrypt=False"
                }
            };

            Applications application2 = new()
            {
                ApplicationName = "SIDES",
                ContactName = "Mike Adams",
                MailSettings = new MailSettings()
                {
                    FromAddress = "FromAddressSides",
                    Host = "SidesHost",
                    PickupFolder = "C:\\MailDrop",
                    Port = 15,
                    TimeOut = 2000
                },
                GeneralSettings = new GeneralSettings()
                {
                    ServicePath = "http://localhost:22222/api/",
                    MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithTime;Integrated Security=True;Encrypt=False"
                }
            };

            context.Add(application1);
            context.Add(application2);

            context.SaveChanges();

        }
        [TestMethod]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void SimpleReadTest()
        {
            using var context = new Context();
            var apps = context.Applications.ToList();

            foreach (var app in apps)
            {
                Console.WriteLine($"{app.ApplicationId,-4}{app.ApplicationName,-8}{app.MailSettings.Host}");
                Console.WriteLine($"    {app.GeneralSettings.MainDatabaseConnection}");
            }
        }

        [TestMethod]
        [TestTraits(Trait.EntityFrameworkCore)]
        public void ReadOneTest()
        {
            using var context = new Context();

            var app = context.Applications.FirstOrDefault(x => 
                x.MailSettings.FromAddress == "FromAddressSides");

            Assert.IsNotNull(app);

        }
    }
}

Summary

The Microsoft EF Core team made it easy to get started with working with json in a SQL-Server database. They will be refining what is possible with json columns in the next version of EF Core, EF Core 8.