Designing an airline passenger reservation system

A problem frequently plaguing those brave enough to us the Entity Framework v1 is mapping exceptions. Whoa, that sounds negative. But the fact remains: it relatively easy to use the EF designer to turn the model into something that will throw a MappingException error at runtime.

In short, the cause of this is that despite EF being designed with a layered model (SSDL describing the storage/db schema, CSDL describing the object model, and MSL defining the mappings between the two), the designer supports editing CSDL only. So when you add a new entityset/entitytype in the EF designer it is added to the CSDL but not to SSDL. Or if you remove an entityset/type using the designer it is only removed from the CSDL/MSL but is left intact in the SSDL. Make a bunch of model changes and you suddenly have a mess of orphaned entitysets and entitytypes in your SSDL and CSDL.

This wouldn’t be such a big deal if it wasn’t for EF throwing runtime errors for all unmapped entities. And that the EF designer’s “update model from database” feature will replace the entire SSDL when updating instead of applying changes corresponding to the differences between the model and the database.

When entities are orphaned Visual Studio gives no or little hints that anything is wrong with the model if you just make changes and then compile. There is a “validate” command in the EF designer but it is not triggered by compilation and it will just present the validation errors without offering any solutions.

The compiler happily compiles executables. But when you run your app you will get nice runtime exceptions such as:

System.Data.MappingException:
SomeRandomModel.msl(3,4) : error 3027: No mapping specified for the following EntitySet/AssociationSet - testSet.

…and:

System.Data.MappingException:
NorthwindEF.msl(93,10) : error 3013: Problem in Mapping Fragments starting at lines 93, 138: Missing table mapping: Foreign key constraint 'FK_Products_Suppliers' from table Products (SupplierID) to table Suppliers (SupplierID): no mapping specified for the table Suppliers.

…even though the unmapped entities are not referenced or used in code. This is a side effect of EF loading the entire XML model description at runtime…

Not a problem - there’s a good workaround: just open up the EDMX file in your favorite XML editor and remove the offending entities from the appropriate part of your model. Or use the “Update from model” command to regenerate the SSDL (sans any customizations you may have made).

This is all fine and nice, but manually editing EDMX files is not really an efficient way to develop software. Especially when there are a lot of iterative changes.

So… enter the “EDMX Cleanup” utility. This is the latest addition to Huagati DBML/EDMX Tools; a new command that will assist with cleaning up orphaned entitysets/entitytypes without the need for an xml editor. The first version supports creating CSDL equivalents from orphaned SSDL entities, or alternatively dropping orphaned SSDL. Creating SSDL/dropping CSDL will be added in the next version.

This is included as of version 1.49 which is currently in beta. You can download your beta-copy of ver 1.49 today from:
http://www.huagati.com/dbmltools/download/HuagatiDBMLExtensions_149_beta.zip

To get the EF model cleanup feature shown above, download Huagati DBML/EDMX Tools version 1.50 or higher from http://www.huagati.com/dbmltools/

(If you don’t have a license key, retrieve a free 30-day trial license here.)

After playing around a bit with the ADO.NET Entity Framework designer in Visual Studio 2008 SP1 I was a bit disappointed with its’ user friendliness (or lack thereof). I guess this can be read between the lines in my previous entry on EF.

Instead of waiting for the next service pack for Visual Studio I figured it is more constructive to try to work around the shortcomings in Visual Studio by adding the missing functionality. So I started adding some edmx-related functionality to the Huagati DBML Tools add-in package. That immediately makes the original choice of name for the add-in a bad one, so I guess from now on it will be “Huagati DBML and EDMX tools”…

EDMX Addin #1: Renaming entity sets, entity types, properties, and navigation properties in EDMX (ADO.NET Entity Data Model) files

The first function, released today, is a renaming function that goes through all entity sets, entity types (classes), properties, and navigation properties in a EDMX file (EF designer) and renames them according to a user-selectable set of rules.

This is more or less similar too the dbml naming convention add-in but this one works with the Entity Framework designer instead of the Linq-to-SQL designer. It updates the conceptual and mapping side and also comes with a new shiny options dialog allowing users to specify what naming rules to apply. The options dialog also has a “preview” field for each section at the bottom of the screen that shows what the outcome will be on classes/properties in the currently open edmx file if using the selected settings.

Please allow me a screenshot-by-screenshot demonstration/walk-through:

1) Right after using the edmx import wizard, the conceptual layer objects; entity sets (accessor properties), entity types (the classes representing tables/views/etc), properties (fields/members) all use the names from the database. This is fine if the naming conventions used in the database match .net naming conventions, but in reality many people use other naming conventions in database schemas than in .net code for a variety of reasons.

Manually renaming all entity sets, entity types and properties to use .net naming conventions after running the EF import database wizard - for nice standardized names in the .net code - can be a lengthy exercise already on a medium sized data model.

(Click on the screenshot for a larger view)

2) The add-in plugs into the Tools menu in Visual Studio, adding a new option “Standardize ADO.NET Entity Data Model class and member names” whenever the entity data model designer is open. (Long menu name, I know. Maybe I’ll rename it some day…)

3) After selecting the “standardize…” tool, an options dialog will appear. This dialog allows the user to control whether to apply ProperCase (TitleCase) to names, remove underscores, pluralize accessors, remove suffixes/prefixes such as “tbl_”, “int_”, add new prefixes/suffixes etc etc.

(Click on the screenshot for a larger view)

4) ..and after hitting OK in the options dialog, the add-in will process all entity sets, entity types, properties, navigation properties etc and update the names according to the rules specified:

(Click on the screenshot for a larger view)

It is all pretty straightforward, but this function really saves a lot of time when importing database schemas into the Entity Framework designer. As the screenshot above shows it spared me from having to re-type (or rename) the 1777 classes and properties generated from the HuagatiRes database by hand, and instead automated the whole process and updated the entire conceptual layer in a matter of seconds.

This new add-in function along with other (yet to be released) EDMX / EF-related functionality comes packaged together with the DBML functionality in the Huagati DBML Tools add-in. If you want to try it out, you can download a trial version and get a trial license key from http://www.huagati.com/dbmltools/ , and if you like it I hope that you upgrade to either one of the paid-for versions to support continued development.

If you already have a license for the DBML Tools add-ins, you can upgrade by simply downloading and copying the latest version over your existing version and re-start Visual Studio.