I have a model with an enum property. That property has a custom enum to string converter defined in the code-first EF Core configuration. I also have a lookup table class for that enum which also has an EF Core code-first configuration as well as generated seeded data for the enums variants.

I would like the enum column on my model to have a foreign key constraint to the code column in my lookup table.

Here is some example code to show what I would like to do:

My model class:

class Model
{
    public Status Status { get; private set; }
}

My enum:

enum Status
{
    [Code("Active")]
    Active = 1,

    [Code("Pending")]
    Pending = 2,

    [Code("InProgress")]
    InProgress = 3,

    [Code("Complete")]
    Complete = 4
}

My lookup:

class StatusLookup
{
    public required int Id { get; init; }
    public required string Code { get; init; }
}

Lookup table EF Core configuration:

public void Configure(EntityTypeBuilder<StatusLookup> builder)
{
    builder
        .ToTable($"Lookup_Status");

    builder
        .HasKey(t => t.Id);

    builder
        .Property(t => t.Code)
        .HasMaxLength(64)
        .IsRequired();

    // Seed the lookup table
    builder
        .HasData(
            BuildLookupLists() // Uses reflection to build lookup list data
        );
}

Model configuration:

protected override void ConfigureEntity(EntityTypeBuilder<Model> builder)
{
    builder
        .Property(t => t.Status)
        .HasConversion(
            s => s.GetCode(),
            c => EnumFromCode<Status>(c))
        .IsRequired();
    
    // I know this doesn't work, but should signify what I am trying to do
    builder
        .HasOne<StatusLookup>()
        .WithMany()

        // Ideally EF Core would know this is mapping to a string column in the database due
        // to the previous definition saying that it has a string conversion, but this does not work.
        .HasForeignKey(t => t.Status)
        .HasPrincipalKey(t => t.Code)
        .OnDelete(DeleteBehavior.Restrict);
}

The problem with my solution is that EF Core says it can't map between the Status enum type, and the Code string type.

Is what I am trying to do possible?

I want the foreign key constraint on the code as we have situations where data people will go directly into the database and manually add/remove/alter records, and having that constraint on the enum means they know all the valid values, and cannot enter an invalid enum value.

I have a model with an enum property. That property has a custom enum to string converter defined in the code-first EF Core configuration. I also have a lookup table class for that enum which also has an EF Core code-first configuration as well as generated seeded data for the enums variants.

I would like the enum column on my model to have a foreign key constraint to the code column in my lookup table.

Here is some example code to show what I would like to do:

My model class:

class Model
{
    public Status Status { get; private set; }
}

My enum:

enum Status
{
    [Code("Active")]
    Active = 1,

    [Code("Pending")]
    Pending = 2,

    [Code("InProgress")]
    InProgress = 3,

    [Code("Complete")]
    Complete = 4
}

My lookup:

class StatusLookup
{
    public required int Id { get; init; }
    public required string Code { get; init; }
}

Lookup table EF Core configuration:

public void Configure(EntityTypeBuilder<StatusLookup> builder)
{
    builder
        .ToTable($"Lookup_Status");

    builder
        .HasKey(t => t.Id);

    builder
        .Property(t => t.Code)
        .HasMaxLength(64)
        .IsRequired();

    // Seed the lookup table
    builder
        .HasData(
            BuildLookupLists() // Uses reflection to build lookup list data
        );
}

Model configuration:

protected override void ConfigureEntity(EntityTypeBuilder<Model> builder)
{
    builder
        .Property(t => t.Status)
        .HasConversion(
            s => s.GetCode(),
            c => EnumFromCode<Status>(c))
        .IsRequired();
    
    // I know this doesn't work, but should signify what I am trying to do
    builder
        .HasOne<StatusLookup>()
        .WithMany()

        // Ideally EF Core would know this is mapping to a string column in the database due
        // to the previous definition saying that it has a string conversion, but this does not work.
        .HasForeignKey(t => t.Status)
        .HasPrincipalKey(t => t.Code)
        .OnDelete(DeleteBehavior.Restrict);
}

The problem with my solution is that EF Core says it can't map between the Status enum type, and the Code string type.

Is what I am trying to do possible?

I want the foreign key constraint on the code as we have situations where data people will go directly into the database and manually add/remove/alter records, and having that constraint on the enum means they know all the valid values, and cannot enter an invalid enum value.

• c#
• entity-framework-core

• How many enums are we talking about? "Code" the entities; load from the enums; and export as a table. Or create the table manually and "import it"; verifying it against the actual enums, if necessary. Less time and less obscure code than configuring EF. Sometimes, "code first" becomes a database that is input into "database first" (and last). – Gerry Schmitz Commented Mar 7 at 4:01

2 Answers 2

It is not what you actually want but I would suggest to "revert" the approach. Enums are easily translatable to integer keys by the EF and arguably integers are better natural keys selection. Something like the following:

public class Model
{
    public int Id { get; set; }
    public Status Status { get; set; }
}

public class StatusLookup
{
    public required Status Id { get; init; }
    public required string Code { get; init; }
}

// ...

And the setup (I've used ModelBuilder approach since it was easier for me):

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<StatusLookup>()
        .ToTable($"Lookup_Status")
        .HasKey(t => t.Id);
    builder.Entity<StatusLookup>()
        .Property(t => t.Code)
        .HasMaxLength(64)
        .IsRequired();
    builder.Entity<StatusLookup>()
        .HasIndex(s => s.Code)
        .IsUnique();

    // Seed the lookup table
    var data = Enum.GetValues<Status>()
        .Select(v => new StatusLookup
            {
                Id = v,
                Code = typeof(Status)
                    .GetMember(v.ToString("G"))
                    .FirstOrDefault()
                    ?.GetCustomAttribute<CodeAttribute>()?.Code 
                       ?? v.ToString("G")
            }
        )
        .ToList();
    builder.Entity<StatusLookup>()
        .HasData(data);
    
    builder.Entity<Model>()
        .HasOne<StatusLookup>()
        .WithMany()
        .HasForeignKey(t => t.Status)
        .OnDelete(DeleteBehavior.Restrict);
}

EF can store enums as either int (or other signed numeric types) or string but you'll have to choose one or the other for the FK column and matching PK. For instance if you want the string representation of the enum to be the FK column in your Model, then the string value needs to be the PK on the StatusLookup table. For instance:

public enum StatusIds
{
    [Code("Active")]
    Active = 1,

    [Code("Pending")]
    Pending = 2,

    [Code("InProgress")]
    InProgress = 3,

    [Code("Complete")]
    Complete = 4
}

public class Model
{
    public int Id { get; set; }
    public StatusIds StatusId { get; set; }
    public StatusLookup Status { get; set; }
}

public class StatusLookup
{
    public required StatusId Id { get; init; }
    public required string Code { get; init; }
}

Then in configuration:

    modelBuilder
        .Entity<Model>()
        .Property(e => e.StatusId)
        .HasConversion<string>();

... and similarly for the key column. At the end of the day EF will be generating an SQL statement and you'd want either:

SELECT ...
FROM Models m
INNER JOIN StatusLooup sl ON m.StatusId = sl.Id

You can't have INNER JOIN sl ON m.StatusId = sl.Code for instance if Model.StatusId is string and sl.Id is int. The FK must link to the table's PK so the types need to match.