Share via

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)

Sarwar Akbar 31 Reputation points
Jul 6, 2022, 11:21 AM

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"
}
]
}

.NET
.NET
Microsoft Technologies based on the .NET software framework.
4,011 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,709 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,158 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,741 Reputation points Microsoft Vendor
    Jul 7, 2022, 6:54 AM

    Hi @Sarwar Akbar ,

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

    218447-image.png

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

    218502-image.png

    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:

    218474-image.png

    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:

    218396-1.gif

    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

    2 people found this answer helpful.

  2. AgaveJoe 29,041 Reputation points
    Jul 8, 2022, 3:08 PM

    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.");  
                    }  
                }  
            }  
    
    1 person found this answer helpful.

  3. AgaveJoe 29,041 Reputation points
    Jul 6, 2022, 12:53 PM

    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.


  4. AgaveJoe 29,041 Reputation points
    Jul 9, 2022, 12:37 PM

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.