我正在尝试从外部API提取数据,并使用实体框架将这些数据保存在MS SQL数据库中。我是实体框架的新手,不知道如何使我的数据持久化。根据以下模型,按照代码优先原则创建数据库:
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; }
}
我成功地从API接收到JSON格式的数据,对其进行反序列化并将其添加到List <JsonResponse> dataG
中。现在,我希望通过将这些数据保存到EF数据库中,使其持久化。由于我的JsonResponse
包含一个Devices
对象列表,所以我很难理解如何做到这一点。我从外部API获取数据的控制器如下所示:
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);
}
来自API的JSON数据如下:
{
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; }
}
}
编辑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);
}
你好,这是我的第一个答案,我正在学习英语,我会努力做得更好。
你有两张桌子吗?主细节?
我建议您首先组织您的代码并分离您需要的职责和Dto对象来表示您的JSON对象和将数据插入数据库的实体,然后分离方法,但我将与您当前的实体共享解决方案。
首先,您需要有一个包含实体和连接字符串的DbContext,一个包含EntityFrameworkCore的DbContext示例:
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; }
}
如果你有一个主细节,你需要用外键创建一个导航属性——在这种情况下,我在设备实体中创建了这个属性。
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; }
}
要保存数据,您需要添加主体实体,详细信息将自动保存:
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);
}
}
}
如果您没有主细节,并且需要保存一系列数据,则必须使用AddRange,并在一个操作中保存所有数据:
public async Task<ActionResult> Test()
{
JsonResponse data = GetJsonData();
db.Devices.AddRange(data.Devices);
db.SaveChanges();
return View(data);
}
正如marc_s所说,EF核心支持访问许多不同的数据库,您使用的是哪种数据库?
通常,要通过EF核心将新数据插入数据库,可以使用DbContext.Add
方法或DbContext.AddRange
方法添加新项。您可以查看以下文章:EF核心保存数据。
我假设您使用的是MS SQL server数据库,根据您的描述,JsonResponse
和Devices
对象包含一对多关系,因此您可以使用导航属性,而不是Devices[]
,尝试更改代码如下:
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; }
}
然后,在迁移并在数据库中生成相关的表之后,可以参考以下代码将新项目插入数据库:
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();
}
注释:由于JsonResponse
对象和Devices
对象包含一对多关系,当使用上面的代码插入新的JsonResponse
时,它将自动将相关的Devices
对象插入到Devices表中。有关详细信息,请查看"保存相关数据"。
此外,您还可以使用以下代码将新设备添加到设备表中:
List<Devices> devicesdata = new List<Devices>()
{
//add Devices.
};
_dbcontext.Devices.AddRange(devicesdata);
_dbcontext.SaveChanges();
参考:实体框架核心一对多关系约定
导航属性
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; }
}
更新控制器
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);
}