tags:

In my previous post, I showed how to populate an enum to a lookup table using EF Code First and a new database. Although you might think that having a database prior to coding force you to go to the Database First workflow, you’d be happy to know you can still use the Code First workflow with the help of EF Power Tools to reverse engineer your database to a Code First starting point.

The database schema for this post is:

Lookup table:

CREATE TABLE [dbo].[StudentStatusType] (
	[ID] INT NOT NULL,
	[StatusName] NVARCHAR (MAX) NULL
);

Students table:

CREATE TABLE [dbo].[Students] (
	[ID] INT IDENTITY (1, 1) NOT NULL,
	[StatusTypeID] INT NOT NULL
);

In addition to this, StudentStatusType.ID is the PK for the FK column Students.StatusTypeID.

The steps to reverse engineer a database are:

  1. Install EF Power Tools extension.
  2. Create a project (Console, web etc.).
  3. Add Entity Framework NuGet package.
  4. Right click on the project. In the context menu click on Entity Framework -> Reverse Engineer Code First.
  5. In the connection dialog enter your server and database name and click OK.

In your project you have now some more files in the Models folder.

Student.cs:

public class Student
{
	public int ID { get; set; }
	public int StatusTypeID { get; set; }
	public virtual StudentStatusType StudentStatusType { get; set; }
}

StudentStatusType.cs:

public class StudentStatusType
{
	public StudentStatusType()
	{
		this.Students = new List();
	}

	public int ID { get; set; }
	public string StatusName { get; set; }
	public virtual ICollection Students { get; set; }
}

In your [dbname]Context.cs you have a standard DbContext class with DbSet for Student and StudentStatusType. In addition you’ll see a folder named Mapping with mapping details using the FluentAPI.

In an earlier post I showed how to generate an enum from a lookup table in the Database First workflow. Although we’re using the Code First workflow, we can use a similar method since the database was created first.

The things we need to change are:

  • Create an enum in addition to the the StudentStatusType class named StudentStatusTypeEnum and populate it with the data in the StudentStatusType table.
  • The PK StudentStatusType.ID should be of type StudentStatusTypeEnum instead of int.
  • The FK Student.StatusTypeID should be of type StudentStatusTypeEnum instead of int.

In order to change the reverse engineer process, right click again on the project but this time click on Entity Framework -> Customize Reverse Engineer Templates (No icon for you buddy, it is still beta).

In the project there are now three more T4 files in CodeTemplates\ReverseEngineerCodeFirst folder:

  • Context.tt – generates the class that inherits DbContext
  • Entity.tt – generates the entities Student and StudentStatusType
  • Mapping.tt – generates the FluentAPI mapping classes
I advise you to install the Tangible T4 Editor. It has a free version and it really helps editing T4 templates.

Creating the enum

Assuming any table name ending with Type should be populated as an enum, we need to add the following code in Entity.tt right before the enclosing bracket of the namespace:

<# if (efHost.EntityType.Name.EndsWith("Type")) { #>
	public enum <#= efHost.EntityType.Name + "Enum" #>
	{
	}
<# } #>

Inside the enum we need to read the table data and populate the enum values. First, just like we did in Database First Workflow, we need to find the connection string in the application config file. With the Database First workflow we used a T4 template who its host can give you access to the Visual Studio environment classes that allows you to enumerate the project files. In our case though, the T4 template host only exposes the model data (it is still beta). Fortunately, the source code of EF Power Tools can be downloaded and changed in a way you’ll get the connection string ready to use!

