Best Practices for Lookup Tables in EF Code-First

Question

I'm doing my first project with EF and I'm planning to go the code-first model. I'm trying to find a bit of guidance about handling a fairly classic "lookup table" scenario.

I'm dealing with a pretty canonical situation where I'll be persisting address data. So, I have a simple address DTO...

public class Address
    {
        public int Id { get; set; }
        public virtual string StreetAddress1 { get; set; }
        public virtual string StreetAddress2 { get; set; }
        public virtual string City { get; set; }
        public virtual string State { get; set; }
        public virtual string ZipCode { get; set; }
    }

In the state property, I'd like to store the standard US two-letter state code. For validation purposes, I'd like to have a standard one-to-many foreign key relationship between the resulting Address table and a fairly standard state lookup table. That table would probably contain an ID, the two-letter code, and a third column to contain the full state name.

I would expect to use this state lookup table to populate and state drop-down style boxes, etc and also act as a validation to the State filed in the address entity. Fairly common stuff. So, I have a couple of simple (I hope) questions.

1. Do I need to create an entity to represent the State entity just to have EF create the table, or can I just include the table creation process in a DBCreation strategy and seed it there?
2. Would it make sense to create that entity, just to use as "view models" for any place where I want to display a "state-picker"
3. I really only want to store the two-letter state code in the address entity, but does this make sense or does it make more sense to just make it a navigation property to a state entity and then display?

I struggled a bit with articulating my point here, so if I'm not clear, feel free to ask for more detail.

Thanks in advance. appropriately in the UI?

Answer 1

1. Yes you can just create a new DBCreation script extending the original script and create a state table which has no relation with the Entity Framework.

2. If I were you I would create the state entity. If state entity is not created, in the end you need to create it in the code, but populating this entity will be a problem, you will need to use sql (you may store this data in xml which seems a better option than storing in sql).

3. If you decide to store the table in the database and directly use it by creating an entity, making it a navigation property is a better option since you may use it directly while lazy loading or eager loading by including it.

Answer 2

1. I would make the state it's own class and a navigation property of the Address.

public class Address
{
    public int Id { get; set; }
    public virtual string StreetAddress1 { get; set; }
    public virtual string StreetAddress2 { get; set; }
    public virtual string City { get; set; }
    public virtual USState State { get; set; }
    public virtual string ZipCode { get; set; }
}

public class USState
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Text { get; set; }
}

With code first EF will create the table, but you can populate it in the Seed() method.

1. You don't necessarily need to use view models, but it makes sense to use a shared view for displaying the states in an edit form. You don't mention MVC, but if you use that, then it's as simple as putting

[UIHint("StatePicker")]
public virtual USState State { get; set; }

in your POCO or view model - depending on what your view uses. Then in Views/Shared/EditorTemplates, add a partial view StatePicker.cshtml, which would looke something like

@inherits System.Web.Mvc.WebViewPage<USState>
@Html.DropDownListFor(m => m, new SelectList((IEnumerable<USState>)ViewBag.USStatesAll,
    "Id",
    "Name",
    Model==null?1:Model.Id),
    "Choose--")

in combination with

@Html.EditorFor(m => m.State)

in your view.

1. Navigation property. Your db will store the USState id as a foreign key, but your app can use addr.State.Code or addr.State.Text, depending on the need. It's much more flexible.