Huagati DBML/EDMX Tools is now free to use

The latest version of Huagati DBML/EDMX Tools (v 2.34, released on 27 Apr 2014) has all license checks removed. This means it is now free to use. $0/user, no cost, no strings attached.

Grab your copy at https://www.huagati.com/dbmltools/

Entity Framework Model Creation beyond ‘Database First’ / ‘Model First’

The built in tools for Entity Framework 4 in Visual Studio forces users to make a distinct choice between the “Database First”, “Model First”, or “Code First” model creation and update strategies. A recent MSDN Magazine article by Julie Lerman describes the options and choices that has to be made on which approach to use for updating the model.

I think those three choices are a bit limiting in that not being able to continually evolve both the model and the underlying database is not an ideal situation for many projects. Applications evolve and so do the underlying databases, often with changes made by several people using different tools. That is the reason why I created the Model Comparer for EF4 in the first place.

Even if the model is based on an existing database, a.k.a. ‘Database First’, I think it should be possible to use the EF designer built into Visual Studio to update the model and add/remove/change entities/tables, members/columns, associations/foreign keys etc.

Likewise, if the model is designed from scratch in the EF designer (‘Model First’), it should be possible to make incremental changes with SQL-DDL diff scripts, as well as making schema changes directly in the database and sync those changes back to the model.

I made a short (4½ minute long) screencast illustrating how this is possible to do with the EFv4 visual designer together with the model comparer. In this video I make changes both to the database in SQL Server Management Studio and to the model in Visual Studio and then sync it up to replicate the changes into both the database and the model.

Enjoy:

The video above is also available on youtube at http://www.youtube.com/watch?v=doqYOlcEAZM , although the youtube version is lower resolution and has more MPEG compression artifacts than the one embedded above.

Oh, and if you want to try it out yourself, you can download the add-in that makes this possible from http://huagati.com/dbmltools/ and get a free trial license from the same site.

Redistribution licenses for the HuagatiEDMXTools.dll EDMX file object model

