Save Collection As JSON with Entity Framework

Question

I am trying to find a way to have an object that is a colleciton but when it is saved to the database becomes a JSON string. How can i set up entity framework 6.1 to do this? Example:

 public class Company{

    public Company(){
       this.Times = new HashSet<DateTime>();
    }

    public int Id {get;set;}

    public string Name {get;set;}

    public List<DateTime> Times {get;set;}

 }

Company is an entity object. I would like for Times to be stored in the database as a json string of times. I would like it to serialize when reading from the database as a list of date time. I would like the list on save to be converted back to the json string and saved.

Matt Jenkins · Accepted Answer · 2017-10-02T18:51:21.530

16

The problem with the accepted answer is that any changes to the contents of the list (adding, modifying or deleting entries) won't be tracked by EF.

Here is my solution, inspired by this excellent blog post.

This class takes care of serializing and deserializing so that the collection can be stored in a single column on the parent model:

[ComplexType]
public class DateTimeCollection : Collection<DateTime>
{
    public void AddRange(IEnumerable<DateTime> collection)
    {
        foreach (var item in collection)
        {
            Add(item);
        }
    }

    [Column("Times")]
    public string Serialized
    {
        get { return JsonConvert.SerializeObject(this); }
        private set
        {
            if (string.IsNullOrEmpty(value))
            {
                Clear();
                return;
            }

            var items = JsonConvert.DeserializeObject<DateTime[]>(value);
            Clear();
            AddRange(items);
        }
    }
}

That's it! You can now use this new collection on your parent class exactly as you'd expect. Changes to the contents of the collection will be tracked.

public class Company{
    // ...
    public DateTimeCollection Times { get; set; }
}

edited Oct 02 '17 at 18:51

answered Feb 07 '16 at 23:28

Matt Jenkins

2,824
1
30
34

6

Awesome answer. It's worth including the way in which you can define ComplexType in `DBCotext`. as: `modelBuilder.ComplexType() .Property(p => p.Serialized) .HasColumnName("Times");` – Sudarshan_SMD Jul 27 '16 at 04:53
Do you have any idea how to make this Json column queryable through EF? can you override the implementation of "Filter/Where" for this property? – Terence May 22 '18 at 18:34
1

In `EF6` we still lack the `EF Core` feature of `ValueConverters` and `Property().HasConversion()`. The accepted answer suggests a separate "flattened" value for each `Collection`, which pollutes the `Model`. That's a big no! This solution right here is the cleanest one by far. Though using `Data Annotation` `Column` on this custom `Collection` implementation, pollutes the `Collection` itsel, which should not be correlated to the `EF`. To fix, just use `Fluent API` and add `Property(i => i.Times.Serialized).HasColumnName("Times");` in `DbContext.OnModelCreating(ModelBuilder)`. – Efthymios Nov 20 '20 at 12:14

score 6 · Answer 2 · answered Feb 03 '15 at 19:53

6

The following should work (I used Json.Net, but you can change it to any other serializer):

public class Company
{

    public int Id {get;set;}

    public string Name {get;set;}

    [NotMapped]
    public List<DateTime> Times {get;set;}

    [Column("Times")]
    public string TimesSerialized
    {
        get
        {
            return JsonConvert.SerializeObject(Times);
        }
        set
        {
            Times = string.IsNullOrEmpty(value)
                    ? new List<DateTime>()
                    : JsonConvert.DeserializeObject<List<DateTime>>(value);
        }
    }
}

You can also make TimesSerialized private if you map it manually.

answered Feb 03 '15 at 19:53

DixonD

6,557
5
31
52

1

@rleffler check this answer about private properties: http://stackoverflow.com/a/13810766/213725 – DixonD Feb 03 '15 at 20:07
@DixonD I'm confused. Shouldn't the set serialize the list (since it will be saved in the database as a string) and the get - deserialize it? – kanpeki Jun 14 '18 at 07:47
@kanpeki The set is called by EF when it reads the entity from the DB. So it calls the set and passes the string DB value, so this setter deserializes it and updates the Times property. And vice versa for the get - it will be called by EF when it will be saving the entity to the DB – DixonD Jun 15 '18 at 06:57

Save Collection As JSON with Entity Framework

2 Answers2