Don't go to the DB every time. Go to the DB at process start; load into readonly dictionaries your lookup data, and call it good. Stuff changes underneath, you need to restart the app to re-grab those dictionaries.

Alternatively, look at actual resource cost of looking the data up every time, if your app isn't significant enough for this to matter, just let it lookup the data everytime via join.

Cache expiration is a complex thing but a very simple technique can be had using SQL server's message broker triggered on updates to lookup tables to send a message to your running app which signals it to replace it's readonly dictionaries with updated versions. This is presuming the updates will be manual SQL scripts, lock your lookup tables down to being updated by the app and those updates can signal a reinstantiation of your read-only lookup table dictionaries, this is so long as you don't need to worry about people changing the lookup tables directly through management studio or otherwise.

Alternatively use a proper cache like appfabric or memcached for these where you can have appropriate cache expiration if you need.

In summation:

No, you do not want dynamic enums, this goes against what an enum is even for

Enums are named consts. Remember that, consts. As soon as you want a group of key-value pairs you are starting down the path of hashtable/dictionary/associative-array or whatever word you wish to call them, that is precisely the purpose of such structures. Not the purpose of enums.

Don't go to the DB every time. Go to the DB at process start; load into readonly dictionaries your lookup data, and call it good. Stuff changes underneath, you need to restart the app to re-grab those dictionaries.

Alternatively, look at actual resource cost of looking the data up every time, if your app isn't significant enough for this to matter, just let it lookup the data everytime via join.

Cache expiration is a complex thing but two very simple techniques come to mind:

• Lock your lookup tables down to being updated by the app and those updates can signal a reinstantiation of your read-only lookup table dictionaries, this is so long as you don't need to worry about people changing the lookup tables directly through management studio or otherwise.
• Using SQL server's message broker triggered on updates to lookup tables to send a message to your running app which signals it to replace it's readonly dictionaries with updated versions. This is presuming the updates will be manual SQL scripts

Alternatively use a proper cache like appfabric or memcached for these where you can have appropriate cache expiration if you need. This seems like overkill for what could really be served just fine by my initial suggestion, most systems have admins turn off their app when they go to update their DB directly anyway, and turn it back on only after DB updates are finished.

In summation:

No, you do not want dynamic enums, this goes against what an enum is even for

Enums are named consts. Remember that, consts. As soon as you want a group of key-value pairs you are starting down the path of hashtable/dictionary/associative-array or whatever word you wish to call them, that is precisely the purpose of such structures. Not the purpose of enums.

Don't go to the DB every time. Go to the DB at process start; load into readonly dictionaries your lookup data, and call it good. Stuff changes underneath, you need to restart the app to re-grab those dictionaries.

Alternatively, look at actual resource cost of looking the data up every time, if your app isn't significant enough for this to matter, just let it lookup the data everytime via join.

Cache expiration is a complex thing but a very simple technique can be had using SQL server's message broker triggered on updates to lookup tables to send a message to your running app which signals it to replace it's readonly dictionaries with updated versions. This is presuming the updates will be manual SQL scripts, lock your lookup tables down to being updated by the app and those updates can signal a reinstantiation of your read-only lookup table dictionaries, this is so long as you don't need to worry about people changing the lookup tables directly through management studio or otherwise.

Alternatively use a proper cache like appfabric or memcached for these where you can have appropriate cache expiration if you need.

In summation:

No, you do not want dynamic enums, this goes against what an enum is even for

Enums are named consts. Remember that, consts.

Don't go to the DB every time. Go to the DB at process start; load into readonly dictionaries your lookup data, and call it good. Stuff changes underneath, you need to restart the app to re-grab those dictionaries.

Alternatively, look at actual resource cost of looking the data up every time, if your app isn't significant enough for this to matter, just let it lookup the data everytime via join.

Cache expiration is a complex thing but a very simple technique can be had using SQL server's message broker triggered on updates to lookup tables to send a message to your running app which signals it to replace it's readonly dictionaries with updated versions. This is presuming the updates will be manual SQL scripts, lock your lookup tables down to being updated by the app and those updates can signal a reinstantiation of your read-only lookup table dictionaries, this is so long as you don't need to worry about people changing the lookup tables directly through management studio or otherwise.

Alternatively use a proper cache like appfabric or memcached for these where you can have appropriate cache expiration if you need.

In summation:

No, you do not want dynamic enums, this goes against what an enum is even for

Enums are named consts. Remember that, consts. As soon as you want a group of key-value pairs you are starting down the path of hashtable/dictionary/associative-array or whatever word you wish to call them, that is precisely the purpose of such structures. Not the purpose of enums.