Your question spans almost the entire dev stack. I strongly recommend that you take the time to learn the basics. That's why I provided the links in my initial post. The problem I see in your approach is the models are missing navigation properties. Secondly, the laureate Id does not map to anything. I'm guessing the Id comes from another database? Also, your sample json data does not contains an "overallMotivation" property. I left that bit out of the example but the overallMotivation could easily be added.
I would approach this, as recommended above, as two distinct steps. First, create the Entity Framework models. I used the openly published pattern found in the relationship documentation.
Relationships
EF Models
namespace WebApiDb.Models
{
public class Prize
{
[Key]
public int PrizeId { get; set; }
public string Year { get; set; }
public string Category { get; set; }
public List<Laureate> Laureates { get; set; }
}
public class Laureate
{
[Key]
public int LaureateId { get; set; }
public string RemoteIdentifier { get; set; }
public string Firstname { get; set; }
public string Surname { get; set; }
public string Motivation { get; set; }
public string Share { get; set; }
public int PrizeId { get; set; }
public Prize Prize { get; set; }
}
}
DbContext
public class SqliteContext : DbContext
{
public SqliteContext(DbContextOptions<SqliteContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Laureate>()
.HasOne(p => p.Prize)
.WithMany(b => b.Laureates);
}
public DbSet<Prize> Prizes { get; set; }
public DbSet<Laureate> Laureates { get; set; }
}
Use the command line tools add-migration and update-database to create the tables.
Second, create separate models for the json file deserialization. Populate the JosnPrize object model from the json file. My file is named data.json and located in the application root.
namespace WebApiDb.JsonModels
{
public class JosnPrize
{
public string year { get; set; }
public string category { get; set; }
public List<Laureate> laureates { get; set; }
}
public class Laureate
{
public string id { get; set; }
public string firstname { get; set; }
public string surname { get; set; }
public string motivation { get; set; }
public string share { get; set; }
}
}
The method to seed the data is below. I created an extension method, ConvertToEntity, that converts the json object model into the EF model.
public class SeedData
{
public static void Initialize(SqliteContext context)
{
// Look for any Prizes.
if (context.Prizes.Any())
{
return; // DB has been seeded
}
//Read the JSON file into json model (not the same as the EF models)
List<JsonModels.JosnPrize> jsonPrizes = new List<JsonModels.JosnPrize>();
using (StreamReader r = new StreamReader("data.json"))
{
string json = r.ReadToEnd();
jsonPrizes = JsonSerializer.Deserialize<List<JsonModels.JosnPrize>>(json);
}
//Create the EF Models and save
foreach(JsonModels.JosnPrize jsonPrize in jsonPrizes)
{
context.Prizes.AddRange(
new Prize()
{
Year = jsonPrize.year,
Category = jsonPrize.category,
Laureates = jsonPrize.laureates.ConvertToEntity()
});
}
context.SaveChanges();
}
}
The extension method.
namespace WebApiDb.Extensions
{
public static class JsonLaureateExtension
{
public static List<Models.Laureate> ConvertToEntity(this List<JsonModels.Laureate> Laureate)
{
List<Models.Laureate> results = (from l in Laureate
select new Models.Laureate()
{
Firstname = l.firstname,
Motivation = l.motivation,
RemoteIdentifier = l.id,
Share = l.share,
Surname = l.surname
}).ToList();
return results;
}
}
}
Invoking the SeedData.Initialize() method from Program.cs. Again, this pattern is covered in the openly published documentation.
Data Seeding
private static void CreateDbIfNotExists(IHost host)
{
using (var scope = host.Services.CreateScope())
{
var services = scope.ServiceProvider;
try
{
var context = services.GetRequiredService<SqliteContext>();
SeedData.Initialize(context);
}
catch (Exception ex)
{
var logger = services.GetRequiredService<ILogger<Program>>();
logger.LogError(ex, "An error occurred creating the DB.");
}
}
}