Lookup tables should not be modeled as related entities in Entity Framework

OrderStatusEDMX I am tracking the status of an order in an eCommerce system. In the database, I have a table called OrderStatus that lists all of the statuses that an order can be in. Lookup table like this are useful for populating combo-boxes and running reports. And a foreign key constraint on the status column ensures the status of each order is within the set.

But within code, the lookup table is not necessary. I created an enumeration for the status values. The IDs were well-known. I didn't care about the descriptions.

I brought both the Order entity and the OrderStatus entity into the EDMX. The Order entity so I could create orders, and the OrderStatus entity to populate a combo-box. Since the Order table has a foreign key constraint relating it to OrderStatus, Entity Framework created a relationship. This caused problems.

When inserting an Order, I needed to set its status. So I created an OrderStatus and set its ID:

order.OrderStatus = new OrderStatus() { OrderStatusId = OrderStatusEnum.Submitted };

Entity Framework did not see this as setting the foreign key. Instead, it saw this as inserting both an Order and an OrderStatus. The result was a uniqueness constraint violation. The correct way to do this is to query for the existing order status, and then set the reference in the new order.

OrderContainer container = new OrderContainer();
order.OrderStatus =
  container.OrderStatus
    .Where(s => s.OrderStatusId == (byte)OrderStatusEnum.Submitted)
    .First();

That just seems like too much work to set a foreign key in a row that I'm inserting. I decided instead to delete the relationship that EF had created between Order and OrderStatus. Upon doing so, I received this error validating the model.

Foreign key constraint 'FK_Order_OrderStatus' from table Orders (OrderStatusId) to table OrderStatus (OrderStatusId):: Insufficient mapping: Foreign key must be mapped to some AssociationSet on the conceptual side.

Entity Framework had pulled the foreign key constraint in from the database schema, and it needed to be mapped. I just deleted the association on the "conceptual side" (i.e. the EDMX designer surface) that represented that constraint.

Here's my solution
I actually have three. First, I could go back to my database and delete the foreign key constraint. This would make EF happy, but it would also remove the extra check on order status. It would take away some information that could be used by reporting tools. The foreign key constraint is the correct model, relationally, and I did not want to violate that model to satisfy EF. So I didn't do it.

Next, I could move OrderStatus to its own EDMX. I took this approach on other parts of the system that had several lookup tables, creating one single Lookup.EDMX file for all of them. This would prevent EF from importing the related tables in the same context, and would prevent it from creating the relationship. This seemed a bit much in this case, since I didn't have any other lookups, so I didn't do it.

Finally, I could remove the imported foreign key constraint. This requires hand-editing the XML, since the model browser doesn't allow you to delete any of the constraints that it has imported. To edit the XML, right-click on the EDMX file in solution explorer, select "Open With..." and then "XML Editor".

Find the AssociationSet element that refers to the foreign key that's giving you trouble. I deleted this chunk of XML:

<AssociationSet Name="FK_Order_OrderStatus" Association="Order.Store.FK_Order_OrderStatus">
  <End Role="OrderStatus" EntitySet="OrderStatus" />
  <End Role="Orders" EntitySet="Orders" />
</AssociationSet>

After cleaning that up, my EDMX validated again. Finally, to set the foreign key myself, I added a scalar property and mapped it to the column in the table.

This is another case of the tool trying too hard to help you out. It can't tell based on the relational model that this is a lookup table, and that it is not supposed to insert rows. Entity Framework tries very hard to keep you from managing foreign keys yourself. But the database is relational, and you do need to know about constraints and foreign keys. You have to be incredibly forceful to get the tool to move out of your way and let you see the model as it truly is.

4 Responses to “Lookup tables should not be modeled as related entities in Entity Framework”

  1. Jonathan Ranes Says:

    Nice post. I am wrestling with this same aspect of entity myself. Sometimes I wish I had just done the projects with NHibernate but there is a lot about entity I like. I agree though that when you have to go edit the xml just to tell it to do something that should be standard operating procedure. The next version better have this fixed.

  2. Jonathan Ranes Says:

    What happens when you update the model from the database after this. Do you have to keep removing the xml. Oh well I am about to find out.

  3. Jonathan Ranes Says:

    Not to talk your leg off but an area I am having to do this a lot with is to accommodate not getting circular references for serializing my objects to json. I have been making entity objects off of views to do this but in many cases if i can tweak the model to ignore a relational key it seems like a better fix.

    How are you getting rid of circular references for javascriptserializer?

  4. Robert Hanlon Says:

    Thanks for the great post. I like how you identified the root cause, then gave three potential solutions and the situations in which you'd use them. You saved me quite a chunk of time; keep up the good work!

Leave a Reply

You must be logged in to post a comment.