Code-First EF Core Lookup Table and Blazor

A lookup table in your Entity Framework Core database can be a more robust alternative to a simple enum when working with code-first C# data models. In this tutorial, you will learn how to create a lookup table and use its contents to populate a dropdown list in a Blazor application.

Creating the Model

Suppose you have an application that stores data about local businesses. The model for your Business class might include the the following basic identifying properties.

public class Business
{
    public int Id { get; set; }
    public string BusinessName { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }
    public int? CategoryId { get; set; }
    public Category Category { get; set; }
}

The navigation property with name Category derives from the Category class, defined below. The integer CategoryId serves as a foreign key for Category. Let's create a data model for Category. Add a class named Category.cs to your project. The lookup table model will contain two properties, an ID, which will serve as the key for the database, and the category Name.

public class Category
{
	public int Id { get; set; }
	public string Name { get; set; }
}

When the Business.Category is expanded, your application will have access to the category's Name.

Configuring EF Core Database Context

Now that you have created the model, you need to configure the database context, so Entity Framework can use it. First, add the following line to DbContext.

public DbSet<Category> Categories { get; set; }

Then, in OnModelCreating of DbContext, add a table to store the list of categories.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	...
    modelBuilder.Entity<Category>().ToTable("Categories");
	...
}

Seed the Lookup Table

You have several options for populating the lookup table. You could create an API endpoint and POST the categories to the database. This is a great solution, if you wish to update the list periodically.

In this tutorial, you will simply seed the table when the database is created at compile time. For example, you might have DbInitializer class with Initialize() method that you launch from the Main() method of Program.cs.

DbInitializer.Initialize(context);

Within there, you could use the following data to seed the lookup table.

if (context.Categories.Any())
{
    return;   // DB has been seeded
}
 
var categories = new Category[]
{
    new Category
    {
        Name="Banking and Finance"
    },
    new Category
    {
        Name="Fuel and Transportation"
    },
    new Category
    {
        Name="Hotels and Lodging"
    },
    new Category
    {
        Name="Restaurants"
    },
    new Category
    {
        Name="Shopping and Retail"
    }
};
 
foreach (Category category in categories)
{
    context.Categories.Add(category);
}
context.SaveChanges();

Lookup Table vs Enum

An Enum is a useful construct that assigns a constant to an integer value. An equivalent enum for the Category class might look like the following.

public enum Category
{
    BankingFinance,
    FuelTransportation,
    HotelsLodging,
    Restaurants,
    ShoppingRetail
}

Notice, the enumeration is hard-coded. There is no way to programmatically change the values of an enum. A lookup table, by contrast, provides the flexibility of creating an interface for modifying the list at runtime.

In addition, the constant value cannot include spaces or special characters. Displaying the contents of an enum in a friendly, readable way can, thus, be tricky. There is an annotation property in the System.Runtime.Serialization namespace that helps provide user-friendly display names.

[EnumMember(Value = "Banking and Finance")]
BankingFinance

Unfortunately, as of .NET Core 3.1, the EnumMember attribute does not yet work with the System.Text.Json namespace. The Name property of a lookup table, by contrast, provides the flexibility of a string data type, something a simple enumeration lacks.

Populate Dropdown from Lookup Table

Once you set up an API in your Blazor web app to fetch the list of Categories, you can populate a dropdown list by binding to the CategoryId property.

<div class="form-group row">
    <label for="businessCategory" class="col-sm-6 col-form-label">Category:</label>
    <select id="businessCategory" class="form-control col-sm-6 input-lg" @bind="business.CategoryId">
        <option value="" disabled selected>Select Category</option>
        @if (categories != null)
        {
            @foreach (var category in categories)
            {
                <option value="@category.Id">@category.Name</option>
            }
        }
    </select>
</div>