Custom type with automatic serialization/deserialization in EF Core

Question

I am using the ical.net library to work with recurrence rules and recurring events within my ASP.NET Core 2 app. I would like to be able to serialize a CalendarEvent object and save it in the database, and I'm looking for the best-practices approach to doing so. I have considered using a non-mapped property to hold the actual object, and defining a string mapped property, and using the event handlers in the DbContext to serialize the object and set it to the string prop before saving the entity , and likewise to recreate the CalendarEvent object from the serialized string when building the entity. Something like the following:

public class AvailabilityRule: ApplicationEntity
{
    ...

    [NotMapped]
    public CalendarEvent Event { get; set; }

}

public class ApplicationDbContext : AuditableDbContext
{
    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<AvailabilityRule>().Property<string>("SerializedEvent");
    }

    public override int SaveChanges()
    {
        // Get instances of AvailabilityRule from ChangeTracker, and
        // set the serialized property
    }
}

I'm assuming there is a similar method I can override to do the opposite upon pulling the record out of the database, but I don't know what it is.

Being new to ASP.NET and Entity Framework, I'm concerned about doing things The Right Way^TM, so I'm interested i knowing if there is a better way. I can't find much information online about this topic.

Answer 1

Storing a complex entity as JSON in a single database column turns out to be pretty easy with the Value Conversions which were added in EF Core 2.1.

[NotMapped] not needed

public class AvailabilityRule: ApplicationEntity
{
   ...
    // [NotMapped]
    public CalendarEvent Event { get; set; }
}

Add the following extension for PropertyBuilder:

public static class PropertyBuilderExtensions
{
    public static PropertyBuilder<T> HasJsonConversion<T>(this PropertyBuilder<T> propertyBuilder) where T : class, new()
    {
        ValueConverter<T, string> converter = new ValueConverter<T, string>
        (
            v => JsonSerializer.Serialize(v, null),
            v => JsonSerializer.Deserialize<T>(v, null) ?? new T()
        );

        ValueComparer<T> comparer = new ValueComparer<T>
        (
            (l, r) => JsonSerializer.Serialize(l, null) == JsonSerializer.Serialize(r, null),
            v => v == null ? 0 : JsonSerializer.Serialize(v, null).GetHashCode(),
            v => JsonSerializer.Deserialize<T>(JsonSerializer.Serialize(v, null), null)
        );

        propertyBuilder.HasConversion(converter);
        propertyBuilder.Metadata.SetValueConverter(converter);
        propertyBuilder.Metadata.SetValueComparer(comparer);
        propertyBuilder.HasColumnType("jsonb");

        return propertyBuilder;
    }
}

In the OnModelCreating method of the database context need call HasJsonConversion, which does the serialization, deserialization and track changes to your object:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<AvailabilityRule>()
        .Property(b => b.Event )
        .HasJsonConversion();
}

Answer 2

I think you're looking at this from the wrong angle. The data you persist should be normalized. Although it's probably unlikely, iCal could go away tomorrow, or next year, or ten years from now, replaced with the newest flavor of the week, and now you've got all this junk in your database with no way to do anything meaningful with it.

Or really, the worst problem with denormalized data is simply that you can't query on it. Supposing you wanted to query events that recur in a certain way, there's no way you can do that without materializing the entire result set, parsing the iCal info, and then running another query in-memory.

Long and short, persist the info you need to create the iCal. Then, create the iCal from the entity instance on the fly. Don't persist the iCal itself to your database.