Devart Blog

Enumeration Support

Posted by on May 17th, 2010

Introducing

We keep receiving requests about enumeration support in Entity Developer from our customers. That’s why we plan to implement this functionality in the second half of 2010.

In this article we describe the functionality concerning enumeration support we plan to add. It is only a draft and these features are a subject to change. It depends on the customer suggestions in many respects, so if you have any suggestions or requirements concerning this functionality, please write them as comments to this article or in any other way comfortable for you.

Enumeration support in databases

Some databases have native enumeration support:

Microsoft SQL Server, Oracle, SQLite store enumerations as usual string or numeric columns. When data is stored in numeric columns, a single table-dictionary with foreign key is often used. When table-dictionary isn’t used, usually the SQL CHECK constraint is applied to the column values.

Enumeration support in LINQ to SQL

Surely, enumeration usage makes sense only in case when column values on which enumeration was formed are static and constant during the client application work. Enumerations in C#/Visual Basic have explicit or implicit specified integer value for each element and each element has a name. This enumeration can be stored in the database as an integer or string value.

Complete support of numeric and string enumerations is available in LINQ to SQL. Please view a sample concerning this support LINQ to SQL Mapping Enum from string.

We support LINQ to SQL in the following products – dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, and dotConnect for SQLite. Recently we released a beta-version of a new product which integrates LINQ to SQL support for Oracle, MySQL, PostgreSQL, SQLite, and SQL Server – Devart LINQ Connect.

The main aspects of the enumerations in LINQ to SQL are described in the next chapters.

Case management

Sometimes it is impossible to set up a case of enumerations in LINQ to SQL.

For example, string values ‘man’ and ‘woman’ are stored in a database, but customer wants to use standard capitalized names in C#.

public enum Gender {
  Man,
  Woman
}

Also, string values in the database can be presented as abbreviations, words written in uppercase, etc. Public enumeration saves .NET enumeration element readability and provides flexibility during enumeration mapping to the database.
It can be similar to the code sample below:

public enum Devart.Data.Linq.EnumMappingCaseRule {
  CaseSensitive,
  UpperCase,
  LowerCase,
  CaseInsensitive
}

It is better to use some flag to manage this behaviour, but adding this flag on the DataQuery level can be insufficient. It may be configured on the DataContext level, because in another case object materialization returned from stored procedures and executed native SQL statements can be incomplete. But in this case mapping flexibility can be lost. So, the best practice will be to separate each enumeration configuration using case sensitivity.

Mapping rules for enumeration in C# can be described with the following attribute:

[AttributeUsage(AttributeTargets.Enum)]
public sealed class Devart.Data.Linq.EnumMappingAttribute
    (Devart.Data.Linq.EnumMappingCaseRule caseRule) : Attribute { }

So, our resulting enumeration will be the following:

[EnumMapping(EnumMappingCaseRule.LowerCase)]
public enum Gender {
  Man,
  Woman
}

Entity Developer

Let’s consider enumeration support in the Entity Developer for LINQ to SQL models.

User interface for enumerations

The Enumerations node will be added to Model Explorer.

A node is created for each enumeration in the model and it can be opened to display its elements. Each enumeration has its Access Modifier (public/internal). When enumeration is created by the column or table-dictionary, information about it can be saved in the Documentation property. This description will be included to the generated C# code as XML-comment, shown to the developer with the help of Visual Studio Intellisense.

 /// <summary>
 /// This enum was generated for 
///  the "ReasonType" column of the "AdventureWorks.Sales.SalesReason" table.
 /// <summary>
 public enum ReasonType {
   ...
 }

Support of EnumMappingCaseRule will be added in design-time as well.

Each enumeration can be:

  • a simple link for external .NET enum-type described in another part of an application or in one of the included references (full type name with class namespace is required).
  • enumeration described in the model that has its own elements list. This enumeration will be generated during code generation.

Each element of the enumeration has a required Name property and a set of the optional properties.
The Integer value of an enumeration element can be stored in the Integer Value property. The Documentation property can store the full name (with spaces and special characters) of the element. User also can place here his own comments concerning the elements of this enumeration. The Documentation property will be saved as an XML-comment to enumeration during code generation.

public enum GlobalRegion {
   /// <summary>
   /// The Americas, being North, Central, and South America
   /// <summary>
   AMER = 1,
   /// <summary>
   /// Europe, the Middle East and Africa
   /// <summary>
   EMEA = 2,
   /// <summary>
   /// Asia Pacific, and Japan
   /// <summary>
   JAPAC = 3
 }

Creating enumerations

Enumeration can be created manually (just by choosing Add New Enum from the Enumerations node popup menu and then Add New Element from the enumeration popup menu).

Some proposals concerning enumeration creation are described here (we don’t know which of them would be implemented):

