Enumeration Support

• Introducing
• Enumeration support in databases
• Enumerations support in LINQ to SQL
  • Case management
• Entity Developer
  • User interface for enumerations
  • Creating enumerations
  • Naming rules
  • The model validation
  • Update Model From Database
• Entity Framework

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:

• MySQL
• PostgreSQL supports enumerations from the 8.3 version

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

Generating enumerations for integer columns

Generating enumerations for table-dictionary

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.