Enity Framework Core: Handling large number of enum-like fields

Database is not an object store and you have to design it accordingly. I have changed you schema and only two tables are needed for storing dropdown values.

[Table("tbl_enum_type")]
public class EnumType {
  public int ID { get; set; } // PK
  public string Name { get; set; }
}

// PK (EnumTypeId, Id) - reusing the same index for dropdown generation
[Table("tbl_enum_value")]
public class EnumValue {
  public int ID { get; set; } 
  public string Name { get; set; }
  public int Order { get; set; } // for dropdown ordering
  public int EnumTypeId { get; set; } 
  public EnumType EnumType { get; set; }
}

// store only ID's, no FK
[Table("tbl_large_dataclass")]
public class LargeDataClass {
   public int EnumOneId { get; set; }    // EnumTypeId 1
   public int EnumSecondId { get; set; } // EnumTypeId 2
   //...
}

For generating dropdowns, you have to cache EnumType and EnumValue tables in memory in useful structure.

Override method SaveChanges/SaveChangesAsync and check saved Id’s according to cached data.

It will not help if your database is changed via SQL, but here we have trade-off between performance and consistency. Probably good trigger may help here.

UPDATE:

Consider to restructure LargeDataClass to two tables

[Table("tbl_option_bag")]
public class OptionBag {
   public int Id { get; set; }    
   public ICollection<Option> Options { get; set; } 
}

[Table("tbl_options")]
public class Option {
   public int Id { get; set; } 
   public int OptionBagId {get; set; }
   public int EnumTypeId { get; set; } 
   public int EnumId { get; set; } 
   //...
}

Here you can use FK and DTO can be generated on selecting Options navigation property.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top