In an earlier blog post, Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll, I wrote about the EDMX file wrapper that is used by the Huagati DBML/EDMX Tools add-in for parsing, updating, creating, and writing EDMX files. HuagatiEDMXTools adds an easy to use object model on top of EDMX files (see documentation at http://huagati.com/edmxtools/help ), making it a lot easier to create/read/update EDMX files, and to write LINQ queries against Entity Framework 4 metadata. It also has a number of built-in queries and lookup functions that make it a breeze to work with Entity Framework 4 EDMX files from code.

Previously, using the HuagatiEDMXTools runtime library required an end-user license for Huagati DBML/EDMX Tools. As of the latest version (v 2.20) this has been extended with a new redistributable license option, allowing third party applications to use and include HuagatiEDMXTools.

Redistribution licenses are divided into three categories:

• Non-development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products not primarily targeted at software developers and whose primary purpose is not to develop software. This may be line-of-business applications that need to generate, update, or read EDMX data to fulfill some functional requirement.
• Development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products whose primary purpose is to assist software development. This may be a tool specifically for working with Entity Framework 4 EDMX files, such as a new model editor or EF model designer.
• Source code license: Source code licenses includes the source code for the HuagatiEDMXTools runtime library. The source code can not be redistributed, and any libraries/assemblies based on or derived from it may only be distributed as part of another software product that is not substantially similar to or competitive with HuagatiEDMXTools. (It may however be used for products competitive with the Huagati DBML/EDMX Tools add-in.)

Pricing and additional details are available at http://huagati.com/dbmltools/

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.

Inferring Foreign Key Constraints in Entity Framework Models

Foreign key constraints are one of the most important components of relational database models. At the database level they are key to maintaining data integrity and prevents applications and users from writing invalid data or from orphaning data by inadvertently deleting data referenced from other tables.

When reverse-engineering entity relationships in modelling tools and OR mappers, foreign key constraints serve yet another important role; they also describe the relationship between tables/entities. Without foreign key constraints, OR mappers can’t generate navigation properties between entities, and will not know in what order* it should process inserts, updates, and deletes for various tables.

* = e.g. generating order detail records before the main order record has been created.

Databases with no FK constraints

Unfortunately, there are lots of real-world databases that - although they are created and deployed in RDBMSes – lack foreign key constraints. The reasons for omitting FK constraints vary, but it is not uncommon that the reasons stated are based on misconceptions, misunderstandings, or lack of information. Sometimes they are omitted by programmers that can’t get their head around proper order of inserts/deletes, sometimes they are omitted “for performance reasons”, sometimes noone can remember anymore why they were omitted. Whatever the reason, relational databases with some or all FK constraints missing are out there and they’re fairly common.

Adding FK constraints to legacy databases

One way to tackle FK-less databases is simply to add the missing FK constraints. Simple solution to data integrity issues, and makes OR mappes happy, right? Unfortunately it is not that simple. The real problem with FK-less databases is the applications behind them. If there are tens-of-thousands, hundreds-of-thousands, or millions of lines of code that interact with those databases you can be certain that some of it will break if you just add all the missing FK constraints. There’s almost certainly code that do inserts/deletes in the wrong order, and there’s a fairly good chance that there is junk data or orphaned data that will break the FK constraint already during creation, and so on.

Inferring FK constraints

Since FK constraints are key to generating the associations in OR mappers such as Entity Framework, I decided to add a feature to infer FKs. Instead of creating them in the database, I use table and column names and types to deduce what might be a FK candidate in the database. After that, the inferred FKs are displayed in the Model Comparer’s list of missing FK constraints.

The user can choose and pick both real and inferred foreign key constraints and add them to the model selectively, and generate model-level associations and navigation properties without actually creating the FKs in the database.

The tool also allows inferred FK constraints to be materialized into SQL-DDL scripts, but that part is entirely optional.

Demonstration

The following set of screenshots demonstrate how the FK inference feature in the Model Comparer for EFv4 can be used to infer FKs in a database and to add selected keys to the EF4 model.

Step 1 – open a EFv4 model in Visual Studio 2010, and bring up the Model Comparer:

Step 2 – Click on Settings to open the settings dialog. Go to the Infer Foreign Keys tab.

Select the type of objects you want to infer FKs for; tables and/or views. Although views can not have FK constraints in the database it is perfectly fine to infer FKs and use them to add associations to views. This makes it possible to have navigation properties to/from views.

There are four different name matching options controlled by two checkboxes: whether PK members contain the name of the entity or not, whether entity names and member names are separated with underscores or not. Changing the checkboxes updates the table example in the middle of the dialog between the four different naming conventions, to make it easy to verify that the settings match the desired naming conventions.

 

Step 3 – with the Infer Foreign Key Constraints for Tables setting and/or the Infer Foreign Key Constraints for Views setting enabled, the model comparer will infer FK constraints where they don’t exist in the database. Inferred FK constraints start with the FKI prefix and has a different icon than existing FK constraints to make it easy to differentiate between inferred and real FK constraints.

 

Step 4 (optional) – The Report view also show all inferred keys. If generating a lot of keys the report view is a more eye-friendly way to go through them in order to determine which ones are valid/desired in the model.

 

Step 5 – Select the FK constraints that you want to use for generating associations in the model. Click Update SSDL to add them to the storage model (SSDL) in the Entity Framework 4 model.

 

Step 6 (optional) – The SSDL-to-DB tree will now show the newly added association. This allows inferred FKs to be materialized as SQL-DDL-scripts if desired. If not, select them and click Ignore. If you want to generate a SQL-DDL script, allowing the inferred FKs to be added to the database, select the FKs and click on Update Database.

 

Step 7 (optional) – If you generated SQL-DDL in Step 6, the SQL-DDL script will appear in a separate SQL editor in the Visual Studio instance that the Model Comparer belongs to. This allows you to review, edit, and save the script before applying to the database.

 

Step 8 – Select the new association(s) in the SSDL-to-CSDL tree and click Update CSDL to add them to the conceptual layer of the model. This will result in navigation properties added between the involved entities.

Step 9 – Close the model comparer and return to Visual Studio. Continue coding. Enjoy.

 

The Huagati Model Comparer

The Model Comparer is a feature in the Huagati DBML/EDMX Tools add-in for Visual Studio 2010. The add-in can be downloaded from http://huagati.com/dbmltools/ , and time-limited free trial licenses are available from the same site.

Screencasts showcasing some of the basic functionality in the Model Comparer for EFv4 is available at http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html and http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

Query Profiling SQL Azure when using Entity Framework or Linq-to-SQL

One slight shortcoming in Microsoft’s SQL Azure (the SQL Server 2008 flavor that is hosted  in Microsoft’s cloud platform) is that users don’t have the trace privileges needed to use SQL Profiler to profile query and performance behavior. Fortunately there are other ways to extract performance data for individual queries; SQL Azure exposes IO statistics, timings, and execution plans in the same way as normal non-cloud editions of SQL Server.

I have recently done some testing against SQL Azure using my tools. Huagati DBML/EDMX Tools needed some minor adjustments to work against SQL Azure as outlined in a previous blog post: http://huagati.blogspot.com/2010/12/sql-azure-support-in-huagati-dbmledmx.html

Next up was testing the profiling / logging components for Entity Framework 4 and Linq-to-SQL in Huagati Query Profiler against SQL Azure. Fortunately I can announce that it works just fine; the techniques used by the Huagati Query Profiler’s logging components for capturing server-side timings, I/O statistics, query execution plans are all supported in SQL Azure. The log entries will look the same and the same performance data and filtering options that work against local SQL Server instances can be used against SQL Server in the cloud.

One interesting thing I noticed while testing is that the roundtrip times against the SQL Azure instance I am accessing is only 100-150ms above accessing a database on my local LAN. That is impressive considering I am in Thailand and the SQL Azure instance is in Singapore, and faster than what I have seen mentioned in some forums and blog posts from US SQL Azure users.

To get started, download and install the Huagati Query Profiler from http://huagati.com/l2sprofiler/ and take a look at these blog posts and/or the sample code that is installed together with the profiler:

Linq-to-SQL: http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
Entity Framework: http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html

Next, you can either try the sample projects that ship with the profiler against your own SQL Azure instance, or add profiling support to your own Entity Framework 4 or Linq-to-SQL based projects against a SQL Azure database.

Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll

Microsoft’s Entity Framework stores models as xml-based EDMX files. Within the EDMX file, the model is divided up into the conceptual layer (CSDL) which describes the C# or VB entity classes and methods, the storage layer (SSDL) which describes the database tables/keys/views/procedures etc, the mapping layer (MSL) which describes how everything in the conceptual model maps to the storage model, and finally one portion describing the designer diagram layout.

The EDMX files are typically generated and modified by the designer in Visual Studio, by edmgen.exe/edmgen2.exe, or by third party tools such as the model comparer in Huagati DBML/EDMX Tools.

Reading/writing EDMX files

Sometimes people look for ways to generate, modify, or parse EDMX files from code. The straightforward answer to that is usually to use an XML parser; XmlDocument, XmlReader/XmlWriter, Linq-to-XML etc. Using XML parsers to read and write EDMX files works fine, but it also requires a fair bit of time spent on reading the CSDL, SSDL, and MSL specifications as well as some manual work figuring out some of the things not clearly spelt out in the specification such as how different common or uncommon mapping scenarios are best described in the MSL.

The HuagatiEDMXTools.dll library

Before developing the new Entity Framework 4 features in Huagati DBML/EDMX Tools, I decided to step away from directly using an XML parser and instead add an intermediate abstraction layer. That makes it a lot easier to work with the models and separates out most of the quirky mapping logic. The result was a separate wrapper library called HuagatiEDMXTools.dll . Underneath it still uses a XML parser, but all model artifacts are exposed as classes, properties, IEnumerables, IQueryables etc. Some related object types such as associationsets and associations are merged together, related objects available as properties, and useful queries and lookups are exposed as simple lookup properties and IQueryables/functions.

Although HuagatiEDMXTools was written to support the EFv4-related functionality in Huagati DBML/EDMX Tools, I decided to document it to allow add-in users to write their own code to create/modify/read EDMX models for scenarios that are not covered by the add-in or other tools. Although the library has been around and exposed since version 1.90 and for the most part fairly self-explanatory, the documentation for it wasn’t released until version 2.11. The documentation is in MSDN format (thanks, Sandcastle and SHFB), and is available online at http://huagati.com/edmxtools/help/ as well as in the form of a HTML help file that is installed together with the Huagati DBML/EDMX Tools add-in.

To use the HuagatiEDMXTools library from a VS project, simply add a reference to the file HuagatiEDMXTools.dll from the project references:

When using the library, start by initializing the licensing module by passing the license data from the add-in’s license file ( \users\[username]\Documents\Visual Studio 2010\Addins\HuagatiDBMLTools2010.dll.lic ) to the license class:

Samples

Next, you can use the library to create, modify, read EDMX files, or simply to query for various model metrics using some of the built-in queries, or by writing new ones against the many IEnumerables and IQueryables exposed by the library.

Download

As I mentioned above, the HuagatiEDMXTools library ships with Huagati DBML/EDMX Tools. You can download the latest version of Huagati DBML/EDMX Tools from http://huagati.com/dbmltools/ and get a license (free trial, or a full paid license) from the same site.

The online help for the library is available at http://huagati.com/edmxtools/help/ as well as in a .chm file that is installed together with Huagati DBML/EDMX Tools.

SQL Azure support in Huagati DBML/EDMX Tools

Recently I have been asked by several users if Huagati DBML/EDMX Tools supports SQL Azure. Until now, the answer was no; SQL Azure has some limitations compared to normal SQL Server 2008/2005/2000. However, after the question was asked again in the support forum this morning I decided to take another look at it.

I came across two minor obstacles:

1) SQL Azure supports only a subset of SQL Server’s system views. The add-in rely on several of these to extract schema data, documentation, indexes etc from the database.

