Designing an airline passenger reservation system

In two previous articles in the architecture section - Part 1 and Part 4, I mentioned that I intended to take a closer look at Microsoft’s Entity Framework once it has been RTM’d (released) in order to decide if ADO.NET EF or Linq-to-SQL is a better base for my DAL for HuagatiRes. Well, now that EF has been released as part of [the hastily RTM'd] Visual Studio 2008 SP1 and .net 3.5 SP1 I guess it is time to start playing around with it.

After several re-tries downloading the 831Mb VS2008SP1 image (all failed thanks to my ISP CSLoxinfo’s ‘invisible’ caching proxies), and getting past the installation quirks I finally got the service pack installed late last night. I quickly proceeded importing the HuagatiRes DB into an entity data model (EDMX file) and noticed some nice improvements in the EDMX designer over the Linq2SQL designer.

Improvements over the Linq to SQL designer

Some of the improvements that I first noticed in the designer:

• The designer clearly identifies what relationships/associations/FKs are one-to-zero-or-one, one-to-one, one-to-many etc.
• The navigation properties related to associations are clearly visible in the designer.
• There is a model browser that plugs right into the same panel as the solution explorer that allows both the store and classes to be explored and looked up in the model.
• It can even update the model from the database without having to resort to writing an add-in…
• Foreign key constraints behind associations are identified by name in the model browser and properties dialog.
• There is a documentation field in the properties dialog where entities, members, associations etc can be documented.

So yes, the EDMX designer at a first glance has some improvements compared to the Linq2SQL designer.

Designer shortcomings

Improvements aside -  there are some missing features that at least I think would have been nice to have in the EDMX designer. Maybe they’re coming in a future version (SP1a, SP1b, SP2, …?)

• Although the model explorer, and association properties, shows both FK constraint details as well as primary keys on tables, it does not show any indexes other than the PK. Why not both show the indexes [and index members] in the model explorer, and import index info and at least have attributes storing information about available indexes? Surely a more complete overview [including indexes] would be useful for all database developers. It would also be nice to see index info in the intellisense bubble when typing where clauses and joins in LINQ expressions, but of course that would require that the model (edmx and generated classes) include that information first. Maybe the EF team need to include a handful of enterprise developers in their advisory council…
• Progress dialogs for lengthy operations.

Let me elaborate a bit on this point:
I decided to take EF for a test-drive on a enterprise-system-size data model. The database I decided to use for this test-drive is not overly large, but large enough to fall in the enterprise-system category with its’ 1000+ tables and 2000+ associations.

I started generating the ADO.NET Entity Data Model (EDMX) from the database at 4PM. At first VS kept SQL Server very busy. The only feedback I got from VS was the hour glass mouse pointer, along with “(Not Responding)” in the title bar. There is no [visible] progress bar, no status messages or anything else to indicate how far it got.

Maybe I misinterpreted something, but when reading some article about EF somewhere on the intarweb I got the impression that EF was aimed at enterprise developers so I thought this would be an interesting test. But then again, maybe it is for enterprise developers with small databases / small data models.

 

…on to the last “missing-but-nice-to-have” items in the EDMX designer:

• Naming convention support…  Oh well, I’ll add edmx support to a future version of the naming convention thingie in the DBML Tools add-in.
• A way to import the table/column/fk human-readable descriptions/notes/documentation from data model diagrams (e.g. from Erwin .erx, ERStudio .dm1, or Visio for Enterprise Architects .vsd  files) into the description fields in the EDMX file. Well, there’s an idea for an add-in for Visual Studio. Maybe someone who doesn’t like re-typing the descriptions of every table, field and foreign key from the data model (like me) will write one…
• Multi page / tabbed diagrams. A single diagram designer surface for the entire db is not really useful for schemas larger than Northwind. It looks like someone thought about it because the edmx file would support it although the designer don’t:
  <edmx:Diagrams>
        <Diagram Name=”HuagatiRes”>
              …     

 

Conclusion

The edmx designer has some nice improvements compared to the Linq-to-SQL designer. However, it lacks some basic features that would make it useful when working with schemas larger than Northwind.

I hope that Microsoft will take a look at data modeling tools to get some feature ideas for the next version / next service pack. Although the edmx and dbml designers are not intended to be used for data modelling, bringing some of the basic features that all data modelling tools have into the designers would really make them more useful during development.   …or why not even take it a step further and add the features needed to use these designers to model both the object model and the database schema?  That would eliminate the need for separate data modelling tools and could allow both the database schema and object model to be created from inside Visual Studio.

 

Coming up….: The next article in this series (5b) will re-visit the performance comparison between EF and Linq to SQL that Roger Jennings performed on the beta. I am hoping that the beta version was slowed down by debug code and that the RTM version will be faster / have less overhead.

 

Update: An hour later (two hours after starting the EDMX generation) I noticed that VS is no longer beating up SQL Server. Visual Studio is still hanging, but it appears to be stuck doing some internal work and using a full CPU to do whatever it is that is keeping it busy.

Update 2 (6 hours later): Visual Studio is still hanging, and I have to admit that trying to import the schema of a large database into a single ADO.NET Entity Data Model (EDMX) was a stupid experiment. It has to be divided up into smaller chunks for a) EF to be able to handle it, b) for maintainability, and c) for source control due to the single-EDMX-single-cs-file-per-data-model-approach.

