Json Columns in Entityframework (Mssql json with C#)

Json column feature is already supported in Mssql but only supports in Entityframework 7 and above for the C# capability. We can directly access the JsonColumn of SQL from Entityframework without serializing and deserializing the data and have the ability to query the JSON string with the Linq.

We can easily achieve this feature in MS SQL by setting the data type to nvarchar (MAX) and saving the data in the JSON structure as in below.

[
  {
    "Day": "Monday",
    "From": "2023-10-10T14:17:43.5301482+06:30",
    "ID": "32a4945726684fa191f1113a8ca722aa",
    "Is24Hrs": null,
    "To": "2023-10-10T15:17:43.5303055+06:30"
  },
  {
    "Day": "Tuesday",
    "From": "2023-10-10T14:17:43.53037+06:30",
    "ID": "6cef6b5fc4cb4cbebf21d777cdb046d7",
    "Is24Hrs": true,
    "To": "2023-10-10T15:17:43.5303703+06:30"
  }
]

So let’s see how we can obtain the JSON capability in C# by using ef7.

Here is the example model

public class tbPlace
{
public int ID {get;set;}
public string Name {get;set;}
public List<Schedule> OpeningHour {get;set;}//Jsoncolumn nvarchar(max)format in mssql
}

public class Schedule
{
public string Day {get;set;}
public datetime? From {get;set;}
public datetime? To {get;set;}
public bool? Is24Hrs {get;set;
}

In the above model OpeningHour from tbPlace should be in string format. But with the help of the ef7 we can use List<Schedule> and to make it available we need to make some setup in DBContext OnModelCreating Method.

public class EgDbContext:DbContext
{
    public EgDbContext(DbContextOptions<EgDbContext> options) : base(options)
    {
    }

    public virtual DbSet<tbPlaces> tbPlace{ get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<tbPlace>().OwnsMany(
            p => p.OpeningHour, option =>
            {
                option.ToJson();
            });
          });
    }
}

Now entityframework will handle the OpeningHour string and transform to List<Schedule> as we use OwnsMany in the modelBuilder. If we use OwnsOne then it will be transform into the single object Schedule. Now We can start the testing of saving and querying data with the same code we used to make CRUD before.