Entity Framework – Storing a complex entity as JSON in a single DB column

jsonDuring the development of TicketDesk 2.5, I came across an unusual case. I wanted to store a large chunk of my entity model as a simple JSON string, and put it into a single column in the database.

Here’s the setup:

I have an entity that encapsulates all of a user’s display preferences and similar settings. One of those settings is a complex set of objects that represents the user’s custom settings for list view UI pages. There can be many lists, each with separate settings. Some of the settings for a list contain collections of other objects, resulting in a hierarchy of settings that goes three levels deep

I didn’t want to represent these settings as a relational data model in the database though. Using EF’s standard persistence mapping conventions, this collection of settings ends up being spread across six  tables. The TSQL queries to access that data would be rather slow and cumbersome, and the relational model doesn’t add any value at all.

Instead, I just wanted to serialize out the entire collection of settings as a single JSON string, and store it in one column in the user settings table. At the same time though, I wanted the code behave as if this were just a natural part of my regular EF entity model.

The solution:

The solution was to use a complex type, with some fluent model binding magic, to flatten the hierarchy into a single column. The heirarchy itself is represented as a custom collection, with a bit of manual JSON serialization/deserialization built-in.

I got a pointer the right general direction from this SO post, which saved me a bunch of time when approaching this more advanced scenario.

First, let’s take a look at the root entity here:

public class UserSetting
 {
 	[Key]
    public string UserId { get; set; }

    public virtual UserTicketListSettingsCollection ListSettings { get; set; }
}

This is the only entity which will map to its own table in the DB. The ListSettings collection is the property I want persisted as JSON in a single column.

Here is the custom collection that will be stored:

public class UserTicketListSettingsCollection: Collection<UserTicketListSetting>
{
    public void Add(ICollection<UserTicketListSetting> settings)
    {
        foreach (var listSetting in settings)
        {
            this.Add(listSetting);
        }
    }

    [JsonIgnore]
    public string Serialized
    {
        get { return Newtonsoft.Json.JsonConvert.SerializeObject(this); }
        set
        {
            if (string.IsNullOrEmpty(value))
            {
                return;
            }

            var jData = Newtonsoft.Json.JsonConvert.DeserializeObject<List<UserTicketListSetting>>(value);
            this.Items.Clear();
            this.Add(jData);
            
        }
    }
}

This is a collection type, and is inheriting generic Collection<T>. In this case, T is the UserTicketListSetting type — which is a standard POCO wrapping up all of the settings for all of the various list views in one place.

Some of the properties inside UserTicketListSetting contain collections of other POCOs. The specific details of what’s going inside those classes doesn’t matter to this discussion, just understand that it results in a hierarchy of related objects. None of the properties in that hierarchy are marked up with EF attributes or anything.

The only magic here is that we have a Serialized property, which manually handles converting from/to JSON. This is the only property that we want persisted to the database.

To make that persistence happen, we will make UserTicketListSettingsCollection an EF complex type, though not by using the [ComplexType] attribute. Instead, we’ll manually register this complex type via the fluent model builder API.

In the DB Context this looks like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
	modelBuilder.ComplexType<UserTicketListSettingsCollection>()
        .Property(p => p.Serialized)
        .HasColumnName("ListSettingsJson");
}

This just tells EF that UserTicketListSettingsCollection is a complex type, and the only property we care about is the Serialized property. If there were other properties in UserTicketListSettingsCollection, you would need to exclude them with something like:

modelBuilder.ComplexType<UserTicketListSettingsCollection>().Ignore(p => p.PropertyToIgnore);

And that’s all I needed to get EF to store this entire hierarchy as a single JSON column.

Using this model in code is just like using any other EF entity. I can query it with LINQ expressions, and SaveChanges on the DbContext updates the JSON data in DB just like any other entity. Even the generation of code-based migrations works as expected.

It took a LOT of experimentation and digging to figure out how to make this work, but the implementation is rather simple once you know how to approach the problem.

This also reflects the amazing power and flexibility of Entity Framework. EF can be extended to fit very advanced scenarios even when the designers didn’t anticipate them directly.

You can see the full implementation of this in TicketDesk 2.5. Currently, TD 2.5 is in alpha, so look to the develop branch in source control on CodePlex. You will find this example, as well as several variations in the TicketDesk.Domain assembly.