2) SQL Azure is technically not available from Thailand until August 2011, and being based in Thailand I couldn’t directly sign up for it.

The first one is simple to solve in code, if connecting to SQL Azure the add-in simply have to avoid using some SQL Server features such as extended properties until those are supported in SQL Azure. This means the documentation sync feature in the add-in will not be able to retrieve documentation information from SQL Azure, since SQL Azure can not store it.

The second one was solved by Microsoft Thailand’s developer evangelist, Khun Ekaraj, who was nice enough to set me up with a one month SQL Azure trial pass. That allowed me to validate that the add-in worked against SQL Azure with the necessary changes applied.

As of today’s release, version 2.12, SQL Azure is now supported from Huagati DBML/EDMX Tools when running under Visual Studio 2010.

Mixing inheritance strategies in Entity Framework models

The Microsoft Entity Framework v4 runtime supports three entity inheritance models: TPT, TPH, and TPC. When using the built-in EF designer and tools in Microsoft Visual Studio 2010, the default inheritance strategy in model-first scenarios is TPT. TPH inheritance can be manually mapped in the designer, and TPC requires editing the mapping (MSL) in the EDMX file manually or using third party tools.

TPT, TPH, and TPC explained

TPT is short for Table Per Type. In TPT inheritance, there is one central table containing all the base class members and then one table per subclass containing the key and the members that are immediate (non-inherited) members of the subclass. In database terms this can be described as a normalized model, with additional tables for members that are not always present.

