JSON string array into Entity Framework

Question

I'm trying to get a JSON file into a SQL Server database with auto identity keys and the correct foreign key relation. Everything is working gloriously with the exception of a string array. The data looks more or less like this:

{
  "id" : "123",
  "name" : "Some Stuff",
  "phrase" : "More Stuff",
  "type" : "ABC",
  "label" : "Some label",
  "responseType" : "The Response Type",
  "answers" : [ "9" ]
}

The "answers" part is causing me fits. It looks like it's almost exclusively a single value, but it could potentially have multiple values like

"answers" : [ "6", "7", "8" ]

Now I know that a List is not supported in EF for native types like string or int. I ultimately would rather have a separate table for the list of answer values anyway, which I'm calling DataAnswers.

        public partial class Response
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ResponseId { get; set; }

        [JsonProperty("id", NullValueHandling = NullValueHandling.Ignore)]
        public string Id { get; set; }

        [JsonProperty("name", NullValueHandling = NullValueHandling.Ignore)]
        public string Name { get; set; }

        [JsonProperty("phrase", NullValueHandling = NullValueHandling.Ignore)]
        public string Phrase { get; set; }

        [JsonProperty("type", NullValueHandling = NullValueHandling.Ignore)]
        public string Type { get; set; }

        [JsonProperty("label", NullValueHandling = NullValueHandling.Ignore)]
        public string Label { get; set; }

        [JsonProperty("responseType", NullValueHandling = NullValueHandling.Ignore)]
        public string ResponseType { get; set; }

        [JsonProperty("answers", NullValueHandling = NullValueHandling.Ignore)]
        public virtual List<DataAnswer> DataAnswers { get; set; }
    }

    public partial class DataAnswer
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DataAnswerId { get; set; }
        public string AnswerText { get; set; }
    }

Currently here's the error I'm getting.

Newtonsoft.Json.JsonSerializationException: 'Error converting value "9" to type 'ForeseeDemo.Data.DataAnswer'. Path 'items[0].responses[0].answers[0]', line 60, position 23.'

Any great ideas of how to get a list of answer strings into a table with a foreign key to the Response ?

Thanks in advance!

Moho · Accepted Answer · 2018-09-25 21:34:37Z

1

You could create a data transfer object then handle the conversion b/t that and the entity object.

You could create a custom JSON converter to handle conversion of the property.

You could create an additional property that is used for serialization/deserialization but not by EF and handle conversions there:

[JsonIgnore]
public virtual List<DataAnswer> DataAnswers { get; set; }
[NotMapped]
[JsonProperty( "answers", NullValueHandling = NullValueHandling.Ignore )]
public List<string> DataAnswerStrings
{
    get => DataAnswers?.Select( da => da.AnswerText )?.ToList();
    set => DataAnswers = value
        ?.Select( s => new DataAnswer() { AnswerText = s } )
        ?.ToList() 
        ?? new List<DataAnswer>();
}

answered Sep 25, 2018 at 21:34

Moho

16.8k1 gold badge36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON string array into Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related