Deserialialize the json file & seed it in the sql server database in Entity Framework Core API with Code First Approach(Database must be created with json file data as per model class strcuture of json file)

Question

How do I write the code of defining Primary key in classes & deserialize the json file , create database & save the data at the time of application startup?

1. Here is the structure of model classes as per json file public class Rootobject
   {
   public Prize[] prizes { get; set; }
   } public class Prize
   {
   public string year { get; set; }
   public string category { get; set; }
   public Laureate[] laureates { get; set; }
   public string overallMotivation { 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; }
   }
2. Below is the Json file Structure

{
"year": "1901",
"category": "peace",
"laureates": [
{
"id": "462",
"firstname": "Henry",
"surname": "Dunant",
"motivation": "\"for his humanitarian efforts to help wounded soldiers and create international understanding\"",
"share": "2"
},
{
"id": "463",
"firstname": "Fr\u00e9d\u00e9ric",
"surname": "Passy",
"motivation": "\"for his lifelong work for international peace conferences, diplomacy and arbitration\"",
"share": "2"
}
]
}

Answer 1

Hi @Sarwar Akbar ,

Based on the Json file, the model should like this:

Then, you can add the DbSet in the ApplicationDbContext and you can use the HasKey method to set the primary key.

And in the program file, it is an asp.net 6 sample. register the applicationdbcontext and add the connection string in the appsettings.json file.

program.cs file:

var builder = WebApplication.CreateBuilder(args);  
  
builder.Services.AddDbContext<ApplicationDbContext>(options =>  
    options.UseSqlServer(builder.Configuration.GetConnectionString("ApplicationDbContext")));  

appsettings.json file:

"ConnectionStrings": {  
  "ApplicationDbContext": "Server=(localdb)\\mssqllocaldb;Database=WebApplication8.Data;Trusted_Connection=True;MultipleActiveResultSets=true"  
}  

Then you can open the Package Manage Console and run the following commands to enable migration and generate the relate table in the database.

To seed the database. you can create a SeedData class like this:

Then add the seed initializer in the program.cs file:

var app = builder.Build();  
  
using (var scope = app.Services.CreateScope())  
{  
    var services = scope.ServiceProvider;  
  
    SeedData.Initialize(services);  
}  

After that, running the application, it will read the json content and insert the data into the database, like this:

You can view the source code from here: 218456-sourcecode.txt

More detail information, see Seed the database

[Note] The above code apply to Asp.net 6, if you are using the previous version, see Seed the database In Asp.net 5

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

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.");  
                }  
            }  
        }  

Answer 3

Please read the official docs.
https://learn.microsoft.com/en-us/ef/core/modeling/data-seeding#custom-initialization-logic

There are two basic steps, First, create EF models using code first as explained in any tutorials. Next, you'll write custom initialization logic to read the JSON file and deserialize the data into a C# type. Use the C# type to populate EF Entity and save.

Answer 4

I request you to give a solution according to prize.txt file for the same step by step with screenshots just like Mr, ZhiLv-MSFT.

The following code has been tested. Keep in mind, the code from ZhiLv-MSFT assumes the year is the primary key. Since your data has duplicate years, ZhiLv-MSFT EF models cannot be used.

Json object model

    public class Rootobject  
    {  
        public List<Prize> prizes { get; set; }  
    }  
  
    public class Prize  
    {  
        public string year { get; set; }  
        public string category { get; set; }  
        public List<Laureate> laureates { get; set; }  
        public string overallMotivation { 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; }  
    }  

SeedData method

    public class SeedData  
    {  
        public static void Initialize(SqliteContext context)  
        {  
              
            if (context.Prizes.Any())  
            {  
                return;   // DB has been seeded  
            }  
  
            //Read the JSON file into json model (not the same as the EF models)  
            JsonModels.Rootobject jsonPrizes = new JsonModels.Rootobject();  
            using (StreamReader r = new StreamReader("data.json"))  
            {  
                string json = r.ReadToEnd();  
                jsonPrizes = JsonSerializer.Deserialize<JsonModels.Rootobject>(json);  
            }  
  
            //Create the EF Models and save  
            foreach(JsonModels.Prize jsonPrize in jsonPrizes.prizes)  
            {  
                context.Prizes.AddRange(  
                        new Prize()  
                        {  
                            Year = jsonPrize.year,  
                            Category = jsonPrize.category,  
                            OverallMotivation = jsonPrize.overallMotivation ?? String.Empty,  
                            Laureates = jsonPrize.laureates.ConvertToEntity()  
                        });  
            }  
  
            context.SaveChanges();  
        }  
    }  

Entity Framework 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 string OverallMotivation { 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 is the same as above.

Lastly, I strongly recommend that you go through a few beginning level entity Framework tutorials to make sure you understand the technology your using.