TPH is short for Table Per Hierarchy. In TPH inheritance there is only one table representing the base class and all subclasses. Members must be nullable to avoid errors when a subclass don't contain members of another subclass. In database terms TPH can be described as a denormalized model, since one wide table is used to represent all of the types in the hierarchy with columns that are left with a null value if they are not members of the entity type.

TPC is short for Table Per Concrete type. In TPC inheritance, every class in a inheritance hierarchy will have their own table. The inheritance hierarchy masks the fact that there are several independent underlying tables, representing each subtype.

Choosing inheritance strategy in Entity Framework

Choosing an inheritance strategy when using the VS2010 built-in tools often means making a model-wide decision on what type of entity inheritance to use. If you work in a 'Model First' pattern, where you create your entity classes in the model designer and then generate SSDL and the database from that, the designer will by default use TPT inheritance for all hierarchies. Microsoft have some plugins that can add model-first TPH support to the designer, and if you want to use TPC inheritance you need to do your mappings by hand using an XML editor or third-party tools.

Microsoft's Alex James has written a nice blog post on how to choose inheritance strategy, that describes the differences and merits to each of the inheritance strategies supported by Entity Framework:
http://blogs.msdn.com/b/alexj/archive/2009/04/15/tip-12-choosing-an-inheritance-strategy.aspx

However, I don't think the decision of what inheritance strategy to use should be a model-level decision. Each of the inheritance strategies have their merits and drawbacks:

