Inserting JSON file into SQL Server when JSON contains a list

Question

I am trying to import a JSON file into SQL server via a .Net Core application.

I have set up my classes using the tool in VS which converts a JSON doc to classes. I then used this to create database using the EF Core migration.

I have deserialised the JSON into my RootObject class using the following:

RootObject rootObject = Newtonsoft.Json.JsonConvert.DeserializeObject<RootObject>(jsonFromFile);

I can expand the RootObject object and all looks good, I can see each field and it contains the right data.

I then want to insert this data into the database, I currently have:

var mySolve = new RootObject
            {
                uuid = rootObject.uuid,
                solution = new Solution { gross_margin = rootObject.solution.myValue, imbalances = rootObject.solution.imbalances  }
            };


            db.RootObjects.Add(mySolve);
            db.SaveChanges();
            db.Dispose();

The problem is that the imbalances class is a list in the json file as seen below:

"solution": {
        "myValue": 9999.99,
        "imbalances": [
            {
                "commodity": "Value1",
                "name": "Val1",
                "direction": "INPUT",
                "amount": 1.419884
            },
            {
                "commodity": "Value2",
                "name": "Val2",
                "direction": "INPUT",
                "amount": 1.419884
            }
        ]
    }

The Class for imbalances in my app looks like this:

public class Imbalance
    {
        public int Id { get; set; }
        public string commodity { get; set; }
        public string name { get; set; }
        public string direction { get; set; }
        public float amount { get; set; }
    }

My app throws an exception on db.RootObjects.Add(mySolve); of:

The type of navigation property 'imbalances' on the entity type 'Solution' is 'Imbalance[]' which is an array type. Collection navigation properties cannot be arrays.

How can insert this object into my database?

UPDATE

I have a table for every section in the JSON file as my class library was auto generated from the JSON file. The imbalances table looks like this:

what does you context look like? have you created a table for the imbalances? do they have id's? you cannot insert an array of anonymous objects in a database. — Glenn van Acker
– Glenn van Acker, Commented Dec 3, 2019 at 12:10
Unrelated to your issue, but I'm not a fan of table named RootObject. Because Json2Csharp named the object that way. — xdtTransform
– xdtTransform, Commented Dec 3, 2019 at 12:29
Does this answer your question? Entity Framework 5 - code first array navigation property one to many with Interface Type — xdtTransform
– xdtTransform, Commented Dec 3, 2019 at 12:30
I think so combined with @nAviD's answer below. Just testing everything at mo. I agree with you about the RootObject name and I will be changing it once I move from development. — Silentbob
– Silentbob, Commented Dec 3, 2019 at 12:36

nAviD · Accepted Answer · 2019-12-03 12:26:17Z

2

In RootObject change the type of Imbalances property from Imbalance[] to List<Imbalance> or any class derived from ICollection.

answered Dec 3, 2019 at 12:26

nAviD

3,3413 gold badges40 silver badges61 bronze badges

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

1 Comment

Silentbob Over a year ago

That appears to work, let me do some more testing and I will come back and mark it as the answer if successful

Collectives™ on Stack Overflow

Inserting JSON file into SQL Server when JSON contains a list

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related