Enums and Lookup Tables with EF Code first

With EntityFramework's support for enums, there are no longer any need to include lookup tables in the model. But I did want to has them in the database for integrity, even with code first.

I ' ve been thinking for some time about to handle enums with code first. The idea behind code first was to being able to write the code as close as possible to what object oriented code is normally WR Itten. For enums, that means, the enum definition itself is the constraint, that's used to ensure, that's valid values are U Sed in the code.

For databases it would being possible to use a column constraint, but the normal-to-use-a lookup table where the valid Values are present. Any column in the database mapped against the enum type was then a foreign key to the lookup table to ensure integrity of t He data.

What I would prefer are a solution where the lookup table is present in the database, but not mapped against any entity in The code.

Adding the Lookup Table

Adding The lookup table is simple when using the Entity Framework migrations. I ' ve reused my old example in creating a database and updated Entity Framework to 6.0.1 that supports enums. When scaffolding a migration, the CarBodyStyle field (which was an enum) was now recognized by the Entity Framework:

public override void Up () { addcolumn ("dbo"). Cars "," bodystyle ", C = C.int (Nullable:false));

The lookup table and the foreign key can be added by extending the migration code:

public override void Up () { createtable ("dbo"). Carbodystyles ", C = new { Id = C.int (nullable:false), Description = c.string (maxlength:50) } ). PrimaryKey (t = t.id); Sql ("INSERT carbodystyles VALUES (0, ' not Defined ')"); AddColumn ("dbo. Cars "," bodystyle ", C = C.int (Nullable:false)); Addforeignkey ("dbo. Cars "," bodystyle "," dbo. " Carbodystyles ");}

The insert of the first value in was there to make CarBodyStyles sure that there was a matching value when the foreign key constrain T is added to the Cars table. Without it, the foreign key can ' t be added.

Adding Lookup Values

The migration takes care of adding the lookup table to the database, but that's only the first half of the solution. The second half is-make sure, the enum values is reflected in the lookup table automatically. The right place-to-do-is-not-a-migration, but-in the the-method that's Configuration.Seed() called every time the migrations is app Lied. It is called even if there is no pending migrations.

protected override void Seed (TestLib.Entities.CarsContext context) { context. Seed<testlib.entities.carbodystyle> ();}

That looks simple–doesn ' t it? All of the work was deferred to the  seed<tenum> ()  extension method. The lookup table isn ' t mapped to the entity model, so there is no-it-use-all-the-features of the Entity Framework for This operation. Instead I ' m building up a plain Sql string, following the pattern from the idempotent DB update scripts post. I know that I ' m not escaping data nor using parameterized SQL which leaves the risk för SQL injection. On the other hand I ' m only using data from description attributes which is controlled by the developer so if you want to u Se it and Sql inject Yourself–go ahead!

public static class enumseeder{//<summary>//Populate a table with the values based on defined enum values. </summary>//<typeparam name= "Tenum" >type of the enum</typeparam>//<param name= "cont Ext ">a DbContext to use to run queries against//the database.</param>//<param name=" IDfield ">id field, that should is populated with///The numeric value of the enum.</param>//<param name= "Descripti Onfield ">description field, that should is//populated with the contents of the Description attribute (if//t Here's any defined) .</param>//<param name= "TableName" >name of the table. Assumed to be the same//as the enum name plus an "s" for pluralization if nothing//else is DEFINED&LT;/PARAM&G T [System.Diagnostics.CodeAnalysis.SuppressMessage ("Microsoft.Design", "ca1062:validate arguments of public methods", MessageId = "0"), System.Diagnostics.CodeANalysis. SuppressMessage ("Microsoft.Design", "Ca1004:genericmethodsshouldprovidetypeparameter")] public static void SEED&L T Tenum> (This DbContext context, string IDfield = "Id", String Descriptionfield = "description", String tableName = null) {if (tableName = = null) {TableName = typeof (Tenum). Name + "S"; }  var CommandBuilder = new StringBuilder ();  commandbuilder.appendformat ("CREATE TABLE #EnumValue S (\ n "+" Id {0} not NULL PRIMARY key,\n "+" Description NVARCHAR ()) \ n \ nthe ", getidtype<tenum> ( ));  addvalues<tenum> (CommandBuilder);  string descriptionupdate = Descriptionfield = null ? String. Empty:string. Format (CultureInfo.InvariantCulture, "when matched then update\n" + "SET dst.{ 0} = src. Description\n ", Descriptionfield);  string descriptioninsert = Descriptionfield = null? String. Empty: ", Src. Description ";  string descriptioninfieldlist = Descriptionfield = = null? String. Empty: "," + descriptionfield;  Commandbuilder.appendformat (CultureInfo.InvariantCulture, "MERGE {0} dst\n" + "USING #EnumValues src\n" + "on (src. Id = DST. {1}) \ n "+" {2} "+" When not matched then\n "+" INSERT ({1}{3}) VALUES (src. ID{4}) \ n "+" when the matched by SOURCE and then delete;\n\n ", TableName, IDfield, Descriptionupdate, de Scriptioninfieldlist, Descriptioninsert);  Commandbuilder.appendformat (CultureInfo.InvariantCulture, "DROP TA BLE #EnumValues \ n ");  context. Database.executesqlcommand (Transactionalbehavior.donotensuretransaction, commandbuilder.tostring ()); }  private static void Addvalues<tenum> (StringBuilder commandbuilder) {var values = Enum.getvalue S (typeof (Tenum));  if (values. Length >0) {Commandbuilder.appendformat (CultureInfo.InvariantCulture, "INSERT #EnumValues VALUES \ n ");  var descriptions = getdescriptions<tenum> ();  bool firstvalue = true; foreach (Var v in values) {if (firstvalue) {Firstvalue = false; } else {Commandbuilder.appendformat (CultureInfo.InvariantCulture, ", \ n") ; } String valuestring = V.tostring ();  Commandbuilder.appendformat (cultureinfo.invariant Culture, "({0}, ' {1} ')", (int) v, descriptions[valuestring]); }  Commandbuilder.appendformat (CultureInfo.InvariantCulture, "\ n"); }}  private static idictionary<string, string> getdescriptions<tenum> () {return typeof ( Tenum). GetMembers (bindingflags.static | BindIngflags.public). Select (M = = new {Name = M.name, Description = M.getcustomattributes (typeof (De Scriptionattribute), True). Cast<descriptionattribute> (). Singleordefault ()}). ToDictionary (A = a.name, a = A.description = = null? null:a.description.description); }  private static string getidtype<tenum> () {var underlyingtype = Enum.getunderlyingtype (typeof (T Enum));  if (Underlyingtype = = typeof (int)) {return ' int '; }  if (Underlyingtype = = typeof (short)) {return ' SMALLINT '; }  if (Underlyingtype = = typeof (Byte)) {return ' TINYINT '; }  throw new NotImplementedException (); }}

Enums and Lookup Tables with EF Code first