Best way to update JSON property with EF core

.net .net-core entity-framework entity-framework-core sql-server

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 one: 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 one : 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: Does there exist another way to do this? Cause those two ways, which I described, looks for me not so elegant (especially first one).

1
3
1/17/2019 3:57:32 PM

Accepted Answer

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.

3
1/18/2019 9:03:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow