Programming

EF Core HasData

Introduction Data seeding can be used to set initial data or for testing purposes. Learn about seeding a database with EF Core using a switch in the project configuration file. There are several methods to accomplish controlling data seeding. Comment out the code to generate data Setup code that reads from appsettings.json or a similar file. Use an environment variable. Here, the focus is on using the appsettings.json configuration file to control generating test data, starting with a simple approach to standard code in a class project that can be referenced in a project or from publishing standard code in a class project to a remote or local NuGet package. Models The following classes are used for all examples. public class Category { public int CategoryId { get; set; } public string Name { get; set; } public virtual List Products { get; } = []; public override string ToString() => Name; } public class Product { public int ProductId { get; set; } public string Name { get; set; } public int CategoryId { get; set; } public virtual Category Category { get; set; } = null!; public override string ToString() => Name; } Sample projects Project name Description Provider ReadEntitySettings Populates database tables using JSON files. Sqlite ConventionalRead Simple example for reading string from appsettings.json using NuGet package ConsoleConfigurationLibrary which has an alias in the project file. Note Config.Configuration.JsonRoot() Config is defined in the project file as an alias. None SqliteHasData Populates database tables using mocked data in code rather than using JSON files. Sqlite SqlServerHasData Populates database tables using mocked data in code rather than using JSON files. SQL-Server WpfHasData Populates database tables using mocked data in code rather than using JSON files. Sqlite RazorHasData ASP.NET Core using mocked data in code SQL-Server Example 1 using data in code In this example, a singleton class reads a section from appsettings.json. { "EntityConfiguration": { "CreateNew": true } } Using the following model. public class EntityConfiguration { public bool CreateNew { get; set; } } The singleton class reads from appsettings.json in the constructor, so the read operation happens once. using System.Text.Json; using ReadEntitySettings.Models; namespace ReadEntitySettings.Classes; /// /// Provides functionality for managing entity settings, including reading configuration /// from the "appsettings.json" file to determine whether a new entity should be created. /// public sealed class EntitySettings { private static readonly Lazy Lazy = new(() => new EntitySettings()); public static EntitySettings Instance => Lazy.Value; public bool GenerateData { get; set; } private EntitySettings() { GenerateData = CreateNew(); } /// /// Reads the "appsettings.json" file and determines whether a new entity should be created /// based on the "CreateNew" property in the "EntityConfiguration" section. /// /// /// true if the "CreateNew" property in the "EntityConfiguration" section is set to true; /// otherwise, false. /// /// /// Thrown when the "EntityConfiguration" section is not found in the "appsettings.json" file. /// private static bool CreateNew() { string json = File.ReadAllText("appsettings.json"); using JsonDocument doc = JsonDocument.Parse(json); JsonElement root = doc.RootElement; if (root.TryGetProperty(nameof(EntityConfiguration), out var entityConfig)) { var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true }; var settings = JsonSerializer.Deserialize(entityConfig.GetRawText(), options); return settings.CreateNew; } throw new Exception($"{nameof(EntityConfiguration)} section not found in appsettings.json"); } } The following is used when the application needs to know whether to create the database and populate tables. EnsureDeletedAsync will delete the database if exists and EnsureCreatedAsync creates the database and all tables. internal class MockedData { /// /// Ensures that the database is properly initialized by deleting and recreating it /// if the property is set to true. /// /// /// The instance representing the database context. /// public static async Task CreateIfNeeded(Context context) { if (!EntitySettings.Instance.GenerateData) return; await context.Database.EnsureDeletedAsync(); await context.Database.EnsureCreatedAsync(); } /// /// Gets the predefined list of objects used to seed the database. /// /// /// This property provides a static collection of categories, each with a unique identifier and name. /// It

Feb 2, 2025 - 23:09

Introduction

Data seeding can be used to set initial data or for testing purposes.

Learn about seeding a database with EF Core using a switch in the project configuration file.

There are several methods to accomplish controlling data seeding.

Comment out the code to generate data
Setup code that reads from appsettings.json or a similar file.
Use an environment variable.

Here, the focus is on using the appsettings.json configuration file to control generating test data, starting with a simple approach to standard code in a class project that can be referenced in a project or from publishing standard code in a class project to a remote or local NuGet package.

Models

The following classes are used for all examples.

public class Category
{
    public int CategoryId { get; set; }

    public string Name { get; set; }

    public virtual List<Product> Products { get; } = [];
    public override string ToString() => Name;

}

public class Product
{
    public int ProductId { get; set; }

    public string Name { get; set; }

    public int CategoryId { get; set; }
    public virtual Category Category { get; set; } = null!;
    public override string ToString() => Name;
}

Sample projects

