Entity Framework 5 and Enum support

Starting from EF 5 (currently RC, available as a NuGet package), you can use enums in your data model. In older versions, you always needed to use casting if you wanted to compare a property value against an enum value.

Note: This post is using the Database First workflow. Have a look also on my posts about Code First and Code First with existing database.

In the following code, Student.StatusID is an FK column with a constraint to the StatusID column in the StudentStatusType lookup table that has another column StatusName (nvarchar).

from s in db.Students
where s.StatusID == (int)StudentStatusEnum.Graduate
select s;

For the above query to work right, you had to hold an enum with values that follow the values in the StudentStatusType table, which was a little risky if for some reason you had to change your data in the StudentStatus table.

With EF 5, you can set a property to be of type enum. To do that, right click on the property in the model designer, and select “Convert to Enum”. After establishing your enum, you have to set both sides of the relation to be of type enum, since like in sql, both sides of a relation must be of the same type.

Now with this enum support you can write:

from s in db.Students
where s.StatusID == StudentStatusEnum.Graduate
select s;

Although your code will look nicer, this feature lacks the ability to generate the enum from the lookup table, so you still need to copy the lookup table to your code.

Fortunately, EF code generation can be customized with T4 templates, and you can start with a template from Visual Studio Gallery. The template that can be used to generate code exactly like the Visual Studio does is still not available for EF 5, but you can use the DbContext generator meanwhile.

The idea is to create the enum as above in the edmx designer, but to query the database in the T4 template.

The original code to generate enums in the template is:

<#=Accessibility.ForType(enumType)#> enum <#=code.Escape(enumType)#> : <#=code.Escape(enumType.UnderlyingType.ClrEquivalentType, fullyQualifySystemTypes: false)#>
{
<#
    foreach (EnumMember member in enumType.Members)
    {
#>
       <#=code.Escape(member)#> = <#=member.Value#>,
<#
    }

...

I replaced the foreach loop with:

IServiceProvider hostServiceProvider = (IServiceProvider)this.Host;
SettingsManager config = new SettingsManager(hostServiceProvider);

EntityContainer container = ItemCollection.GetItems().FirstOrDefault();
var connectionString = config.ConnectionStrings[container.Name].ConnectionString;

if (connectionString.Contains("|DataDirectory|"))
{
    DTE dte = (DTE)hostServiceProvider.GetService(typeof(DTE));
    Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;
    connectionString = connectionString.Replace("|DataDirectory|", System.IO.Path.GetDirectoryName(project.FileName)+ "\App_Data");
}

var builder = new System.Data.Common.DbConnectionStringBuilder();
builder.ConnectionString = connectionString;
connectionString = builder["provider connection string"].ToString();

var primaryKeyProperty = code.GetItemsToGenerate(ItemCollection).SelectMany(e => e.KeyMembers).FirstOrDefault(km => km.TypeUsage.EdmType == enumType);
var tableName = primaryKeyProperty.DeclaringType.Name;

using (var con = new SqlConnection(connectionString))
{
    var cmd = new SqlCommand("Select * From " + tableName, con);
    con.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
#>
    <#=reader[1]#> = <#=reader[0]#>,
<#
        }
    }
}

Explanation:

First we need to find the project config file and load it. This is not so simple since the T4 template code is not running in the context of your project and regular config code won’t work here. For this I used the SettingsManager class available here. Note: Although this code works you can’t debug it in Visual Studio 2012 because it fails iterating ProjectItems collection.

IServiceProvider hostServiceProvider = (IServiceProvider)this.Host;
SettingsManager config = new SettingsManager(hostServiceProvider);

Now you need to get the connection string. The connection string key can be found with the container name. The container is initialized in the template used to generate the DbContext code and you can copy it from there.

EntityContainer container = ItemCollection.GetItems().FirstOrDefault();
var connectionString = config.ConnectionStrings[container.Name].ConnectionString;

In case your database is in your app_data folder, you need to resolve the “|DataDirectory|” macro by yourself since this macro will lead you inside Visual Studio installation folders.

if (connectionString.Contains("|DataDirectory|"))
{
    DTE dte = (DTE)hostServiceProvider.GetService(typeof(DTE));
    Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;
    connectionString = connectionString.Replace("|DataDirectory|", System.IO.Path.GetDirectoryName(project.FileName)+ "\App_Data");
}

Now before using the connection string, you need to extract just the connection string itself without the metadata info since SqlConnection doesn’t like it.

var builder = new System.Data.Common.DbConnectionStringBuilder();
builder.ConnectionString = connectionString;
connectionString = builder["provider connection string"].ToString();

Now that we got the connection string, we are ready to “select * from”, but we still need to know the table name. So we need to find the entity type that represents the lookup table. Because we set earlier its primary key to be of the enum type, it’s very easy:

var primaryKeyProperty = code.GetItemsToGenerate(ItemCollection).SelectMany(e => e.KeyMembers).FirstOrDefault(km => km.TypeUsage.EdmType == enumType);
var tableName = primaryKeyProperty.DeclaringType.Name;

The entity type name won’t always be identical to the table name. There is no built-in way to get that, but there is a tricky way. I just assumed that the entity name is the table name. If you want you can use the trick.

It’s time to get the data!!!

using (var con = new SqlConnection(connectionString))
{
    var cmd = new SqlCommand("Select * From " + tableName, con);
    con.Open();
    using (var reader = cmd.ExecuteReader())
    {
       while (reader.Read())
       {
#>
           <#=reader[1]#> = <#=reader[0]#>,
<#
       }
    }
}

I assumed the first column is the primary key and the second is the name, and I didn’t deal with whitespaces and capitalization. You can improve that code like in here.

Well this was quite an ugly code. I hope MS will add a capability to set the lookup table name when you create the enum type.

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com