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:
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:
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:
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:
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.
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.
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.