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<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;

/// <summary>
/// Provides functionality for managing entity settings, including reading configuration
/// from the "appsettings.json" file to determine whether a new entity should be created.
/// </summary>
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();
    }
    /// <summary>
    /// Reads the "appsettings.json" file and determines whether a new entity should be created
    /// based on the "CreateNew" property in the "EntityConfiguration" section.
    /// </summary>
    /// <returns>
    /// <c>true</c> if the "CreateNew" property in the "EntityConfiguration" section is set to <c>true</c>;
    /// otherwise, <c>false</c>.
    /// </returns>
    /// <exception cref="Exception">
    /// Thrown when the "EntityConfiguration" section is not found in the "appsettings.json" file.
    /// </exception>
    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
{
    /// <summary>
    /// Ensures that the database is properly initialized by deleting and recreating it
    /// if the <see cref="EntitySettings.CreateNew"/> property is set to <c>true</c>.
    /// </summary>
    /// <param name="context">
    /// The <see cref="Context"/> instance representing the database context.
    /// </param>
    public static async Task CreateIfNeeded(Context context)
    {
        if (!EntitySettings.Instance.GenerateData) return;
        await context.Database.EnsureDeletedAsync();
        await context.Database.EnsureCreatedAsync();
    }
    /// <summary>
    /// Gets the predefined list of <see cref="Category"/> objects used to seed the database.
    /// </summary>
    /// <remarks>
    /// 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 <c>Categories</c> table.
    /// </remarks>
    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" }
    ];

    /// <summary>
    /// Gets a predefined list of <see cref="Product"/> instances representing mock data
    /// for use in database initialization or testing scenarios.
    /// </summary>
    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.

See also NuGet Local feeds

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, using 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.

💡 A quick way to add a reference.

Select PayneServiceLibrary project and drag and drop to another project.

Add the following section to appsettings.json

"EntityConfiguration": {
"CreateNew": true
}

• Call await MainConfiguration.Setup(); at start of a project which reads the above setting.

Storing database connection

{
  "ConnectionStrings": {
    "MainConnection": "...",
    "SecondaryConnection": "..."
  },
  "EntityConfiguration": {
    "CreateNew": true
  }
}

Using the above, MainConnection would be for connecting to production, while SecondaryConnection would be for testing. Or an application may require two different databases.

💡 If the names MainConnection and SecondaryConnection do not work for you feel free to change them.

• To access the main connection string use DataConnections.Instance.MainConnection

Example where in this case Sqlite DbContext is shown.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlite(DataConnections.Instance.MainConnection);

SQL-Server

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer(DataConnections.Instance.MainConnection);

The data provider does not matter which means a developer working with Oracle can use the same as shown for the two providers above.

DataConnections class is a singleton.

public sealed class DataConnections
{
    private static readonly Lazy<DataConnections> Lazy = new(() => new DataConnections());
    public static DataConnections Instance => Lazy.Value;

    /// <summary>
    /// Gets or sets the main connection string used by the application.
    /// </summary>
    /// <remarks>
    /// This property holds the primary connection string required for database operations. 
    /// It is typically initialized from the application's configuration settings and 
    /// accessed via the singleton instance of <see cref="DataConnections"/>.
    /// </remarks>
    public string MainConnection { get; set; }

    public string SecondaryConnection { get; set; }
}

DataConnections class is populated by the following class.

internal class SetupServices
{
    private readonly EntityConfiguration _settings;
    private readonly ConnectionStrings _options;


    public SetupServices(IOptions<ConnectionStrings> options, IOptions<EntityConfiguration> settings)
    {
        _options = options.Value;
        _settings = settings.Value;
    }

    public void GetConnectionStrings()
    {
        DataConnections.Instance.MainConnection = _options.MainConnection;
        DataConnections.Instance.SecondaryConnection = _options.SecondaryConnection;
    }

    public void GetEntitySettings()
    {
        if (JsonHelpers.EntityConfigurationSectionExists())
        {
            EntitySettings.Instance.CreateNew = _settings.CreateNew;
        }
        else
        {
            EntitySettings.Instance.CreateNew = false;
        }

    }
}

Example 3 ASP.NET Core

Configuration in Program.cs

public class Program
{
    public static async Task Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);

        /*
         * Setup for connection string and mocking data
         */
        await MainConfiguration.Setup();

        builder.Services.AddRazorPages();

        /*
         * Connection string from appsettings.json
         * Sensitive data logging enabled
         * Logs to a text file
         */
        builder.Services.AddDbContext<Context>(options =>
            options.UseSqlServer(DataConnections.Instance.MainConnection)
                .EnableSensitiveDataLogging()
                .LogTo(action: new DbContextToFileLogger().Log));


        SetupLogging.Development();
        var app = builder.Build();
...

Index.cshtml.cs

public class IndexModel(Context context) : PageModel
{
    private readonly Context _context = context;
    public List<ProductGroup> GroupedProducts { get; set; } = new();
    public async Task OnGet()
    {
        await MockedData.CreateIfNeeded(_context);

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

        GroupedProducts = products
            .GroupBy(p => p.Category)
            .Select(group => 
                new ProductGroup(group.Key, group.OrderBy(x => x.Name).ToList()))
            .OrderBy(x => x.Category.Name)
            .ToList();
    }
}

Source code

ASP.NET Core sample (SQL-Server)

Console (Sqlite) sample

WPF (Sqlite) sample

PayneServiceLibrary

Summary

Using the information provided, developers can switch between using mocked data for EF Core and storing connection strings in appsettings.json for desktop projects.

Using JSON or mocked data in code depends on business requirements. For example, testers need to be able to modify the data.

Using a class project for uniformity across all projects using EF Core along with ease of maintenance of code being in one place.

The class project PayneServiceLibrary can be renamed if so desired, as there are no copyrights on this project.