Unfortunately chunking it up into many DALs leads to duplicated entity definitions for common/shared entities but at this point it appears that EF is not yet up to the challenge of supporting larger schemas.

Hopefully a future version of EF will support using and merging multiple designer surfaces with support for shared entities. Maybe by then it will even be able to use the information already available in the data model diagrams (erx/dm1/vsd) to divide up the schema in functional areas / subject areas.

For now guess it would be a good idea for Microsoft to add a “top 200″ or “top 400″ [or whatever is the limit for the EF designer] to the SQL Server catalog queries that the EDMX designer thing uses to import tables. Just to avoid having people try this kind of stuff…

 

Update 3 (16 hours later): The next morning VS was still hanging with no indication of how far it got and how much longer it would take. I decided it was time to terminate the experiment…

A closer look at the files in my project folder revealed a 8Mb+ edmx file along with a 1.75kb .cs file. I opened the edmx file using a more reliable/stable development tool; MS Visual Notepad and noticed that all entities, members, associations etc had been defined in the edmx. However, the diagram section was empty so I am guessing that Visual Studio spent all night on trying to decide how to layout the 1000+ files in a single designer surface.

After restarting Visual Studio I tried adding the generated edmx file to an existing project. It got added but trying to open the designer resulted [again] in a hung Visual Studio (using 100% of one CPU core).

Update 4: I tried to generate the entity model using the command line tool for EF - edmgen.exe - hoping that it is better suited for large schemas than the edmx designer. I started it at 9:35am using the FullGeneration option. Unlike the designer/wizard in Visual Studio, EdmGen does output status messages.

 

C:\temp>edmgen /mode:fullgeneration /project:LargeDB /provider:System.Data.SqlClient
     /connectionstring:"server=(local);integrated security=true;database=largeenterprisedb"
Microsoft (R) EdmGen version 3.5.0.0
Copyright (C) 2008 Microsoft Corporation. All rights reserved.

Loading database information...
Writing ssdl file...
Creating conceptual layer from storage layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...

Update 5: EdmGen crashed after 20-25 minutes.

The console output simply stated “Process is terminated due to StackOverflowException.”

I rest my case. And I promise I will stick to small schema databases in my continued explorations of the ADO.NET Entity Framework.

 

Update 6: A Microsoft rep told me in the support forum that the EF designer is good for models with up to 120 tables. “Our response for the designer is that performance is typically reasonable up to about 120 tables, after which thinkgs begin slowing down.”

http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3757588&SiteID=1

I should have asked before trying to use EF with a larger model…