Using JSON Data and EF to Seed a Database

I’m so used to use standard code (C# and EF APIs) to let EF help me seed a database. You know, instantiate an object, populate its fields, maybe add objects to a related list. Then add the whole kit n’ kaboodle to the DbContext and call SaveChanges.

I was showing some EF Core code to Geoffrey Grossenbach when we were talking about doing a Play by Play for Pluralsight on EF Core in advance of my buckling down to do a more serious course. Geoffrey looked at all the code for building up my objects to seed the database and said “wow that’s a lot of code. Can’t you use JSON or something?” (Note: I tired of trying to get this code formatted prettily in wordpress, but you get the point…right?)

private static List BuildWeatherEvents()
{
  var events = new List
  {
   WeatherEvent.Create(DateTime.Now,WeatherType.Sun,
    new List<string[]>{new []{"Julie","Oh so sunny!"}}),
   WeatherEvent.Create(DateTime.Now.AddDays(-2),WeatherType.Rain),
   WeatherEvent.Create(DateTime.Now.AddDays(-3),WeatherType.Sun,
    new List<string[]>{
      new []{"Julie","Oh lovely summer sun!
                      Too bad I'm on my computer"},
      new []{"Everyone in vermont", "Hooray let's go play!"},
      new []{"Sampson","I'd like to go for a swim, please!"},
    }),
   WeatherEvent.Create(DateTime.Now.AddDays(-4),WeatherType.Cloudy),
   WeatherEvent.Create(DateTime.Now.AddDays(-5),WeatherType.Rain),
   WeatherEvent.Create(DateTime.Now.AddDays(-6),WeatherType.Sun)
  };
 var lastEvent = 
   WeatherEvent.Create(DateTime.Now.AddDays(-1),  WeatherType.Snow,
          new List<string[]> {
             new[] { "Julie", "Snow? In July? 
                      Okay this is ridiculous even for VT!" } });
 lastEvent.Reactions.FirstOrDefault().Comments.Add
     (new Comment { Text = "Get over it, Julie!" });
 events.Add(lastEvent);
 return events;
}

Oh how much prettier it would be. Here’s how I’ve done it with EF Core but you can certainly use the same concept for doing the same with EF6.

My domain is WeatherEvent which is the domain in my EFCore demo at https://github.com/julielerman/EFCore-ASPNetCore-WebAPI-RTM, (which I have not yet updated to demonstrate using the JSON data).

Here’s the json which I store in a file called weatherdataseed.json.

[
 {
  "date": "2016-07-27T00:00:00",
  "time": "22:09:13.8216230",
  "type": 5,
  "reactions": [
   {
    "name": "Julie",
    "quote": "Oh so sunny!",
    "comments": []
   }
  ],
 "mostCommonWord": null
 },
 {
 "date": "2016-07-25T00:00:00",
 "time": "22:09:13.8237230",
 "type": 1,
 "reactions": [],
 "mostCommonWord": null
 },
 {
  "date": "2016-07-24T00:00:00",
  "time": "22:09:13.8238740",
  "type": 5,
  "reactions": [
   {
    "name": "Julie",
    "quote": "Oh lovely summer sun! Too bad I'm on my computer",
    "comments": []
   },
   {
    "name": "Everyone in vermont",
    "quote": "Hooray let's go play!",
    "comments": []
   },
   {
    "name": "Sampson",
    "quote": "I'd like to go for a swim, please!",
    "comments": []
   }
  ],
  "mostCommonWord": null
 },
 {
  "date": "2016-07-23T00:00:00",
  "time": "22:09:13.8239130",
  "type": 6,
  "reactions": [],
  "mostCommonWord": null
 },
 {
  "date": "2016-07-22T00:00:00",
  "time": "22:09:13.8239210",
  "type": 1,
  "reactions": [],
  "mostCommonWord": null
 },
 {
  "date": "2016-07-21T00:00:00",
  "time": "22:09:13.8239290",
  "type": 5,
  "reactions": [],
  "mostCommonWord": null
 },
 {
  "date": "2016-07-26T00:00:00",
  "time": "22:09:13.8239360",
  "type": 2,
  "reactions": [
   {
    "name": "Julie",
    "quote": "Snow? In July? Okay this is ridiculous even for VT!",
    "comments": [
     {
     "text": "Get over it, Julie!"
     }
    ]
   }
 ],
 "mostCommonWord": null
 }
]

So that’s not just data, but hierarchical data with 3 levels of relationship. Expressing it in json is a lot easier and prettier and readable than building all of that up in C#, creating the objects, etc.

Now of course it’s time for the magical JSON.NET which makes it possible to pull this data in to EF short and sweet.

This is the full code that I’m using to seed the database from the JSON using EF.

I’m calling it from startup.cs in an ASP.NET Core Web API inside the Configure method. Here I’m just reading the file with System.IO.File.ReadAllText and then passing that text into my Seedit method. Also note the ConfigureServices where I’m setting up the DbContext along with the connection string it requires.

Note: There’s been some churn in the code in this post as  Shawn Wildermuth and I went through some learning on twitter with Dave Fowler, one of the core leads on the aspnet team. I  changed my original code to streamline it as per a great suggestion from Shawn, but Dave pointed out some scoping issues with that. So now the sample is back to my original version, where the seedit method is responsible for creating a new ServiceScope and instantiating the context. 

 public void ConfigureServices(IServiceCollection services)
 { 
   services.AddDbContext<WeatherContext>(
     options=> options.UseNpgsql(
       Configuration["Data:PostgreConnection:ConnectionString"]));
   services.AddMvc();
 }

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
  loggerFactory.AddConsole(Configuration.GetSection("Logging"));
  loggerFactory.AddDebug();

  app.UseMvc();
  var dataText=System.IO.File.ReadAllText(@"weatherdataseed.json");
  Seeder.Seedit(dataText,app.ApplicationServices);
}

Here’s the Seedit method, which uses JSON.NET to deserialize the json into a list of  WeatherEvent objects.  The contract serializer is to overcome private setters in my WeatherEvent class. I got this from Daniel Wertheim’s github repo. Then I use the ASP.NET Core ServiceProvider to get service I set up in startup which will instantiate a WeatherContext along with the connection string specified in startup.

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.Extensions.DependencyInjection;
using EFCoreWebAPI;
using Newtonsoft.Json;
using JsonNet.PrivateSettersContractResolvers;

public static class Seeder {
  public static void Seedit(string jsonData,
                            IServiceProvider serviceProvider) {
  JsonSerializerSettings settings = new JsonSerializerSettings {
    ContractResolver = new PrivateSetterContractResolver()
  };
  List<WeatherEvent> events =
   JsonConvert.DeserializeObject<List<WeatherEvent>>(
     jsonData, settings);
  using (
   var serviceScope = serviceProvider
     .GetRequiredService<IServiceScopeFactory>().CreateScope())
   {
     var context = serviceScope
                   .ServiceProvider.GetService<WeatherContext>();
     if (!context.WeatherEvents.Any()) {
       context.AddRange(events);
       context.SaveChanges();
     }
   }
 }
}

After instantiating the context, I have it check to see if I have any weather events in the database yet so I don’t re-seed. (This is logic I want for seeding during my demo so you may have different rules for the seeding.) Now I call context.AddRange passing in the list of WeatherEvent objects. I could use the DbSet directly or the context to call AddRange. And finally, SaveChanges.

So the key here, whether you are using EFCore or EF6 (so the use of the service is specific to the fact that my app is an ASPNET Core api), is really just reading the json file, deserializing the data and adding it to the context. This is oh, so much simpler than creating all of that data imperatively.

It may not always be the answer depending on the shape of your data but it was perfect for this particular model and the seed data that I needed.

 

24 thoughts on “Using JSON Data and EF to Seed a Database

  1. Since you’re in aspnet core, is really avoid using a static class and just lean on the di to create the class and dependencies instead of requiring a service provider instance.

    1. hmm looks like I need to learn more about how to use aspnet core DI. No constructor in a static class (Seeder). So I’d have to change that class. But then do I have to instantiate Seeder in startup and then use the services to somehow pass the WeatherContext instance in? I am missing something because I can’t figure out how to magically get the context instance to the Seeder class and it seems more complicated (because of what I don’t know). I know you’re at NDC Sydney so no rush but interesting and curious.

      1. Shawn you’re awesome. Thanks for the help and showing me exactly how to pull it off . 🙂 I’m glad to have an ASPNET Core expert available. 🙂

    1. Simon, your [https://channel9.msdn.com/Series/aspnetmonsters] partners in crime showed that to me in montreal. It is SO cool. I have to start using it!!

  2. Two notes:
    1) keep in mind that if you have private setters, you’ll need to use a json net contract resolver. I’ll be sure that’s in the repo I’ll add for this. I needed that!
    2) Getting some comments on twitter from David Fowler from the ASP.NET team and want to ensure this solution makes him happy before I post to github.

  3. Hi Julie, since EF has changed some much since its initial release, would a developer find it useful to read one the many books you have written on EF? If so, which one of your books would you recommend? Thanks

    1. Hi Dom,
      I think the big book (programming ef 2nd edition) has some merit for understanding some of the internals of EF. The DbContext & Code First are pretty relevant. There are other newer books by other authors. However, I have put all of my efforts into creating content for Pluralsight rather than writing books. So that’s where you’ll find the same level of in-depth information from me about EF. Start with pluralsight.com/authors/julie-lerman

  4. Was great to see the journey of this solution – and I’ve learnt even more about DI and scopes in ASP.NET Core as a result, thanks!

  5. It is cheating of course, but you could have just used SQL Server 2016 json support. You would still have to write all the EF stuff to CRUD the database in the web app.

    CREATE TABLE Contract(
    ConId int,
    Symbol varchar(4)…)

    INSERT INTO Contract
    SELECT contract.*
    FROM OPENROWSET (BULK C:\Data\Contract.json’, SINGLE_CLOB) as j
    CROSS APPLY OPENJSON(BulkColumn)
    with (ConId int,
    Symbol varchar(4),…
    ) as contract
    WHERE NOT EXISTS(SELECT ConId FROM Contract c WHERE c.ConId = ConId)

    select * from Contract

  6. Hi, cool article.

    I released some weeks ago a .NET library called CherrySeed with which you can seed test data from any source (CSV, XML, Gherkin, SpecFlow, etc) to any destination. It is the next step of your solution.

    In SpecFlow we also have to seed many data into the database. So I created an easy-to-use wrapper around CherrySeed targeted to the SpecFlow usage.

    Would be nice if you check CherrySeed and ask me how it is you opinion about the library.

    Here is the link to the introduction article https://medium.com/@michael_altmann/why-seeding-test-data-into-database-using-cherryseed-net-c-529da467ffd9#.t29hgtvlt

    There are some links in the article to wiki, github, etc.

    1. Well, yeah, this was using EF Core. However AddRange is on DbSet in EF6 (and EF Core, too). Can you make it work with EF6?

      1. The probably is the IDbSet in EF6 does not expose AddRange (or RemoveRange). I don’t know why. See I had to create a workaround method.

  7. Hi Julie. I can’t believe how quickly you ALWAYS respond to new posts. There must be seventeen of you. Anyway, yes – – I created a static method and used it instead of context.AddRange():

    public static IEnumerable AddRange(
    this IDbSet dbset,
    IEnumerable entitiesToAdd) where TEntity : class
    {
    return ((DbSet)dbset).AddRange(entitiesToAdd);
    }

    Thanks for all you do.

    Jeff

Leave a Reply

Your email address will not be published. Required fields are marked *