Introduction
The main objective is to demonstrate creating data for Microsoft EF Core that is the same every time the application runs and/or unit test run.
The secondary objective is to show how to use IOptions and AddTransient to read information from appsettings.json which is not gone over completely so if interested see the following GitHub repository which has code samples for console and web projects.
Walkthrough
In the source code, the first steps are to read the database connection string and a setting to decide to create a fresh copy of the database from appsettings.json.
appsetting.json
“ConnectionStrings”: {
“MainConnection”: “Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Bogus2;Integrated Security=True;Encrypt=False”,
“SecondaryConnection”: “TODO”
},
“EntityConfiguration”: {
“CreateNew”: false
}
}
In Program.cs, the following code sets up to read appsettings.json data.
{
var services = ApplicationConfiguration.ConfigureServices();
await using var serviceProvider = services.BuildServiceProvider();
serviceProvider.GetService<SetupServices>()!.GetConnectionStrings();
serviceProvider.GetService<SetupServices>()!.GetEntitySettings();
}
Both connection string and application settings use singleton classes to access information in various parts of the program.
{
private static readonly Lazy<DataConnections> Lazy = new(() => new DataConnections());
public static DataConnections Instance => Lazy.Value;
public string MainConnection { get; set; }
public string SecondaryConnection { get; set; }
}
public sealed class EntitySettings
{
private static readonly Lazy<EntitySettings> Lazy = new(() => new EntitySettings());
public static EntitySettings Instance => Lazy.Value;
/// <summary>
/// Indicates if the database should be recreated
/// </summary>
public bool CreateNew { get; set; }
}
Next, in Program.Main method
Instantiate an instance of the DbContext
EntitySettings.Instance.CreateNew determines if the database should be created fresh.
{
await Setup();
await using var context = new Context();
if (EntitySettings.Instance.CreateNew)
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
}
. . .
}
Database details.
There is one table BirthDays which
YearsOld column is a computed column
BirthDate is a date column, C# side a DateOnly
Gender is a string column, C# side an enum
[Id] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (MAX) NULL,
[LastName] NVARCHAR (MAX) NULL,
[Gender] NVARCHAR (MAX) NOT NULL,
[BirthDate] DATE NULL,
[YearsOld] AS ((CONVERT([int],format(getdate(),‘yyyyMMdd’))–CONVERT([int],format([BirthDate],‘yyyyMMdd’)))/(10000)),
[Email] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_BirthDays] PRIMARY KEY CLUSTERED ([Id] ASC)
);
DbContext
OnConfiguring the connection is setup, read from DataConnections _ class and _DbContextToFileLogger is setup and responsible for logging all EF Core operations to a daily log file beneath the application.
OnModelCreating setup a conversion for Gender property and setup the computed column.
Note
The Gender enum is setup as the same as Gender in Bogus, more on this later.
Next, the final part determines if Bogus data should be used and will only happen when testing, not for production so be mindful to change the setting in appsetting.json before moving to production.
{
public Context()
{
}
public Context(DbContextOptions<Context> options)
: base(options)
{
}
public virtual DbSet<BirthDays> BirthDays { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
/*
* Get connection string from appsetting.json
* Setup logging to a file under the app folder (see the project file for folder creation)
*/
optionsBuilder.UseSqlServer(DataConnections.Instance.MainConnection)
.EnableSensitiveDataLogging()
.LogTo(new DbContextToFileLogger().Log, new[]
{
DbLoggerCategory.Database.Command.Name
},
LogLevel.Information);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<BirthDays>(entity =>
{
// setup enum conversion
entity.Property(e => e.Gender)
.HasConversion<int>()
.IsRequired();
// setup computed column
entity.Property(e => e.YearsOld)
.HasComputedColumnSql(“((CONVERT([int],format(getdate(),’yyyyMMdd’))-CONVERT([int],format([BirthDate],’yyyyMMdd’)))/(10000))”, false);
});
if (EntitySettings.Instance.CreateNew)
{
modelBuilder.Entity<GenderData>().HasData(BogusOperations.GenderTypes());
modelBuilder.Entity<BirthDays>().HasData(new List<BirthDays>(BogusOperations.PeopleList(20,338)));
}
}
}
BirthDays model
There are two constructors, the overload which passes an int is for use when creating data with Bogus.
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
/// <summary>
/// Person gender
/// </summary>
public Gender Gender { get; set; }
public DateOnly? BirthDate { get; set; }
// computered column, see DbContext OnModelCreating
public int? YearsOld { get; set; }
public string Email { get; set; }
// For Bogus to set Id property
public BirthDays(int id)
{
Id = id;
}
public BirthDays()
{
}
}
Creating Bogus data
To create consistent data with Bogus, the Faker must be seeded using Randomizer.Seed = new Random(338) where 338 can be any number but in some cases the seed in tangent with count of instances of the (any) model may not align first names with the proper gender so play with the number if matching first names to the proper gender.
In the image below, the arrows mark the code which attempt to match first names with the proper gender.
Both methods produce the same results, its a matter of preference which to use.
Third party NuGet packages uses
Package
Purpose
Bogus
For creating mocked data
ConfigurationLibrary
Provides access to appsettings.json for connection strings for three environments, development, testing/staging and production.
EntityCodeFileLogger
A simple class to log EF Core operations to a text file
EF Power Tools
EF Power Tools was used to reverse engineer the original database.