The steps to do that are:

  1. Download EF source code from http://entityframework.codeplex.com/
  2. Download Visual Studio 2012 SDK
  3. Open the Power Tools solution. The sln file is in the root folder of the source code.
  4. Open the EfTextTemplateHost class and add this property:
    public string ConnectionString { get; set; }
  5. Open the ReverseEngineerCodeFirstHandler class. This code is responsible to open the connection dialog when you reverse engineer a database. The line that do it is:
    var dialogResult = dialog.ShowDialog(connect: true);

    After a few rows the connection string is extracted:

    var connectionString = connection.ConnectionString;

    In this class, every place an EfTextTemplateHost is initiated, set its ConnectionString property with the value of the connectionString variable, for example:

    var entityHost = new EfTextTemplateHost
    	{
    	    ConnectionString = connectionString
    	// Rest of properties
    	};
  6. Build the project.
  7. Copy EFPowerTools.dllfrom the output directory to the extension directory. The directory should be
    C:\Users\[YourUserName]\AppData\Local\Microsoft\VisualStudio\11.0\Extensions\rj2pbzdi.3ea

    The last folder name might be different. This path can also be found in the registry in this key

    HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0_Config\Packages\{2b119c79-9836-46e2-b5ed-eb766cebbf7c}\CodeBase

    Close all Visual Studio windows if you can’t replace the file.

Now that we got the connection string in our hand we can add code to query the database and populate the enum:

Add these two lines after the first row in the Entity.tt file:

<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>

Add this code where we created the enum before:

<#  if (efHost.EntityType.Name.EndsWith("Type")) 	{ #>
	public enum <#= efHost.EntityType.Name + "Enum" #>
	{
<#   	// assuming the first string column is the name of the enum value 	var firstStringColumnName = efHost.EntityType.Properties.First(p => (p.TypeUsage.EdmType as System.Data.Metadata.Edm.PrimitiveType).ClrEquivalentType.Equals(typeof(string))).Name;
	var keyColumnName = efHost.EntityType.KeyMembers[0].Name;
	var connectionString = efHost.ConnectionString;
	using (var con = new SqlConnection(connectionString))
    {
		con.Open();
		var cmd = new SqlCommand("SELECT * FROM " + efHost.EntityType.Name);
		cmd.Connection = con;
		using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
#>
		<#= code.Escape(reader[firstStringColumnName].ToString()) #> = <#= reader[keyColumnName].ToString() #>,
<#             }         }     } #>
	}
<# } #>

Change the PK and FK columns type

In Entity.tt file there is a foreach loop that creates the properties for each table column. Inside it we need to add some code that will find out if the current property is the PK or the FK and change the type accordingly:

foreach (var property in efHost.EntityType.Properties)
{
	string typeName = code.Escape(property.TypeUsage);;

	// If we are iterating the properties of the lookup table
	if (efHost.EntityType.Name.EndsWith("Type"))
	{
		// If this property is the primary key - change the type
		if (efHost.EntityType.KeyMembers.Any(km => km.Name == property.Name))
		{
			typeName = efHost.EntityType.Name + "Enum";
		}
	}
	// We are iterating another table - look if this is the FK property
	else
	{
		// Find the navigation property of this property and see if this connects us to the lookup table
		var navProperty = efHost.EntityType.NavigationProperties.FirstOrDefault(np => np.GetDependentProperties().Any(p => p.Name == property.Name) && np.ToEndMember.GetEntityType().Name.EndsWith("Type"));

		if (navProperty != null)
			typeName = code.Escape(navProperty.ToEndMember.GetEntityType()) + "Enum";
	}
#>
	<#= Accessibility.ForProperty(property) #> <#= typeName #> <#= code.Escape(property) #> { get; set; }
<#
}

I used some mapping classes that can be found with MSDN help and also by debugging the EF Power Tools. Now that we have the source code we can attach the VS debugger with the source code to the VS with our project.

Save the T4 template we edited and reverse engineer again. In StudentStatusType.cs you’ll see our enum, and the PK and the FK properties have the type of the new enum.

Run this code and see if you get a new row in the Students table:

using (var dc = new YourContext())
{
    dc.Students.Add(new Student() { ID = 1, StatusTypeID = StudentStatusTypeEnum.Graduate });
    dc.SaveChanges();
}

What would we do if the lookup table will have more rows? Well, Code First Reverse Engineer is still one time action. You can do that again but it will override all your code and you still want to use Code First. Incremental reverse engineering might come in the future, but I think that after reverse engineering you should only edit your code. If you add more enum values, you can add them to the lookup table with Migrations as I’ve written in my previous post.