Best way to update JSON property with EF core

Question

I considering to store some information as a JSON object in a SQL Server database but it is not clear for me how I should update a single property of that object in JSON column but not the whole object.

It is an asp.net core application. As ORM I use Entity Framework core. I want to create a class which will look like this example:

public class Person{
  public int Id {get;set;}
  public string FirstName {get;set;}
  public string  LastName {get;set;}  
  public string AdditionData {get;set;} //json object
}

And JSON object in "AdditionData" column will have the following format:

{
  phoneNumbers:["123456789","789456123"],
  groups:[{name:"g1", description:"blah"}, {name:"g2", description:"blah2"}]
}

So now I want add new PhoneNumber to some person and method signature might look like this:

void AddPhoneNumber(int personId, string phoneNumber);

I found only two options how I can add new phoneNumber to described object.

First option:

1. find person by id using EF
2. deserialize whole AdditionData object from string
3. add new phoneNumber to additionData.PhoneNumbers list
4. serialize AdditionData object back to string
5. update person.AdditionData property with this string
6. perform "saveChanges"

Second option:

Use JSON_MODIFY SQL function and execute raw SQL with parameter, something like this:

context.Database.ExecuteSqlCommand("UPDATE Person
SET additionalData = JSON_MODIFY(additionalData,"append  $.phoneNumbers", @phoneNumber)
WHERE Id= @personId", personIdParam,phoneNumberParam);

The Question: Is there another way to do this? Because those two ways, which I described, don't look elegant to me (especially first one).

Answer 1

Its not possible as of EF Core 2.2. There is a proposal for such a feature, but I would not hold my breath.

If the JSON you are storing is small and/or the updates are rare I would go with the first approach as the changes are stored in the context and you don't need a transaction and aligns better with the overall design of EF. Plus it gives you compile time safety and is easier to refactor/change. Check this thread out - How to store JSON in an entity field with EF Core?

If you need performance than definitely the JSON_MODIFY sql command.

Answer 2

EF 7.0 Upgrade

Now you can have Json columns for sql server with EF 7 and also you can update json properties:

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public AdditionalData AdditionalData { get; set; } //Save this object as Json
}

public class AdditionalData
{
    public List<string> PhoneNumbers { get; set; }
    //other props 
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>().OwnsOne(
        p => p.AdditionalData, options =>
        {
            options.ToJson(); //will save the object as Json into database
        });
}

So for example if you want to update the phone numbers you can do it like this:

var perzon = await _dbContext.Persons.FirstOrDefaultAsync(x=> x.Id == id);

//update phone numbers
author.AdditionalData.PhoneNumbers.Add("123467899");

_dbContext.Update(person);
await _dbContext.SaveChangesAsync(); // will update the json