Damir's Corner.

Loading data from JSON in EF Core

The navigation properties in Entity Framework Core models are not only useful for reading hierarchical data from database, but can also very much simplify importing hierarchical data from other sources like JSON files.

Imagine having a structured multi-level JSON like this:

[
  {
    "title": "The Witcher 3: Wild Hunt",
    "developer": "CD Projekt RED",
    "dlcs": [
      {
        "title": "Base game",
        "achievements": [
          // ...
        ]
      },
      {
        "title": "Hearts of Stone",
        "achievements": [
          // ...
        ]
      },
      {
        "title": "Blood and Wine",
        "achievements": [
          // ...
        ]
      }
    ]
  }
]

In a relational database, you would model such data with three tables, one for each level of hierarchy:

public class AchievementsDbContext : DbContext
{
    public DbSet<Game> Games => Set<Game>();
    public DbSet<Dlc> Dlcs => Set<Dlc>();
    public DbSet<Achievement> Achievements => Set<Achievement>();
}

To deserialize the sample data above, there must be collection properties for child objects inside every parent object. In EF Core models, these will be navigation properties:

public class Game
{
    public int GameId { get; set; }
    public string Title { get; set; }
    public string Developer { get; set; }
    public virtual List<Dlc> Dlcs { get; set; } = null!;
}

public class Dlc
{
    public int DlcId { get; set; }
    public int GameId { get; set; }
    public virtual Game Game { get; set; } = null!;
    public string Title { get; set; }
    public virtual List<Achievement> Achievements { get; set; } = null!;
}

public class Achievement
{
    public int AchievementId { get; set; }
    public int DlcId { get; set; }
    public virtual Dlc Dlc { get; set; } = null!;
    public string Name { get; set; }
    public string Description { get; set; }
    public int Gamerscore { get; set; }
}

With these models defined, the JSON file can simply be read into a list of games. All child collections (DLCs and achievements) will automatically be read correctly as well:

var options = new JsonSerializerOptions
{
    PropertyNamingPolicy = JsonNamingPolicy.CamelCase
};

var json = await File.ReadAllTextAsync("achievements.json");
var games = JsonSerializer.Deserialize<List<Game>>(json, options);

What came as a slight surprise for me, was how easy it is to persist the whole data structure to the database:

var context = new AchievementsDbContext();
context.Games.AddRange(games);
await context.SaveChangesAsync();

Yes, only the parent objects need to be explicitly added to the database context. For the child objects, it's enough that they are contained in the collection navigation properties, and they will be persisted along with their parents. This works for the whole multi-level hierarchy.

You can find a working project with a sample JSON file in my GitHub repository.

Many EF Core functionalities can be creatively used to make tasks easier to implement, even if they aren't directly related to database access. This post demonstrates how EF Core can make importing data from structured JSON files into database almost trivial.