28 December 2010

Inferring Foreign Key Constraints in Entity Framework Models (Part 2)

In my last blog entry, Inferring Foreign Key Constraints in Entity Framework Models, I wrote about a new feature in the Model Comparer for EFv4 that allow foreign key constraints to be inferred and added to an Entity Framework model even if some or all FKs are missing in the database. The inferred FKs can then be used to generate associations and navigation properties in Entity Framework models.

In the latest release of Huagati DBML/EDMX Tools, the FK inference feature has been improved with a new regex match-and-replace feature for matching foreign key columns to primary key columns. This allows FKs to be inferred even if the FK/PK columns to be matched use inconsistent naming and/or are named with special prefixes/suffixes etc.

Of course, the basic name matching feature described in the previous article is still there, and can be used under many (if not most) common scenarios. However, when the PK/FK naming is more complex then using Regex match+replace comes in handy for resolving what columns in one table can be mapped to the PK in another table.

The following screenshot shows the settings dialog with the new settings for RegExp-based column matching:


In addition to allowing the standard regular expression language elements to be used in the Match field, and the substitution elements to be used in the Replace field, there are two reserved keywords that can be used in the replacements as well; %pktable% which is replaced with the name of the primary-key-side table and %fktable% which is replaced with the name of the foreign key table when the match is evaluated.

The above screenshot shows how regular expression replacements plus the %pktable% substitution keyword can be used to match two tables even if the PK column is named “id” and the FK column in another table is named using the PK table’s name followed by “_key”; if the result of the Match+Replace on both sides evaluate to the same value, a foreign key constraint is inferred and will be displayed in the diff-tree and diff-reports in the Model Comparer’s main window.

The Model Comparer

If you want to read more about the Model Comparer and the other features for Entity Framework contained in the Huagati DBML/EDMX Tools add-in for Visual Studio, see the following links:

Introducing the Model Comparer for Entity Framework v4
What’s new in the latest version of the Model Comparer for Entity Framework 4
Using the Model Comparer to generate difference reports for Entity Framework v4 models
Mixing inheritance strategies in Entity Framework models
SQL Azure support in Huagati DBML/EDMX Tools
Inferring Foreign Key Constraints in Entity Framework Models
Simplify Entity Framework v4 models with complex types

…and of course, there is a brief summary of the add-in’s supported features for both Entity Framework and Linq-to-SQL at the product page: http://huagati.com/dbmltools/ where you can also download the add-in and get a free 30-day trial license.

No comments:

Post a Comment