Monday, 31 December 2007

Using enums in LINQ to SQL

A common practice when working with databases is to use enumerations for any static lookup (ID/description) data. These enums are represented both in code and also in the database for referential integrity. The idea being this static data rarely changes so we don't need to pull it back continually from the database.

Taking the following data model as an example;

Click for more details

All of the tables that end with Enum are static tables that consist of reference data only and we don't want this to be pulled back from the database. Instead these enum tables are represented in code as enums and as such we need to tell LINQ to map the fields that refer them to enum values rather than the database tables.

First things first, I mapped out my basic entities as follows (notice the lack of enum tables as objects in this model).

image In order to force the entities to use an enum value instead of loading a child entity object, I simply set the Type property for the field to a type in my project, as per the image below; (Also I renamed the fields from FieldXYZID to just FieldXYZ).


Finally, notice that because my enum definition is within the same assembly as my DBML, the type namespace is relative to the DBML. If it was in a different assembly, then you'd specify a full type name here instead.

No comments:

Post a Comment