• TPT inheritance is sleek storage wise, the tables involved contain only the valid members. However, the SQL queries generated for complex TPT hierarchies can easily become very large and complex even for simple hierarchies. For complex hierarchies there can be a noticeable overhead in generating the queries, when SQL Server generates execution plans, and in cost of executing the queries.
  
  Even with a handful of subtypes, TPT hierarchies require EF to create all possible joins and then create a big UNIONed query covering all options, unless the query specifically asks for a single type within the hierarchy.
  
  Some of the performance considerations related to TPT are outlined in a blog post in the EF team’s blog:
  http://blogs.msdn.com/b/adonet/archive/2010/08/17/performance-considerations-when-using-tpt-table-per-type-inheritance-in-the-entity-framework.aspx
  
• TPH inheritance results in wider tables with many nullable columns, since all types within a inheritance hierarchy maps to the same table. Nullable columns opens up the possibility for missing values going undetected (bad data), and wide tables will use more storage space than narrower tables.
  
  However, the SQL queries generated for TPH hierarchies are more simple and TPH inheritance can have a performance advantage over TPT both when generating queries and when running the queries. (Although it is worth noting that it doesn’t always have a performance advantage; pulling a few fields from a very wide table with many records can result in a lot of unnecessary I/O).
  
• TPC inheritance is also good storage-wise since each type has its own table with no redundant columns. However, due to having multiple tables, TPC adds some constraints on the choice of keys.
  
  Using SQL Server’s int identity columns don't work very well together with TPC since there will be duplicate entity keys when inserting in different tables all having their own identity seed, so either a spread seed (where each table has its’ own initial seed value) will be needed, or a mechanism other than SQL Server’s identity keyword is needed. Some other RDBMSes have other mechanisms allowing a sequence to be shared by multiple tables, and something similar can be achieved with customized default constraints in SQL Server. Using GUID keys, or int identity keys with different starting ranges solves that problem.
  
  TPC is not supported by the VS2010 EFv4 designer’s mapping editor, so mapping TPC requires using an XML editor or third-party tools.
  
  Queries against TPC hierarchies, like TPT hiearchies, makes EF generate UNIONed queries, although the queries for TPC hierarchies are typically less complex than the queries generated for TPT inheritance hierarchies since all tables are self-contained and no joins are needed between base- and subtype tables, and no additional unions when the inheritance hierarchy is more than one level deep.

Depending on complexity of the inheritance hierarchy, the shape and volume of the data, the type of queries, etc., there are often good reasons to use different inheritance strategies within one model. Instead of a model-level decision, I think it is necessary to allow inheritance strategy to be decided on a case-by-case basis for each inheritance hierarchy within a model.

New inheritance strategy settings in the Model Comparer

Enter the third party tools. The latest version of the Model Comparer for EFv4 in Huagati DBML/EDMX Tools has added support for all three inheritance strategies supported by Entity Framework: TPT, TPH, and TPC. The settings dialog for the model comparer has a new setting allowing users to pick the default inheritance strategy used in model first scenarios; when creating SSDL/MSL/SQL-DDL from CSDL.

In addition to allowing a model-level default inheritance strategy for model-first scenarios, there is a fourth inheritance strategy setting called "Mixed".

When the inheritance strategy setting is set to Mixed, a dropdown for choosing inheritance strategy is displayed in the conceptual model tools area in the model comparer. This enable developers to pick inheritance strategy on-the-fly when adding new inheritance hierarchies to the model. The SSDL and MSL generated by the model comparer's "Update SSDL" feature will then be structured based on what is selected in the dropdown.

Caveats

• Does this mean inheritance strategies can be mixed within one inheritance hierarchy? No, it doesn't. Each inherited tree of entity types must stick to one inheritance strategy, but independent inherited hierarchy of entities within the model can use different inheritance strategies. To enforce this, the model comparer stores the selected inheritance strategy for each CSDL entity set when generating SSDL, and will reuse the same inheritance strategy if e.g. a new subtype is added within a hierarchy.
• Does this make it easier to choose what inheritance strategy to use? No, it doesn't. Choosing inheritance strategy should be done on a case-by-case basis, and based on facts. Will you have a lot of data or just a handful of records? How complex is your inheritance strategy? Look at the queries generated by EF for your particular model, profile the queries, generate sample data corresponding or exceeding the amount of data you expect your application will generate and profile again.
• Does this make it a good idea to use entity inheritance in every corner of the model? No, it doesn't. Entity inheritance is a nice feature from a code and object-orientation perspective. However, in many scenarios it is not really needed. Don't make your model more complex than necessary. There are always tradeoffs on one side or the other: going berzerk with object-orientation can add database-side complexities such as performance issues, so try to find the right balance between object orientation and a clean database that keep both developers, DBAs, and end-users happy.

