I was recently reading updates in the upcoming .NET 10 release. In the changes to Entity Framework, I saw a section labeled 'JSON type support'. This really intrigued me - I've worked with JSON-based databases and enjoyed the flexibility of storing and working with unstructured data. But I always come back to SQL because it fits most of my needs.

Refer to this article for the official comments from Microsoft: Whats New in EF Core 10

The article from Microsoft explains that there has been JSON support in SQL for several versions, but that the JSON was simply stored as plain text. Now with EF 10, we map complex objects as JSON, and then still query them. I had to try it out. So I dove into some code! This example is with SQLite; I wasn't sure it would work with SQLite but it worked! I'll go over my process.

Source Code at GitHub

1. I created a new console application with two C# files. Program.cs (obviously) and DBContext.cs.
2. Let's look at the DBContext.cs. I created two entities: a "Campground" and a "Site". A campground is made up of many sites. I stored the campground as a traditional entity/table. And I stored the site as JSON data in a column of the campground.

    public class Campground
    {
        public string Id { get; set; } = Guid.NewGuid().ToString();
        public string Name { get; set; } = string.Empty;
        public DateOnly SeasonStart { get; set; } = DateOnly.FromDateTime(DateTime.Now);
        public DateOnly SeasonEnd { get; set; } = DateOnly.FromDateTime(DateTime.Now);
        public List<Site> Sites { get; set; } = new List<Site>();
    }

    public class Site
    {
        public string Id { get; set; } = Guid.NewGuid().ToString();
        public int SiteNumber { get; set; }
        public int MaxOccupancy { get; set; }
        public bool IsAccessible { get; set; }
        public int MaxRVLength { get; set; }
        public decimal DailyFee { get; set; }
    }

    public class DBContext : DbContext
    {
        public DbSet<Campground> Campgrounds { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=JsonInSQL-Demo.db");
            // optionsBuilder.UseSqlServer("Server=localhost;Database=SampleDb;Trusted_Connection=True;Encrypt=False;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // modelBuilder.Entity<Campground>().ComplexProperty(b => b.Sites, b => b.ToJson());
            modelBuilder.Entity<Campground>().ComplexCollection(b => b.Sites, b => b.ToJson());
        }
    }

Notice that the DBContext only defines one entity:

    public DbSet<Campground> Campgrounds { get; set; }

In the OnModelCreating method, notice the model builder is used to map the "Sites" list as a "Complex Collection" in the Campground. The end result is that the database only has the campground table and stores the site data as JSON object:

    modelBuilder.Entity<Campground>().ComplexCollection(b => b.Sites, b => b.ToJson());

3. Lets look at the Program.cs. In the Main program, I created an instance of the DBContext and made sure the DB was created. Then I created 20 random campgrounds, each with a random number of sites. I added that data to the context and saved.
4. Query time! I did a query that look at the campground table and looked for "sites" (JSON data) that had a Max RV Length greater than 39 feet. This query shows that the complex-sites-JSON-object was queried. It worked beautifully. Very exciting!!!

    //Get campgrounds with a RV length of at least 39
    var suitableCampgrounds = await db.Campgrounds
        .Where(cg => cg.Sites.Any(s => s.MaxRVLength >= 39))
        .ToListAsync();

I love that we have this flexibility in Entity Framework. Most of the data I work with is structured and should be structured - but I am excited to have this tool as an option for when I will need a little more flexibility.

Source Code at GitHub