Designing an airline passenger reservation system

In Tools Part 6 I covered SQL-DDL generation from Linq-to-SQL models using Huagati DBML Tools. The L2S version is incremental, meaning that it compares the L2S model to the underlying database and generates the DDL code necessary to change the database to match the model. This is effectively a reverse implementation of the update/sync feature in Huagati DBML Tools.

The time has now come for the first step towards “model first” using Entity Framework. A vital step towards this is the EDMX cleanup feature described in Tools Part 7; without the cleanup feature a lot of model editing in a “model first” scenario would require using an XML editor to edit the SSDL. Although not all pieces are in place in the “EDMX cleanup” feature (yet) I have added a first implementation of the SQL-DDL generator for Entity Framework models to the add-in.

This first version supports generating new databases from scratch, essentially doing what Microsoft have described in their “Model First” article in the EFDesign blog. (And what was demonstrated during their “Entity Framework futures” session at PDC2008). The next version will support incremental SQL-DDL generation identical to the L2S version described in Tools Part 6; generating scripts including only the differences between the model and the underlying database.

This functionality is available in Huagati DBML/EDMX Tools version 1.51, and can be downloaded from here. If you prefer to use the Microsoft version of the same, look out for the next release that (if I interpret all statements from MSFT correctly) will be included in Visual Studio 2010.

Step-by-step usage example

1) Download and install Huagati DBML/EDMX Tools.

 

2) Fire up Visual Studio 2008 and open a Entity Framework model in the EF designer.

 

3) Select “Generate DDL” under the “DBML/EDMX Tools” menu.

 

4) Wait a few seconds and you have a DDL script that will generate schemas, tables, foreign key constraints, primary keys, and indexes matching the foreign keys.

 

 

 

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

A question frequently asked on community sites such as stackoverflow.com and in the MSDN forums is “how do I generate DDL scripts with model changes“. Translated: some people want to use a model first approach with the Linq-to-SQL designer or the Entity Framework designer as the modelling tool.

This is a nice idea that I absolutely subscribe too. However, a modelling tool should be able to accurately describe the entire (as in all aspects of) the model so using either of those designers to model a database would result in schemas with no indexes besides primary keys etc. Support for incremental changes is also a pretty basic requirement in my opinion.

“Model First” in Entity Framework

A while back, the EF team published an article in their EFDesign blog where they described the approach they are taking in the next version of the EF designer, slated for release sometime around 2010 if I have interpreted everything correctly. They will add a new feature that generates the SSDL description of the storage layer automatically from the conceptual layer and then generate SQL DDL from that. Effectively they are throwing away the advantages that a layered model brings, and turning it into a 1:1 model no different from L2S models. Or in the words of Microsoft PM Noam Ben Ami: “We would like users to understand that this feature will regenerate all SSDL and all MSL from scratch.”

To add insult to injury, they won’t even support generating diff-scripts, e.g. if you add “EmailAddress” to your employee table they will re-generate the entire database rather than just issue a simple “alter table employee add email_address nvarchar(100);” statement. Again in the words of Microsoft’s “Noam Ben Ami” in the efdesign blog article: “your database will be recreated from scratch“.

This “regenerate the database” feature was demonstrated by another Microsoft PM, Tim Mallalieu, at PDC during his “Entity Framework Futures” presentation. (41:00 until 47:00). He also explicitly pointed out that the database will be dropped and re-created instead of amended with the changes (45:55 - 46:15 into the presentation) and added a gleeful “I told you so, when your data is gone“. Watch it here: http://channel9.msdn.com/pdc2008/TL20/

Not to mention indexes, alias types, views, etc etc. Belongs in the model in my opinion, does not according to Microsoft’s EF team / DP group.

“Model First” in Linq to SQL

Linq-to-SQL is not really designed for a model-first approach either. Unlike EFv1 however it has the ability to generate a database from scratch. Still no support for indexes etc in the model though, but it is nice to get a baseline database (tables, PKs, FKs etc) generated.

A lot of development time involves the usual changes to models and databases. Add a field here, drop a field there. Add a couple of new tables, a couple of new foreign keys etc. Who want to throw away the database and start with a new blank database every time some small change is made to the model? Not me. So I normally model my databases using modelling tools (currently Visio for Enterprise Architects 2003) that support forward/reverse engineering and change scripts, and then update the L2S model to correspond with the db schema. Supporting that scenario is the core reason why I wrote the “sync model” feature in Huagati DBML/EDMX Tools in the first place; I was bored with having to apply the same changes to more than one model.

Now I want to simplify that by generating basic change scripts; added columns, added tables, dropped tables, dropped/added foreign keys, etc as SQL DDL scripts. So I added that feature to the add-in. It is available in version 1.47 that was just released and is available for download now.