The Model Comparer for Entity Framework v4

The Model Comparer is part of the Huagati DBML/EDMX Tools add-in for VS2010. If you want to try it out, you can download it from http://huagati.com/dbmltools/ and get a trial license from the same site.

If you want to learn more about the basic features in the Model Comparer, and how it improves the EFv4 designer experience in Visual Studio 2010 with new options and features, please watch the screencasts in the following blog posts:
http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html
http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

The Model Comparer and other features in the Huagati DBML/EDMX Tools add-in keep evolving, so keep an eye out for the new features that we keep adding to it. Release cycles are generally very short and we try to get new features, improvements, and of course also bugfixes out to users quickly.

Using the Model Comparer to generate difference reports for Entity Framework v4 models

In the previous two blog entries, Introducing the Model Comparer for Entity Framework v4, and What’s new in the latest version of the Model Comparer for Entity Framework 4 I covered the basics of using the Model Comparer to keep EFv4 models in sync within the model and with the underlying database without replacing or losing customizations made to the model.

Now the turn has come to show the latest feature in the Model Comparer: Reports.

The main work area in the model comparer shows database <=> SSDL differences, SSDL <=> database, SSDL <=> CSDL, and CSDL => SSDL differences broken down in three treeviews and allows users to sync (or ignore) individual/selected (or all) differences.

In some situations it may be more useful to generate simple diff reports listing all differences in a readable format; printable/mailable reports come in handy when emailing to colleagues, for use during meetings, to review differences in more detail before applying changes, etc.

The newest feature added to the Model Comparer for EF4 is a "Reports" tab made just for creating reports from EF4 models:

The diff reports are generated from the same model diff comparison as the diff data shown in the treeviews in the differences tab, but the report format allows a lot more details pertaining to unmapped and changed objects can be shown; from database column attributes to SQL Server extended property descriptions, and in-model documentation.

Each type of difference get a section in the report detailing all differences of that specific type:

The Model Comparer compares all available attributes; from entity level down to data types, max size, precision/scale, nullability, default values, default constraints and just about everything else that is reflected in both the database, and the SSDL and CSDL layers of the EFv4 model:

 

Screencast / Video

I have created a short video/screencast demonstrating the basics of the Model Comparer's new report-generator feature:

 

Want to try it out?

If you want to try out the Model Comparer, you can download Huagati DBML/EDMX Tools and get a trial license from http://www.huagati.com/dbmltools/ . (The model comparer is just one of many features for Entity Framework and Linq-to-SQL contained within the add-in. The report viewer was added in version 1.93.)

Keep an eye on this blog for new features; we’re rapidly adding new EFv4-related features to the Huagati DBML/EDMX Tools Add-in. Note that the EFv4-related feature set only works under Visual Studio 2010.

What’s new in the latest version of the Model Comparer for Entity Framework 4

The following is a brief description of some of the new functionality that was just added to the Model Comparer for EF4.

An introduction video to the model comparer is available in the previous blog entry: “Introducing the Model Comparer for Entity Framework v4”.

New Version – new features

Yesterday I released a new version of Huagati DBML/EDMX Tools, and with it a new version of the Model Comparer with a pile of fixes, improvements, and some new features.

The goal of the Model Comparer is to give developers using Entity Framework 4.0 in Visual Studio 2010 better control over differences between the conceptual model (CSDL) and storage model (SSDL), and between the database and the storage model in EFv4 models. It does that by comparing the three layers and showing detected differences in three treeviews. The user can then select individual differences (or all) and either bring them across to the opposite layer or tell the Model Comparer to ignore differences.

The core functionality in the Model Comparer is described in more detail in the intro video in “Introducing the Model Comparer for Entity Framework v4”. If you haven’t used the model comparer or watched the intro video you may want to start with that one to get an idea of what the Model Comparer itself can do and how it works.

To give users even more control over what the Model Comparer takes into account when determining differences between the layers, it has a number of user-controllable settings. In the first version (add-in version 1.91) those settings were stored in XML files but not readily accessible from the UI.

