A common challenge in developing database solutions is the insertion and updating of master data. By master data, I mean information such as titles („Mr.“, „Ms.“, „Prof.“, etc.), product categories („Food“, „Tools“, „Services“), or the list of all countries in the world – useful, for example, in address capturing.
Staying up-to-date with master data without much effort
How can you get this data into the database and keep it current without a high level of effort?
In this example, I will show you how all the countries of the world with their official abbreviation codes are automatically entered into a table when creating the database — either during the initial creation or during a migration of the database. You will learn how to further simplify the work involved in database creation and migrations in another blog post.
Using Entity Framework for easy master data collection
Fortunately, Entity Framework provides helpful functions, which I would like to demonstrate through my code.
For this example, I have stored a list of all countries in a class with constants. I generated the list of all countries and their abbreviations using GitHub Copilot — so if there are any errors, please blame Copilot, not me 😉
public static class Countries
{
public static Dictionary<string, string> All = new Dictionary<string, string>
{
{ "Afghanistan", "AF" },
{ "Albania", "AL" },
{ "Algeria", "DZ" },
{ "Andorra", "AD" },
{ "Angola", "AO" },
{ "Antigua and Barbuda", "AG" },
{ "Argentina", "AR" },
{ "Armenia", "AM" },
{ "Australia", "AU" },
{ "Austria", "AT" },
{ "Azerbaijan", "AZ" },
{ "Bahamas", "BS" },
{ "Bahrain", "BH" },
{ "Bangladesh", "BD" },
{ "Barbados", "BB" },
{ "Belarus", "BY" },
{ "Belgium", "BE" },
{ "Belize", "BZ" },
{ "Benin", "BJ" },
{ "Bhutan", "BT" },
{ "Bolivia", "BO" },
{ "Bosnia and Herzegovina", "BA" },
{ "Botswana", "BW" },
{ "Brazil", "BR" },
{ "Brunei", "BN" },
{ "Bulgaria", "BG" },
{ "Burkina Faso", "BF" },
{ "Burundi", "BI" },
{ "Cabo Verde", "CV" },
{ "Cambodia", "KH" },
{ "Cameroon", "CM" },
{ "Canada", "CA" },
{ "Central African Republic", "CF" },
{ "Chad", "TD" },
{ "Chile", "CL" },
{ "China", "CN" },
{ "Colombia", "CO" },
{ "Comoros", "KM" },
{ "Congo, Democratic Republic of the", "CD" },
{ "Congo, Republic of the", "CG" },
{ "Costa Rica", "CR" },
{ "Croatia", "HR" },
{ "Cuba", "CU" },
{ "Cyprus", "CY" },
{ "Czech Republic", "CZ" },
{ "Denmark", "DK" },
{ "Djibouti", "DJ" },
{ "Dominica", "DM" },
{ "Dominican Republic", "DO" },
{ "Ecuador", "EC" },
{ "Egypt", "EG" },
{ "El Salvador", "SV" },
{ "Equatorial Guinea", "GQ" },
{ "Eritrea", "ER" },
{ "Estonia", "EE" },
{ "Eswatini", "SZ" },
{ "Ethiopia", "ET" },
{ "Fiji", "FJ" },
{ "Finland", "FI" },
{ "France", "FR" },
{ "Gabon", "GA" },
{ "Gambia", "GM" },
{ "Georgia", "GE" },
{ "Germany", "DE" },
{ "Ghana", "GH" },
{ "Greece", "GR" },
{ "Grenada", "GD" },
{ "Guatemala", "GT" },
{ "Guinea", "GN" },
{ "Guinea-Bissau", "GW" },
{ "Guyana", "GY" },
{ "Haiti", "HT" },
{ "Honduras", "HN" },
{ "Hungary", "HU" },
{ "Iceland", "IS" },
{ "India", "IN" },
{ "Indonesia", "ID" },
{ "Iran", "IR" },
{ "Iraq", "IQ" },
{ "Ireland", "IE" },
{ "Israel", "IL" },
{ "Italy", "IT" },
{ "Jamaica", "JM" },
{ "Japan", "JP" },
{ "Jordan", "JO" },
{ "Kazakhstan", "KZ" },
{ "Kenya", "KE" },
{ "Kiribati", "KI" },
{ "Korea, North", "KP" },
{ "Korea, South", "KR" },
{ "Kosovo", "XK" },
{ "Kuwait", "KW" },
{ "Kyrgyzstan", "KG" },
{ "Laos", "LA" },
{ "Latvia", "LV" },
{ "Lebanon", "LB" },
{ "Lesotho", "LS" },
{ "Liberia", "LR" },
{ "Libya", "LY" },
{ "Liechtenstein", "LI" },
{ "Lithuania", "LT" },
{ "Luxembourg", "LU" },
{ "Madagascar", "MG" },
{ "Malawi", "MW" },
{ "Malaysia", "MY" },
{ "Maldives", "MV" },
{ "Mali", "ML" },
{ "Malta", "MT" },
{ "Marshall Islands", "MH" },
{ "Mauritania", "MR" },
{ "Mauritius", "MU" },
{ "Mexico", "MX" },
{ "Micronesia", "FM" },
{ "Moldova", "MD" },
{ "Monaco", "MC" },
{ "Mongolia", "MN" },
{ "Montenegro", "ME" },
{ "Morocco", "MA" },
{ "Mozambique", "MZ" },
{ "Myanmar", "MM" },
{ "Namibia", "NA" },
{ "Nauru", "NR" },
{ "Nepal", "NP" },
{ "Netherlands", "NL" },
{ "New Zealand", "NZ" },
{ "Nicaragua", "NI" },
{ "Niger", "NE" },
{ "Nigeria", "NG" },
{ "North Macedonia", "MK" },
{ "Norway", "NO" },
{ "Oman", "OM" },
{ "Pakistan", "PK" },
{ "Palau", "PW" },
{ "Palestine", "PS" },
{ "Panama", "PA" },
{ "Papua New Guinea", "PG" },
{ "Paraguay", "PY" },
{ "Peru", "PE" },
{ "Philippines", "PH" },
{ "Poland", "PL" },
{ "Portugal", "PT" },
{ "Qatar", "QA" },
{ "Romania", "RO" },
{ "Russia", "RU" },
{ "Rwanda", "RW" },
{ "Saint Kitts and Nevis", "KN" },
{ "Saint Lucia", "LC" },
{ "Saint Vincent and the Grenadines", "VC" },
{ "Samoa", "WS" },
{ "San Marino", "SM" },
{ "Sao Tome and Principe", "ST" },
{ "Saudi Arabia", "SA" },
{ "Senegal", "SN" },
{ "Serbia", "RS" },
{ "Seychelles", "SC" },
{ "Sierra Leone", "SL" },
{ "Singapore", "SG" },
{ "Slovakia", "SK" },
{ "Slovenia", "SI" },
{ "Solomon Islands", "SB" },
{ "Somalia", "SO" },
{ "South Africa", "ZA" },
{ "South Sudan", "SS" },
{ "Spain", "ES" },
{ "Sri Lanka", "LK" },
{ "Sudan", "SD" },
{ "Suriname", "SR" },
{ "Sweden", "SE" },
{ "Switzerland", "CH" },
{ "Syria", "SY" },
{ "Taiwan", "TW" },
{ "Tajikistan", "TJ" },
{ "Tanzania", "TZ" },
{ "Thailand", "TH" },
{ "Timor-Leste", "TL" },
{ "Togo", "TG" },
{ "Tonga", "TO" },
{ "Trinidad and Tobago", "TT" },
{ "Tunisia", "TN" },
{ "Turkey", "TR" },
{ "Turkmenistan", "TM" },
{ "Tuvalu", "TV" },
{ "Uganda", "UG" },
{ "Ukraine", "UA" },
{ "United Arab Emirates", "AE" },
{ "United Kingdom", "GB" },
{ "United States", "US" },
{ "Uruguay", "UY" },
{ "Uzbekistan", "UZ" },
{ "Vanuatu", "VU" },
{ "Vatican City", "VA" },
{ "Venezuela", "VE" },
{ "Vietnam", "VN" },
{ "Yemen", "YE" },
{ "Zambia", "ZM" },
{ "Zimbabwe", "ZW" }
};
}My DbContext looks as follows:
pulic class AdminDbContext : DbContext
{
public AdminDbContext(DbContextOptions<AdminDbContext> options)
: base(options)
{
}
public DbSet<GroupEntity> Groups { get; init; } = default!;
public DbSet<MemberEntity> Members { get; init; } = default!;
public DbSet<CountryEntity> Countries { get; init; } = default!;
}My DbContext looks as follows:
[EntityTypeConfiguration(typeof(CountryEntityConfiguration))]
public class CountryEntity
{
[Key]
[StringLength(2)]
public string Id { get; set; } = default!;
public string Name { get; set; } = default!;
}This entity consists of only two properties, namely the Id and the name of the country. The Id key is simultaneously the Country Code and must never be longer than two characters. The most interesting part of this class lies in the attribute EntityTypeConfiguration. This attribute accepts another class as its type: CountryEntityConfiguration.
In the file CountryEntityConfiguration, the entity is described in detail. This combination does exactly what has previously been done in the OnModelCreating event in the DbContext.
public class CountryEntityConfiguration: IEntityTypeConfiguration<CountryEntity>
{
public void Configure(EntityTypeBuilder<CountryEntity> builder)
{
string tableName = "Countries";
builder.ToTable(tableName, "dbo");
// Unique Index
// No duplicate Country Name allowed
builder
.HasIndex(c => c.Name)
.IsUnique();
// Seeding All Countries
List<CountryEntity> countryEntities = [];
foreach (var country in Countries.All)
{
CountryEntity countryEntity = new()
{
Id = country.Value,
Name = country.Key
};
countryEntities.Add(countryEntity);
}
builder.HasData(
countryEntities
);
}
}By using this, if you create a migration or regenerate the database using DbContext.Database.EnsureCreated(), you will get a populated table with all the countries of the world.
I hope you learned something new, happy auto-fill your database with entity framework.