Demo

Allow me a quick demonstration using the Microsoft Northwind database:

First I open up my existing Northwind DBML test project that contain the baseline Northwind model. I add a new member/column EmployeeEmail to the Employee entity/table, two new entities/tables “EmployeeFamily” and “EmployeeRelativeKind” and associations between Employee/EmployeeFamily and EmployeeFamily/EmployeeRelativeKind.

Next, I pop over to the DBML/EDMX Tools menu in Visual Studio and select the “Generate DDL with Model <-> db differences” option.

And when the add-in has completed comparing the model to the underlying database it pops up a new SQL-DDL change script outlining the differences between the model and the database.

 

So, there you have it. This is available today for Linq-to-SQL. It generates change scripts with just the differences instead of “blowing away the database” (to paraphrase Tim Mallalieu). Huagati DBML Tools now support both forward and reverse updating between model and database.

I may even add the same feature for Entity Framework if there is enough demand for it so people can reverse/forward update their models without blowing away the existing SSDL/MSL and without dropping the entire database.

 

Ps. Dear Microsoft: if you’re interested in providing these features for L2S and/or EF to your customers so they don’t have to buy the add-in from me - you got my phone number.

It has been a while since I posted anything on the blog. Did I stop blogging? Nope, just been having fun with other stuff. Like optimizing indexes for an oracle database, adding new features to my L2S/EF toolkit etc.

Gone Fishing

Right now I am “busy” fishing. It is Sunday and we’re at our vacation house on the countryside 130km to the west of Bangkok, where we have a small lake full of fish. I have been trying to catch a fish since yesterday but so far it is 2-0 to the fish. Over night, two tackles and hooks just disappeared. One large triple pointed hook attached to a steel wire and baited with a chicken skin is gone, the steel wire had snapped or been cut off. So I either got a very large fish with razor sharp teeth hanging out in the lake, a fish with a wire cutter, or maybe our resident monitor lizard got it.

The second missing tackle is also a triple pointed hook attached to half litre water bottle. I use water bottles as floats as they allow the fish to swim around while making it easy for me to find them. Until now. I have checked everywhere, and it is just gone. Not in the lake, and not anywhere in the irrigation canals out in the back of the garden. If the monitor lizard got it I would have expected to find it outside of her nest but no trace there either. (I know it’s a ’she’ because she laid eggs a few months ago. Unfortunately someone poached the nest a couple of weeks later so we won’t get any cute baby monitors. Monitor lizard eggs are turned into “performance enhancing” omelet by the locals.)

Some time whatever animal got it will have to surface, no fish can stay under water forever with an extra half kilo bouyancy. One remote possibility is that I got a fish on it and the fish got eaten by something larger. Like a python. They’re not resident (they’re more like consultants ) so if a python got it, it has probably already left our garden. If anyone comes across a large python dragging around a Minere bottle attached to a string, please return it to me.

 

EF performance

Enough off-topic stuff. I promised a while ago that I would write something about Microsoft’s new ADO.NET Entity Framework and performance. I was planning to do that, but I feel it is no longer necessary. There has been a lot written about it already and it isn’t pretty. Somehow they forgot to test EF and L2E beyond “Hello World”. Complex LINQ-to-entities queries are translated into really poor SQL. Less complex queries like a simple “from person p in oc.Persons where p.LastName.StartsWith(lastName) select p” are translated into equally bad SQL that will always force a table scan. Basic Math library operations such as “Round” in L2E queries are not supported at all by EF. The list goes on and on, but in short it EF v1 was simply released way too early, the current version should have been labelled “CTP” instead of “RTM”.

In contrast, L2S generates remarkably good and efficient SQL queries from just about any Linq expression thrown at it.

The future of Linq-to-SQL and Entity Framework/Linq-to-Entities

Microsoft made an announcement regarding Linq-to-SQL and Linq-to-Entities last week. Actually they made a lot of announcements regarding Linq-to-Entities last week during PDC. During one session the EF program manager, Tim Mallileau showed what they’re working on for future versions of Entity Framework. A lot of the “new” stuff in EF vNext are features that already exist and work in Linq-to-SQL such as deferred loading, POCO support with object-relational mappings described as attributes instead of in a bloated embedded EDMX file. That’s all nice, and shows that they have learnt at least a few things from the EFv1 fiasco, so now they’re trying to merge what is already there in L2S into EF. Wouldn’t it be smarter to cut the losses on EF and instead continue forward with Linq-to-SQL? At a minimum, opening up the provider model in L2S would be a nice first step…

So instead, they came out with an announcement that a large part of the L2S user base interpreted as a death certificate for Linq-to-SQL. Read it here: http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx

A lot of heated discussions and upset blog entries ensued, people asked “Has Microsoft really killed Linq-to-SQL?” on stackoverflow etc. As always, Roger Jennings does a great job at summarizing what is being written around the blogosphere so head over to http://oakleafblog.blogspot.com/ for a summary…

Is Linq-to-SQL dead?

So is Linq-to-SQL dead? No, Linq-to-SQL is not dead. Heck no. (to paraphrase some ’softies). Microsoft can’t kill it. Linq-to-SQL is built into the .net framework as System.Data.Linq etc and it will stay there. To my knowledge, so far they haven’t ever removed/killed any features from the .net framework, but some namespaces don’t get a lot of new stuff added to them. That doesn’t mean they’re dead.

What I think the statement from Microsoft can be interpreted as is: “We’re not going to commit resources to improving Linq-to-SQL because EF is our big strategic object-relational mapping tool.“. And that is not really a big problem. Linq-to-SQL works really good out of the box, from a developer’s perspective it is easy to get started with, easy to use and really speeds up development of database-driven apps. Sure there are areas that can be improved, but the majority of those things can be solved without Microsoft having to make changes in the framework. More importantly, Linq-to-SQL is ready to use in app development today while EF is still really just a CTP release although Microsoft keeps touting it as RTM.

New features for the designer is one good example where L2S can be improved without the help of Microsoft, and I think I have already demonstrated that it can be done as VS addins. Code generation extensibility has been covered by Damien Guard in his T4 templates for L2S. There are plenty of runtime enhancements and code samples out there as well. Linq-to-SQL fans just need to follow Roger Jenning’s blog and they’ll find everything there is to find about Linq-to-SQL. So as long as Microsoft takes care of the occassional bug that might surface in L2S that’s fine and I’m sure they will. Although the documentation don’t mention it, they seem to have included a bunch of improvements in .net framework 3.5 - for example queries with multiple aggregates now come out clean and much more optimized than they did in the RTM version.

Oslo - the city with the most expensive beer in the world

Another interesting thing that was announced and demonstrated at MS PDC last week is something called “Oslo”. Besides being a city with the most expensive beer in the world, “Oslo” is also the code name for a new set of modelling tools from Microsoft, slated for release in 2010. I first heard about it a couple of months ago when I got an email from Don Box asking me if I ever considered working for Microsoft.

I have considered that a couple of times, the first time in 1997 or 1998 when I was interviewed for a job at Microsoft in Stockholm. That time I went for five separate in-person interviews (I lived 500km from Stockholm at the time), one which included filling out a “personality test” that bore a resemblance to the OCA test. They (MSFT) still couldn’t make up their mind after the fifth interview so I cut it short.

But since I got an email from a famous guy I did consider working for MSFT once again and so I attended a couple of phone interviews. This time there was no personality test involved so either they got rid of that test or maybe it was just something used by Microsoft in Stockholm. This time I got to the third interview, the tech interview. I botched it on a couple of questions on swapping items in linked lists, and on QSort. Don’t know what I can blame that on but I guess it being 7am and me not being a morning person is one semi-valid excuse. Anyway, they were cool about it and said it had to do with budget constraints and not me messing up the linked list… :) Budget constraints at Microsoft? That’s like snow in Bangkok…

So no, I’m not going to become a ’softie. Anyway, the whole experience got me interested in knowing more about “Oslo” (mainly because Don Box is leading the Oslo team, making it highly likely that something cool has to come out of it), so I downloaded the Oslo SDK CTP when it was released last week.

Oslo includes a graphical modelling tool, Quadrant (which is not included in the CTP download), a text-based modelling language called M, some lower level languages for defining domain specific languages, a repository design pattern that appear to be aimed at ensuring the generated databases will play nice in their new “Cloud” services platform, called “Windows Azure” (with a worse nickname). The “Oslo” tools are still in their infancy so there’s a lot of stuff missing, but it is still interesting to play around with it.

If you are interested/active in model driven development, drift over to the msdn Oslo Developer Center,  download the SDK, take it for a test spin, browse the Oslo msdn forum and let them know what features you would like to see in Oslo.

Personally, I am hoping that this time around MSFT will release a modelling tool that [on the database modelling side]:

• is at least as easy to use as Visio, and as flexible as ERStudio and ERWin together
• supports all basic/common data modelling scenarios
• will successfully be able to reverse-and-forward engineer at a minimum Northwind and AdventureWorks without losing any database objects
• will handle versioning of data models and schemas; schemas evolve over time in any system so the ability to update an existing database with model changes is a fairly basic requirement in my opinion
• will be flexible enough to support the different naming conventions commonly in use

Now I’m off to check if I got a fish or if the missing bottle/tackle has surfaced anywhere. If not, I’m going to switch to net-fishing next week.

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.

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…