You can generate enumeration for MySQL and PostgreSQL for table columns of enumeration data type at once. Other ways of enumeration creation are listed below:

Generating enumerations for a string column

The example of the string enumeration is the ReasonType column in the Sales.SalesReason table of the AdventureWorks database.

To generate enumeration from the string property use the Convert to Enum menu item or drag a property to the Enumerations node. During this operation the database call will be executed for data fetching from this column. A new enumeration will be created (or an existing will be detected and used) depending on the selected data. User will receive a Message Box warning about database call. If there are no records returned then enumeration wouldn’t be created. User will be informed about that too. Also please note that the string enumeration element names can be incorrect for C#/Visual Basic. User should be aslo informed about this. Property type will be changed if enumeration was succesfully created.

Generating enumerations for integer columns

The example of the integer enumeration is the Status column in the Production.Document table of the AdventureWorks database.

Everything that was written concerning string columns can be also used for integer columns. But this is less convenient. In this case probably a table-dictionary exists in the database. User has to rename all enumeration elements from the numeric to the string values manually if the table-dictionary doesn’t exist in the database.

Generating enumerations for table-dictionary

The tables Person.AddressType and Person.ContactType are an example of the table-dictionary in the AdventureWorks database.

After an integer column migrates to the enumeration which presents values from the current table-dictionary, user doesn’t need to join these tables. Besides, user can delete the entity corresponding to the table-dictionary from the model and leave only the enumeration. The Create Enum Based on This Entity popup menu item is available for an entity with two properties (integer primary key and string column), if this entity is included to the association as a parent table. Similar action can be provided by moving this entity to the Enumerations node. If this entity has other columns, then user should delete them. For example, the AdventureWorks database has has Modified date column in all tables.

After this operation the database call will be executed for the process of receiving data from this table and a new enumeration will be created (or an existing one will be detected and used) depending on the selected data.

If enumeration was successfully created, then Entity Developer checks all foreign keys and changes the related properties type. Maybe, it is a good idea to implement such popup menu item as Convert Entity to Enum. In this case, entity and all related associations will be deleted.

Naming rules

Naming Rules of Entity Developer require enhancement. User will be able to setup rules for the enumeration element name conversion during the process of the enumeration creation. All names will be capitalized by default.

Model validation

Model validation will check the following points:

  • Are enumerations and their element names correct for C#/Visual Basic?
  • Are enumeration element names unique within the enum bounds?
  • Are specific integer values unique for the enumeration elements?

Update Model From Database

When using the Update Model from Database wizard it is necessary to update enumeration element collection built by column or table-dictionary. Maybe, the wizard should not offer to add table-dictionary if it was deleted from the model.

Entity Framework

The possibility to work with enumerations in Entity Framework is important for users too. Enumeration support on the EF run-time level was planned in the first CTP in 2006. However, it wasn’t implemented in EF v4. Entity properties in Entity Framework have a limited set of types. All of the possible solutions can be provided only with the help of the wrapper property, but in LINQ to Entities queries calls are made only via primary db-property which is mapped to a database column, which is mapped to database column. There are some samples concerning this point: How do I replace an Int property with an Enum in Entity Framework?, Enum in ADO.NET Entity Framework v1.

Therefore the necessity of the enumeration support for Entity Framework is not obvious, although such support is potentally possible, but noticeably limited if compared to LINQ to SQL.

For example, it is possible to support enumeration storing in the model and automatic enumeration generation based on the database values. But in future we will have a trouble with enumeration linking with the specific mapped properties for wrapper property creating. It is possible to provide a possibility of creating wrapper properties, but this seems too difficult.

Maybe, it is necessary to allow setting up enumeration mapping for specific tables/columns and saving it in the enumeration itself. It will help with the Update model from database task, and it will allow updating values of the enumeration element collection from several tables. It will simplify the trouble with Entity Framework models.

4 Responses to “Enumeration Support”

  1. Rui Marques Says:

    Currently I’m using a simple solution that mimics Enum support to entity classes.

    Having a way to do this task on the designer with the ability of the T4 templates would be a killer feature.

  2. Shalex Says:

    Rui,

    Please describe your approach. How should it work (user interface, etc)?

    Best regards

  3. Rui Marques Says:

    I user this approach: http://landman-code.blogspot.com/2010/08/adding-support-for-enum-properties-on.html. To be honest this is the approach most used if you Google for it.

    I terms of user interface something like right-click on a property and having an option “Map to Enum type” would be great. Then having the choice to browse from an assembly would be just perfect.

    I think that having also full support within Entity Developer would please everyone if some sort of specific model settings are permitted: include enum in current class, make enum global, etc, etc…

  4. Shalex Says:

    Entity Developer supports ENUM types now: http://www.devart.com/forums/viewtopic.php?t=21949 .