EF Core prototyping

Introduction

When designing a database schema, it's a good idea to load the development database with mocked data and run various SELECT, UPDATE, DELETE, and INSERT statements to ensure the schema fulfills business requirements.

Data provider

Microsoft SQL Server is used in the provided source code.

Prototype project

Source code in BaseContextExampleApp project

Create a Console project with an appsettings.json file containing the development database connection string and a method to read it.

Here, the connection string in appsettings came from an ASP.NET Core project, which will be used for the database. ASP.NET Core-specific settings are not needed and do not harm anything.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MainConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=BirthDaysDatabase;Integrated Security=True;Encrypt=False"
  }
}

The connection string will be used in a custom class below (included in the source code in a class project).

public abstract class ProtoTypeContext() : DbContext(BuildOptions())
{
    private static DbContextOptions BuildOptions()
    {
        var builder = new DbContextOptionsBuilder()
            .UseSqlServer(AppConnections.Instance.MainConnection);

        // NOTE - adjust based on your environment variable setup
        var env = Environment.GetEnvironmentVariable("CONSOLE_ENVIRONMENT") ?? "Production";

        if (env.Equals("Development", StringComparison.OrdinalIgnoreCase))
        {
            builder.EnableSensitiveDataLogging()
                .LogTo(message => Debug.WriteLine(message), LogLevel.Information);
        }

        return builder.Options;
    }
}

Add appropriate EF Core NuGet packages to the console project.

Configure the database e.g. code first or database first.

Add the class project EnityLibrary as a project reference to the console project.

Alter the DbContext from

public partial class Context : DbContext

To

public partial class Context : ProtoTypeContext

ℹ️ The PrototypeContext class provides a consistent way for each prototype or any console project that does not need dependency injection.

Differences between console and ASP.NET Core projects

The differences are very minor meaning that going from console to ASP.NET Core are easy.

Write EF Core statements

When writing code, consider writing for each model in its own class to keep it clean. Once you are satisfied, you can move to the ASP.NET Core project.

ASP.NET Core project

Source code in BirthDaysApp project

Registering the DbContext

builder.Services.AddDbContext<Context>(options => options.UseSqlServer(
    builder.Configuration.GetConnectionString("MainConnection"))); 

Summary

When used for all prototyping, the approach presented provides a repeatable pattern and consistency, rather than handling each project differently.

Class project Console project ASP.NET Core project