I’m trying to pull data from an external API and save this data in a MS SQL Database using Entity Framework. I’m new to Entity Framework and can’t figure out how to make my data persistent. The Database is created following the Code First principle according to the following Model:
JsonResponse.cs
public partial class JsonResponse
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }
[JsonProperty("prtg-version")]
public string PrtgVersion { get; set; }
[JsonProperty("treesize")]
public int TreeSize { get; set; }
[JsonProperty("devices")]
public Devices[] devices { get; set; }
}
public partial class Devices
{
[Key]
[JsonProperty("objid")]
public int objid { get; set; }
[JsonProperty("probe")]
public string probe { get; set; }
[JsonProperty("device")]
public string device { get; set; }
[JsonProperty("host")]
public string host { get; set; }
}
I managed to successfully receive the data from the API in JSON, deserialize it and add it to the List <JsonResponse> dataG. Now I wish to make this data persistent by saving it to the EF Database. I’m struggling on how to do this since my JsonResponse contains a list of Devices Objects. My Controller in which I pull the data from the external API looks as follows:
CMDBController.cs
public class CMDBController : Controller
{
private DbContext db = new DbContext();
public async Task<ActionResult> Test()
{
List<JsonResponse> dataG = new List<JsonResponse>();
using (var httpClient = new HttpClient())
{
using (var response = await httpClient
.GetAsync(
"/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
)
{
string apiResponse = await response.Content.ReadAsStringAsync();
var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
dataG.Add(data);
var devices = data.devices;
foreach (var item in devices)
{
db.Add(item);
db.SaveChanges();
}
}
}
return View(dataG);
}
The JSON data from the API looks like this:
{
prtg-version: "20.4.63.1412",
treesize: 2,
devices: [
{
objid: 40,
probe: "Local Probe",
group: "Local Probe",
device: "Probe Device",
host: "127.0.0.1"
},
{
objid: 42,
probe: "Local Probe",
group: "Network Infrastructure",
device: "DNS: 84.116.46.23",
host: "84.116.46.23"
}
DBContext
public class FrontDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
"Server=(localdb)\MSSQLLocalDB;Database=FrontDB;MultipleActiveResultSets=true");
}
public DbSet<JsonResponse> Responses { get; set; }
}
}
EDIT 1
public class CMDBController : Controller
{
private DbContext db = new DbContext();
public async Task<ActionResult> Test()
{
List<JsonResponse> dataG = new List<JsonResponse>();
using (var httpClient = new HttpClient())
{
using (var response = await httpClient
.GetAsync(
"/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
)
{
string apiResponse = await response.Content.ReadAsStringAsync();
var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
dataG.Add(data);
var devices = data.devices;
db.Responses.AddRange(data);
db.SaveChanges();
}
}
return View(dataG);
}
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Hello this is my first answer and I’m learning english I will try to do better.
Do you have two tables? A Master-Detail?
I recommend you organize your code and separate responsibilities first you need to have and Dto Object to represent your JSON object and an entity to insert the data into a Database then separate the methods but I will share you the solution with your current entity.
First you need to have a DbContext with your entities and your connectionstring, an example of DbContext with EntityFrameworkCore:
public class MyDbContext:DbContext
{
private readonly string connectionString;
public MyDbContext(string connectionString)
{
this.connectionString = connectionString;
_migrateDatabase = true;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseMySql(connectionString);
}
public DbSet<JsonResponse> JsonResponses { get; set; }
public DbSet<Devices> Devices { get; set; }
}
If you have a master detail you need to create a navigation property with a foreign key in this case I created this property in the Device entity.
public partial class JsonResponse
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }
[JsonProperty("prtg-version")]
public string PrtgVersion { get; set; }
[JsonProperty("treesize")]
public int TreeSize { get; set; }
[JsonProperty("devices")]
public List<Devices> devices { get; set; }
}
public partial class Devices
{
[Key]
[JsonProperty("objid")]
public int objid { get; set; }
[JsonProperty("probe")]
public string probe { get; set; }
[JsonProperty("device")]
public string device { get; set; }
[JsonProperty("host")]
public string host { get; set; }
public int JsonResponseId { get; set; }
[ForeignKey("JsonResponseId")]
public JsonResponse JsonResponse { get; set; }
}
To save the data you need to add the principal entity and the detail will saved automatically:
public class CMDBController : Controller
{
private MyDbContext db = new MyDbContext("mycnn");
public async Task<ActionResult> Test()
{
JsonResponse data = GetJsonData();
db.JsonResponses.Add(data);
db.SaveChanges();
return View(data);
}
JsonResponse GetJsonData(){
using (var httpClient = new HttpClient())
{
var response = await httpClient.GetAsync(
"/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host");
string apiResponse = await response.Content.ReadAsStringAsync();
return JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
}
}
}
If you don’t have a master detail and you need to save a range of data you must use AddRange, and save all the data in one action:
public async Task<ActionResult> Test()
{
JsonResponse data = GetJsonData();
db.Devices.AddRange(data.Devices);
db.SaveChanges();
return View(data);
}
Method 2
As marc_s said, EF core support to access many different databases, which kind of database are you using?
Generally, to insert new data into database via EF core, you could use the DbContext.Add method or the DbContext.AddRange method to add new items. You could check this articles: EF core Saving data.
I assume you are using MS SQL server database, according to your description, the JsonResponse and the Devices object contains one-to-Many relationship, so you could use a navigation property, instead of a Devices[], try to change your code as below:
public partial class JsonResponse
{
[Key]
public int Id { get; set; }
[JsonProperty("prtg-version")]
public string PrtgVersion { get; set; }
[JsonProperty("treesize")]
public int TreeSize { get; set; }
[JsonProperty("devices")]
public List<Devices> devices { get; set; }
}
public partial class Devices
{
[Key()]
[DatabaseGenerated(DatabaseGeneratedOption.None)] // prevent database auto generate objid.
[JsonProperty("objid")]
public int objid { get; set; }
[JsonProperty("probe")]
public string probe { get; set; }
[JsonProperty("device")]
public string device { get; set; }
[JsonProperty("host")]
public string host { get; set; }
}
Then, after migration and generate the related tables in the database, you could refer the following code to insert new items into the database:
private readonly ILogger<HomeController> _logger;
private readonly WebApplication2Context _dbcontext;
public HomeController(ILogger<HomeController> logger, WebApplication2Context context)
{
_logger = logger;
_dbcontext = context;
}
public IActionResult Index()
{
List<JsonResponse> data = new List<JsonResponse>()
{
new JsonResponse(){ PrtgVersion ="20.4.63.1412", TreeSize = 2, devices = new List<Devices>()
{
new Devices(){ objid= 40, probe="Local Probe", device = "Probe Device", host = "127.0.0.1"},
new Devices(){ objid= 41, probe="Local Probe", device = "DNS: 84.116.46.23", host = "86.114.46.23"}
}
}
};
_dbcontext.JsonResponses.AddRange(data);
_dbcontext.SaveChanges();
return View();
}
Comment: Since the JsonResponse object and Devices object contains one-to-many relationship, when using above code to insert a new JsonResponse, it will auto insert the related Devices objects into the Devices table. More detail information, check the Saving Related Data.
Besides, you could also use the following code to add new Device into the Devices table:
List<Devices> devicesdata = new List<Devices>()
{
//add Devices.
};
_dbcontext.Devices.AddRange(devicesdata);
_dbcontext.SaveChanges();
Reference: Entity Framework Core One To Many Relationships Conventions
Method 3
Update the DbContextClass
public class MyDbContext:DbContext
{
private readonly string connectionString;
public MyDbContext(string connectionString)
{
this.connectionString = connectionString;
_migrateDatabase = true;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseMySql(connectionString);
}
public DbSet<JsonResponse> JsonResponses { get; set; }
}
Update the controller
public async Task<ActionResult> Test()
{
using (var httpClient = new HttpClient())
{
using (var response = await httpClient
.GetAsync(
"/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
)
{
string apiResponse = await response.Content.ReadAsStringAsync();
var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
db.JsonResponses.Add(data);
db.SaveChang();
}
}
return View(dataG);
}
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0