As of version 1.92 there are UI dialogs exposing the most common settings. The settings dialogs are accessible through two new buttons in the lower left corner of the Model Comparer dialog:

The first button, “Settings” controls the behavior of the model comparer, and the second one, “CSDL Name Rules” define the naming rules used when creating CSDL entities and members from SSDL objects.

Model Comparer Settings

Most of these settings are simple on-off options so the settings dialog are cluttered with has a lot of checkboxes. (Yes, I know, checkbox-hell, but hopefully the settings themselves are self-explanatory enough to make it survivable on the rare occasions that these settings may need to be tweaked. It was really down to a choice between checkbox-hell and property-grid-hell and I think checkbox-hell is still easier to get an overview over.)

So, let’s break it down. The settings are grouped by the model area that they correspond to: Database, SSDL, CSDL.

Additionally there is one shared group of settings for SSDL/CSDL member differences, and one group that define what – if any - database default constraints should translate into store generated patterns in the model.

Database-to-SSDL difference settings

The first group of checkboxes controls if the model comparer should detect tables, views, columns, FK constraints, procedures and functions that exist in the database but has no representation in the SSDL:

The next group of checkboxes control what types of differences between database columns and SSDL member properties should be included in the database tree as DB->SSDL differences:

Finally, the last group of database diff settings are for stored procedures and functions; these settings control what type of differences should land stored procedures and database functions on the list of procedures with differences between the database and SSDL:

SSDL difference settings

The second portion of the settings dialog, SSDL settings, controls what types of objects present in the SSDL should be shown in the SSDL tree if not present in the database or CSDL.

The first five settings control the SSDL-to-database missing-objects comparison:

…and the following four settings controls the SSDL-to-CSDL missing-objects comparison:

CSDL difference settings

The next portion of the settings dialog, CSDL, controls if CSDL objects not present in the SSDL portion of the model should be displayed in the Model Comparer’s CSDL tree:

CSDL-to-SSDL difference settings

The CSDL to SSDL settings portion controls what types of differences at entity member level should land entities and their members on the diff list in the CSDL and SSDL trees. These settings are shared between both portions since differences in this area are shown in both trees:

Database constraint settings

The final portion of the Model Comparer’s settings dialog controls which – if any – database default constraints should be treated as StoreGeneratedPattern: Identity or StoreGeneratedPattern: Computed instead of the default StoreGeneratedPattern: None.

This comes in handy if primary key values are generated database-side by a default constraint like NewID or NewSequentialID, or if a table has ‘created date/time’ fields that are populated by the database rather than the client application.

There is also one field that control what the default constraint should be set to if generating SQL-DDL from the model comparer for tables with GUID PKs marked as StoreGeneratedPattern: Identity in the model. The default value is NewSequentialID but it can be changed to NewID or a user-defined function, as needed.

CSDL name rules

The second settings button available in the Model Comparer controls how CSDL entities and CSDL members generated from SSDL artifacts should be named. It allows you to e.g. strip underscores, apply ProperCase/pascal casing to class and member names, control pluralization and singularization, remove (or add) prefixes/suffixes and apply a whole set of other naming rules to ensure that the final classes and member properties conform to .net naming conventions (or your own naming conventions) even if the database objects don’t.

The naming rule settings are applied by the Model Comparer only when adding entities or members to the CSDL portion of the model. For renaming existing CSDL entities and members, the add-in has a separate feature that use the same set of settings but allows renaming existing entities and entity members.

What’s next?

The Model Comparer is a brand new feature in Huagati DBML/EDMX Tools, and it will get new features and improvements in various areas in the near (and far) future. Besides general usability improvements, the next version (1.93) will introduce new reporting features. More on this when 1.93 is released.

Want to try it?

If you want to try out the Model Comparer, all you need to do is head over to http://www.huagati.com/dbmltools/ and download the Huagati DBML/EDMX Tools add-in.

After downloading, click on the ‘trial license’ link to get a trial license, fill out your details, and you will receive a license key that will enable you to use the add-in for free for 20 days. If you’re happy with it and want to continue using it after that, please buy a license (or subscribe for only $15/month/user) to support further development of this and other features.

The add-in includes the Model Comparer for EF4 as well as a whole lot of other useful extensions to the Entity Framework and Linq-to-SQL designers in Visual Studio 2010 and 2008. (Note: Some add-in features such as the EF4 Model Comparer are only available under VS2010)