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:
And this is my POCO class in C#:
This is the way I initialize POCO object:
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.
2
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.
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.
UPDATE
You also can define one table (one entity), like so:
public class Log
{
public string id { get; set; }
public int? sent { get; set; }
public int? recv { get; set; }
public int? total { get; set; }
}
And then treat your JSON as DTOs, so you need to define classes for DTOs:
public class UsageDto
{
public int? sent { get; set; }
public int? recv { get; set; }
public int? total { get; set; }
}
public class LogDto
{
public string id { get; set; }
public UsageDto usage { get; set; }
}
Then you need to adjust your code, so it does the appropriate mapping:
var rawJson = @"[
{
""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
}
}
]";
var dtos = JsonSerializer.Deserialize<LogDto[]>(rawJson);
var logs = dtos
.Select(x => new Log
{
id = x.id,
sent = x.usage.sent,
recv = x.usage.recv,
total = x.usage.total,
}).ToArray();
6
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.
1