The Dark Side of Entity Framework: Mapping Enumerated Associations

Published by marco on

This article was originally published on the Encodo Blogs. Browse on over to see more!


At Encodo, we’re using the Microsoft Entity Framework (EF) to map objects to the database. EF treats everything—and I mean everything—as an object; the foreign key fields by which objects are related aren’t even exposed in the generated code. But I’m getting ahead of myself a bit. We wanted to figure out the most elegant way of mapping what we are going to call enumerated associations in EF. These are associations from a source table to a target table where the target table is a lookup value of type int. That is, the enumerated association could be mapped to a C# enum instead of an object. We already knew what we wanted the solution to look like, as we’d implemented something similar in Quino, our metadata framework (see below for a description of how that works).

The goals are as follows:

  1. Properties of the enumerated type are stored in the database, including its identifier, its value and a mapping to translations.
  2. Relations to the enumerated value are defined in the database as constraints.
  3. The database is therefore internally consistent
  4. C# code can work with an enumerated type rather than a sub-object; this avoids joining the enumerated type tables when retrieving the main object or restricting to the enumerated type’s value.

EF encourages—nay, requires—that one develop the application model in the database. A database model consists of tables, fields and relationships between those tables. EF will map those tables, fields and relationships to classes, properties and sub-objects in your C# code. The properties used to map an association—the foreign keys—are not exposed by the Entity Framework and are simply unavailable in the generated code. You can, however, add custom code to your partial classes to expose those values[1]:

return Child.ParentReference.ID;

However, you can’t use those properties with LINQ queries because those extra properties cannot be mapped to the database by EF. Without restrictions or orderings on those properties, they’re as good as useless, so we’ll have to work within EF itself.

Even though EF has already mapped the constraint from the database as a navigational property, let’s add the property to the model as a scalar property anyway. You’ll immediately be reprimanded for mapping the property twice, with something like the following error message:

Error 3007: Problem in Mapping Fragments starting at lines 1383, 1617: Non-Primary-Key column(s) [ColumnName] are being mapped in both fragments to different conceptual side properties − data inconsistency is possible because the corresponding conceptual side properties can be independently modified.

Since we’re feeling adventurous, we open the XML file directly (instead of inside the designer) and remove the navigational property and association, then add the property to the conceptual model by hand. Now, we’re reprimanded for not having mapped the association EF found in the database, with something like the following error message:

Error 11008: Association ‘FOREIGN_KEY_NAME’ is not mapped.

Not giving up yet, we open the model in the designer again and delete the offending foreign key from the diagram. Now, we get something like the following error message:

Error 3015: Problem in Mapping Fragments starting at lines 6680, 6699, 6716, 6724, 6801, 6807, 6815: Foreign key constraint ‘FOREIGN_KEY_NAME’ from table Source (SourceId) to table TargetType (Id):: Insufficient mapping: Foreign key must be mapped to some AssociationSet on the conceptual side.

The list of line numbers indicate where the foreign key we’ve deleted is still being referenced. Despite having used the designer to delete the key, EF has neglected to maintain consistency in the model, so it’s time to re-open the model as XML and delete the remaining references to ‘FOREIGN_KEY_NAME’ manually.

We’re finally in the clear as far as the designer and compiler are concerned, with the constraint defined as we want it in the database and EF exposing the foreign key as an integer—to which we can assign a typecast enum—instead of an object. This was the goal, so let’s run the application and see what happens.

Everything works as expected and there are no nasty surprises waiting for us at runtime. We’ve got a much more comfortable way of working with the special case of enumerated types working in EF. This special case, arguably, comes up quite a lot; in the model for our application, about half of the tables contain enumerated data, which are used as lookups for reports.

It wasn’t easy and the solution involved switching from designer to XML-file and back a few times[2], but at least it works. However, before we jump for joy that we at least have a solution, let’s pretend we’ve changed our database again and update the model from the database.

Oops.

The EF-Designer has detected the foreign key we so painstakingly deleted and re-established it without asking for so much as a by-your-leave, giving us the error of type 3007 shown above. We’re basically back where we started … and will be whenever anyone changes the database and updates the model automatically. At this point, it seems that the only way to actually expose the foreign key in the EF model is to remove the association from the database! Removing the constraint in the database, however, is unacceptable as that would destroy the relational integrity just to satisfy a crippled object mapper.

In a last-ditch effort, we can fool EF into thinking that the constraint has been dropped not by removing the constraint but by removing the related table from the EF model. That is, once EF no longer maps the destination table—the one containing the enumerated data—it will no longer try to map the constraint, mapping the foreign key as just another integer field.

This solution finally works and the model can be updated from the designer without breaking it—as long as no one re-adds the table with the enumerated data. This is the solution we’ve chosen for all of our lookup data, establishing a second EF-model to hold those tables.

  • The main model contains non-enumerated data; relations to enumerated data end in integer fields instead of objects.
  • The lookup model contains a list of enumerated data tables; these are queried for the contents of drop-down lists and so on.
  • We defined an enumerated type in C# for each table in the lookup model, with values corresponding to the values that go in the lookup table.
  • We wrote a synchronizer to keep the data in the lookup tables synchronized with the enum-values in C#.
  • Business logic uses these enumerated types to assign the values to the foreign-key integer fields (albeit with a cast).

Using Quino to Solve the Problem

It’s not a beautiful solution, but it works better than the alternative (using objects for everything). Quino, Encodo’s metadata framework includes an ORM that addresses this problem much more elegantly. In Quino, if you have the situation outlined above—a data table with a relation to a lookup table—you define two classes in the metadata, pretty much as you do with EF. However, in Quino, you can specify that one class corresponds to an enumerated type and both the code generator and schema migrator will treat that meta-class accordingly.

  • The code generator maps relations with the enumerated class as the target to the C# enum instead of an object, automatically converting the underlying integer foreign key to the enumerated type and back.
  • The schema migrator detects differences between the C# enumerated type and the values available in the lookup table in the database and keeps them synchronized.
  • As simple integer enums, the values can be easily restricted and ordered without joining extra tables.
  • Generated code used the C# enumerated type, which ensures type-safety and code-completion, including documentation, in business code.

EF has a graphical designer, whereas Quino does not, but the designer only gets in the way for the situation outlined above. Quino offers an elegant solution for lookup values with only two lines of code: one to create the lookup class and indicate which C# enum it represents and one to create a property of that type on the target class. The Quino Demo (not yet publicly available) contains an example.


[1] You can also try to modify the T4 templates used to generate code, but that would be futile for reasons that follow.
[2] Which is, frankly, appalling, but hardly unexpected for a 1.0 product from Microsoft, which usually needs a few tries to get things working smoothly.