Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to save nested JSON into SQL Server using C# on .NET Core with Entity Framework?

I have my JSON objects in this format:

[
    {
        "id": "k002f3d",        
        "usage": {
            "sent": 0,
            "recv": 2,
            "total": 2
        }
    },
    {
        "id": "k641327",        
        "usage": {
            "sent": 0,
            "recv": 2,
            "total": 2
        }
    },
    {
        "id": "k6419b2",        
        "usage": {
            "sent": 872,
            "recv": 5912,
            "total": 6784
        }
    }
]

I would like to save it into a SQL Server table. I have defined my table to store that information like this:

enter image description here

And this is my POCO class in C#:

enter image description here

enter image description here

This is the way I initialize POCO object:

enter image description here

newClient is an object that we modify some property before adding to SQL Server database. When db.SaveChanges() is executed, it raises an error of not defined relationship between object or need use [NotMapped] attribute before property. As you can see, Usage is just a subset of property of the object itself.

Would you mind to guide me how can I do to be able to save record to database? I have tried to put [NotMapped] attribute before property usage in the class Log definition, error not generated but no data save for sent, recv, and total property of object Usage.

enter image description here

Tags:

3

Answers


  1. 0

    You should define foreign key relation:

    public class Usage
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int id { get; set; }

    public int? sent { get; set; }

    public int? recv { get; set; }

    public int? total { get; set; }
}

    Add also usageId property in Log entity:

    public class Log
{
    public string id { get; set; }
    public int usageId { get; set; }
    public Usage usage { get; set; }
}

    and then your code should work out of the box, as all the IDs should be now assigned automatically.

    Login or Signup to reply.
  2. 0

    Save nested JSON data into SQL Server using Entity Framework Core by defining model classes that represent the JSON structure, configuring relationships in the DbContext, and deserializing the JSON before saving it to the database.

    Login or Signup to reply.
  3. 0

    First of all, you need primary keys in both the Log and Usage entities to uniquely identify records in each table. Then, you need to define the relationship between the Log and Usage entities in Entity Framework. This involves setting up navigation properties and foreign keys to establish the connection between the Log and Usage tables in your database. Michał Turczyn wrote how to implement this in your Code in the Answer above.

    If you use auto-increment on the primary key, you only need to add or update your Log entity in the database context. However, this will not automatically create a related Usage entity unless the Usage entity is properly instantiated and added to the context as well.

    To deserialize your JSON into a Log object, you can use the Newtonsoft.Json NuGet package. Ensure that the JSON structure matches the Log class, including any nested objects for related entities. Then, you can deserialize it directly into the class without needing complex code.

    If you want to retrieve a Log from your database along with its related Usage, you need to use the Include method to eagerly load the related data. Here’s an example of how to do that:

    var logWithUsage = context.Log
.Include(log => log.Usage)
.FirstOrDefault(log => log.Id == someLogId);

    This will load the Log entity with its associated Usage entity in a single query, ensuring that both entities are available when needed. Remember, eager loading with Include is crucial if you want to access related data without making separate database calls for each entity.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search