Oct 302012
 

A few days ago I went to start a new project using Entity Framework. Like every other relational database in the world, I knew I would need some “lookup value” style tables. Y’know, WidgetStatus, FrobbleType, that sort of thing. EF had never had a good mechanism for quickly defining and referencing these types of lookup tables, but now there is Enum support! I thought this would be perfect, but it turned out to be woefully inadequate. So, I realized that EF still needed a good answer to creating lookup tables. I did some searching, and couldn’t find any good solutions, so I wrote one.

What’s wrong with EF Enum support?

Before we get into the library that I ended up writing, let me address why exactly the enum support in EF 5.X isn’t enough. Using EF Code First, we can create the following code:

    public class Widget
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public WidgetStatus Status { get; set; }
    }
 
    public enum WidgetStatus
    {
        Pending        = 1,
        ReadyForReview = 2,
        Finalized      = 3,
        Cancelled      = 4,
    }
 
    public class WidgetContext : DbContext
    {
        public DbSet Widgets { get; set; }
    }

Once we run some code that uses DbContext, Entity Framework will create a database for us. Unfortunately, only one table gets created, Widgets, with a INT field for WidgetStatus. This leaves your data model incomplete, and introduces “magic numbers” into your database.

Ideally, you want a WidgetStatus table in your database that is foreign keyed to the Widget table through a WidgetStatusID field. Also, in a lot of cases that I’ve seen, you actually want to store more about these lookup values then just a name. You also want an immutable Code field that can be referenced in the code, as well as a Name field that can be displayed on the UI. Also, you might want a Description field that gives more details, perhaps shown in a tooltip. You might even need a SortOrder field to allow for sorting the list of values in something other than alphabetical. Over time, you might need to remove values from the list due to changing business requirements, and the best way to do this is with an Expired field.

Extending Enums in Entity Framework to support these needs is currently impossible. And, in my opinion, the current support for Enums is not adequate enough to ever use in a production application.

Goals

So, what are the goals of EFLookup?

  • Make it easy to define lookup tables
  • Automatically provide the “standard” fields that you generally want: Code, Name, Description, etc.
  • Provide a simple syntax for retrieving lookup values (either one-at-a-time or as a list)e
  • Allow for a concise, type-safe way to define “known values” and reference them in code

Defining Lookup Values

The EFLookup library allows for the following syntax to create a lookup value and define known values:

    public class WidgetStatus : LookupValue { }
 
    public class WidgetStatusValues : LookupValueProvider
    {
        public WidgetStatusValues(DbContext ctx) : base(ctx) { }
 
        public WidgetStatus Pending { get; set; }
        public WidgetStatus ReadyForReview { get; set; }
        // etc...
    }

This results in a SimpleLookupValues table getting created in the database by Entity Framework, with fields for Name, Code, Description, CreateDate, ExpiredDate, and SortOrder. Other entities that need to reference this lookup table in a foreign key can do so with normal properties, the same as with any other EF entity.

What’s with these known values?

There are many times where you need to refer to a particular lookup value in code. For example, if you need to write a validation to make sure that your widget has a status of “Pending”, you will need to pull the “Pending” status record out of the database to do a comparison. In this case, “Pending” would be a known value, and the syntax above would allow you to refer to that record in a type-safe manner, using this syntax:

myContext.GetLookupValue((WidgetStatusValues s) => s.Pending)

That syntax isn’t as nice as using an Enum, unfortunately. But it is definitely better than using a LINQ statement to pull the record based on a magic string store in a constant. I chose to create extension methods on DbContext to make the syntax as succinct as possible. However, if you don’t like that, you can ignore those extensions and use the LookupValueProvider (in this example, WidgetStatusValues) directly. It provides similar methods.

By default, EFLookup assumes that the property name matches the Code field in the database. However, if you want the property name to be different then the Code, you can decorate your property with a LookupValueCode attribute.

Retrieving the List of Values

There is a similar syntax for retrieving the entire list of status values:

myContext.GetLookupValueList<WidgetStatus>();

This returns all of the non-expired statuses in order based on SortOrder and then Name. The intent of this would be to put the list in a dropdown type control on the UI for selection by the user.

Where do I get it?

This post was meant to be an introduction to EFLookup and to provide some background on why I created it and what my goals were. If you are interested in learning more about EFLookup, you can find the code on BitBucket. There are examples include in the EFLookup.Tests project that you can use to get started. The library is also published on the NuGet gallery so that you can easily reference it from your .NET projects. EFLookup is provided under the Apache 2.0 license.

Next Steps

If you end up finding this library useful, I’d love to hear your feedback. Or, if you think this is the wrong solution to the problem, I’d like to hear that, too. Hopefully in the future the Entity Framework will provide more extensive support for Enums that would make a library like this unnecessary. In the meantime, though, EFLookup allows for working with lookup tables without having to start from scratch. There are definitely enhancements I could make to this library. I already have some ideas. However, I’m not sure who will end up using it or for what purposes. So, if you find it useful, and would like to see more features, let me know.

  2 Responses to “Introducing EFLookup”

  1. Hi, Thank you for this, it looks very useful. I’m having some trouble using it however, I’m new to EF. Do you have a sample project I could look at?

    • Hi Sham,

      I’m sorry I do not have a sample project beyond what is included in this post. If you let me know what specific problem you are having, I can try to help.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>