Project name	Description	Provider
ReadEntitySettings	Populates database tables using JSON files.	Sqlite
ConventionalRead	Simple example for reading string from appsettings.json using NuGet package `ConsoleConfigurationLibrary` which has an alias in the project file. Note Config.Configuration.JsonRoot() `Config` is defined in the project file as an alias.	None
SqliteHasData	Populates database tables using mocked data in code rather than using JSON files.	Sqlite
SqlServerHasData	Populates database tables using mocked data in code rather than using JSON files.	SQL-Server
WpfHasData	Populates database tables using mocked data in code rather than using JSON files.	Sqlite
RazorHasData	ASP.NET Core using mocked data in code	SQL-Server

Example 1 using data in code

In this example, a singleton class reads a section from appsettings.json.

{
  "EntityConfiguration": {
    "CreateNew": true
  }
}

Using the following model.

public class EntityConfiguration
{
    public bool CreateNew { get; set; }
}

The singleton class reads from appsettings.json in the constructor, so the read operation happens once.

using System.Text.Json;
using ReadEntitySettings.Models;

namespace ReadEntitySettings.Classes;

/// 
/// Provides functionality for managing entity settings, including reading configuration
/// from the "appsettings.json" file to determine whether a new entity should be created.
/// 
public sealed class EntitySettings
{
    private static readonly Lazy<EntitySettings> Lazy = new(() => new EntitySettings());
    public static EntitySettings Instance => Lazy.Value;

    public bool GenerateData { get; set; }
    private EntitySettings()
    {
        GenerateData = CreateNew();
    }
    /// 
    /// Reads the "appsettings.json" file and determines whether a new entity should be created
    /// based on the "CreateNew" property in the "EntityConfiguration" section.
    /// 
    /// 
    /// true if the "CreateNew" property in the "EntityConfiguration" section is set to true;
    /// otherwise, false.
    /// 
    /// 
    /// Thrown when the "EntityConfiguration" section is not found in the "appsettings.json" file.
    /// 
    private static bool CreateNew()
    {

        string json = File.ReadAllText("appsettings.json");
        using JsonDocument doc = JsonDocument.Parse(json);

        JsonElement root = doc.RootElement;

        if (root.TryGetProperty(nameof(EntityConfiguration), out var entityConfig))
        {
            var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
            var settings = JsonSerializer.Deserialize<EntityConfiguration>(entityConfig.GetRawText(), options);
            return settings.CreateNew;
        }

        throw new Exception($"{nameof(EntityConfiguration)} section not found in appsettings.json");

    }
}

The following is used when the application needs to know whether to create the database and populate tables.

EnsureDeletedAsync will delete the database if exists and EnsureCreatedAsync creates the database and all tables.

internal class MockedData
{
    /// 
    /// Ensures that the database is properly initialized by deleting and recreating it
    /// if the  property is set to true.
    /// 
    /// 
    /// The  instance representing the database context.
    /// 
    public static async Task CreateIfNeeded(Context context)
    {
        if (!EntitySettings.Instance.GenerateData) return;
        await context.Database.EnsureDeletedAsync();
        await context.Database.EnsureCreatedAsync();
    }
    /// 
    /// Gets the predefined list of  objects used to seed the database.
    /// 
    /// 
    /// This property provides a static collection of categories, each with a unique identifier and name.
    /// It is utilized during the database initialization process to populate the Categories table.
    /// 
    public static List<Category> Categories { get; } =
    [
        new Category { CategoryId = 1, Name = "Cheese" },
        new Category { CategoryId = 2, Name = "Meat" },
        new Category { CategoryId = 3, Name = "Fish" },
        new Category { CategoryId = 4, Name = "Bread" }
    ];

    /// 
    /// Gets a predefined list of  instances representing mock data
    /// for use in database initialization or testing scenarios.
    /// 
    public static List<Product> Products { get; } =
    [
        new Product { ProductId = 1, CategoryId = 1, Name = "Cheddar" },
        new Product { ProductId = 2, CategoryId = 1, Name = "Brie" },
        new Product { ProductId = 3, CategoryId = 1, Name = "Stilton" },
        new Product { ProductId = 4, CategoryId = 1, Name = "Cheshire" },
        new Product { ProductId = 5, CategoryId = 1, Name = "Swiss" },
        new Product { ProductId = 6, CategoryId = 1, Name = "Gruyere" },
        new Product { ProductId = 7, CategoryId = 1, Name = "Colby" },
        new Product { ProductId = 8, CategoryId = 1, Name = "Mozzela" },
        new Product { ProductId = 9, CategoryId = 1, Name = "Ricotta" },
        new Product { ProductId = 10, CategoryId = 1, Name = "Parmesan" },
        new Product { ProductId = 11, CategoryId = 2, Name = "Ham" },
        new Product { ProductId = 12, CategoryId = 2, Name = "Beef" },
        new Product { ProductId = 13, CategoryId = 2, Name = "Chicken" },
        new Product { ProductId = 14, CategoryId = 2, Name = "Turkey" },
        new Product { ProductId = 15, CategoryId = 2, Name = "Prosciutto" },
        new Product { ProductId = 16, CategoryId = 2, Name = "Bacon" },
        new Product { ProductId = 17, CategoryId = 2, Name = "Mutton" },
        new Product { ProductId = 18, CategoryId = 2, Name = "Pastrami" },
        new Product { ProductId = 19, CategoryId = 2, Name = "Hazlet" },
        new Product { ProductId = 20, CategoryId = 2, Name = "Salami" },
        new Product { ProductId = 21, CategoryId = 3, Name = "Salmon" },
        new Product { ProductId = 22, CategoryId = 3, Name = "Tuna" },
        new Product { ProductId = 23, CategoryId = 3, Name = "Mackerel" },
        new Product { ProductId = 24, CategoryId = 4, Name = "Rye" },
        new Product { ProductId = 25, CategoryId = 4, Name = "Wheat" },
        new Product { ProductId = 26, CategoryId = 4, Name = "Brioche" },
        new Product { ProductId = 27, CategoryId = 4, Name = "Naan" },
        new Product { ProductId = 28, CategoryId = 4, Name = "Focaccia" },
        new Product { ProductId = 29, CategoryId = 4, Name = "Malted" },
        new Product { ProductId = 30, CategoryId = 4, Name = "Sourdough" },
        new Product { ProductId = 31, CategoryId = 4, Name = "Corn" },
        new Product { ProductId = 32, CategoryId = 4, Name = "White" },
        new Product { ProductId = 33, CategoryId = 4, Name = "Soda" }
    ];
}

Note
When populating data, tables with primary keys generated by the database need their primary keys set. Also, reference tables need to be populated first, as in the above example, the Category table must be populated before the Product table.

In the DbContext, an if statement in OnModelCreating determines if mocked data should be used.

public class Context : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite("Data Source=products.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        if (!EntitySettings.Instance.GenerateData) return;

        modelBuilder.Entity<Category>().HasData(MockedData.Categories);
        modelBuilder.Entity<Product>().HasData(MockedData.Products);

    }
}

Note
The connection string is hard coded, which is generally not a good idea. In later examples, the connection string is read from a configuration file.

Let’s look at using the above code in a console project using the above code.

internal partial class Program
{
    static async Task Main(string[] args)
    {
        await using var context = new Context();
        await MockedData.CreateIfNeeded(context);

        var products = context
            .Products
            .Include(x => x.Category)
            .ToList();

        var groupedProducts = products
            .GroupBy(p => p.Category)
            .Select(group => new
            {
                Category = group.Key,
                Products = group.OrderBy(x => x.Name).ToList()
            })
            .OrderBy(x => x.Category.Name);

        foreach (var group in groupedProducts)
        {
            AnsiConsole.MarkupLine($"[cyan]{group.Category.Name}[/]");
            foreach (var product in group.Products)
            {
                Console.WriteLine($"     {product.Name}");
            }
        }

        Console.ReadLine();
    }
}

In this example, creation and populating data is controlled by a setting in appsettings.json where the code is specified to this project, which means for another project, the developer must copy code to another project which is not conducive to time spent on copying code along with if in time the developer decides to change how they want to perform this operation.

In the next example, a class project will be used, which can be referenced in any project that also includes code to read a connection string. This route requires less refactoring and does not require copying and pasting code from project to project.

Another advantage is the class project can be published as a remote or local NuGet package.

Example 1 using data from a file

In some cases, testers may need to adjust the data, which can be done by reading JSON from a file rather than storing data in code, as shown above.

internal class MockedData
{

    public static async Task CreateIfNeeded(Context context)
    {
        if (!EntitySettings.Instance.GenerateData) return;
        await context.Database.EnsureDeletedAsync();
        await context.Database.EnsureCreatedAsync();
    }

    public static List<Category> CategoriesFromJson 
        => JsonSerializer.Deserialize<List<Category>>(File.ReadAllText("Categories.json"));


    public static List<Product> ProductsFromJson 
        => JsonSerializer.Deserialize<List<Product>>(File.ReadAllText("Products.json"));
}

In the above sample, Categories.json and Products.json have the same exact data as in the first sample. To see this in action, see the project ReadEntitySettings.

One option to generate data safely is to provide a tester with a simple app that connects to a local database and allows editing and exporting to JSON. Advanced testers can edit current JSON with an editor like Microsoft VS-Code.

Example 2

The following example is for developers who will create many projects using EF Core that need an option to toggle using mocked data and data for production.

Feel free to change the project name once added to a Visual Studio Solution.

Using PayneServiceLibrary. Once cloned, use the following commands from the command line.

git init PayneServiceLibrary
cd PayneServiceLibrary
git remote add origin https://github.com/karenpayneoregon/csharp-11-ef-core-7-features.git
git config core.sparseCheckout true
echo "PayneServiceLibrary/*" >> .git/info/sparse-checkout
git pull origin master

Create a new Visual Studio solution, add PayneServiceLibrary.
Publish as a remote or local NuGet feed.

The local feed in the following is on a local drive, but local feeds can also be on a shared drive.

Next, add a reference to a project from a NuGet package, or the alternate is adding PayneServiceLibrary directly to a developer's Visual Studio solution.