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.

Happy over Microsoft’s new commitment to data modelling, I have been playing around a bit with the “Oslo” CTP. It is time they come up with a replacement for Visio 2003 EA. The latest thing I tried was a conversion of the HuagatiRes datamodel into an M script. There’s not much to say about it - “M” and “Oslo” are still CTPs so some things can be defined in M models (e.g. table defs, foreign keys, PKs) while others can not be described in M (e.g. indexes, alias types, precision/scale on numeric/decimal types etc). I also haven’t figured out how to declare both nullability and length on members of type “Text”.   “:Text where value.Count <=100” works fine while “:Text? where value.Count <=100” will throw an error, but that might just be me not fully understanding the M syntax.

The M model for HuagatiRes can be downloaded from here:

http://blog.huagati.com/downloads/HuagatiResMmodel.zip

Huagati MGen / “Aker Brygge”

Now, I wasn’t stupid enough to actually type all of that into Intellipad. Instead, I did something even more stupid: I wrote a code snippet that extracts schema, type, table, column, foreign key, and index defs and generates M files.

If you’re interested in taking the schema->M tool (”Huagati MGen” a.k.a. “Aker Brygge”) for a test-spin, you can download it from here:

http://blog.huagati.com/downloads/HuagatiMGen.zip

It requires .net 3.5 SP1 and works against SQL Server 2005 and SQL Server 2008 databases only. Give it a connect string and an output folder and it will generate one M file per schema in the database you point it at, complete with code comments (based on object names and extended property descriptions) and all. Even [commented out] index definitions - prepared for the day when M gets support for index definitions…

In the guts of MGen

The first version of MGen was a single exe, but I have since divided it up into a DLL containing a simple object model (MCodeProvider) for generating M code, along with a class that will connect to a SQL Server to extract schema definition and generate M using the MCodeProvider.

The exe file adds the simple user interface shown above, but if you prefer you can use the MCodeProvider or the DBSchemaMGenerator from your code instead. Note that this is just an experimental piece of sample code - it does not support everything that can be done in M, and likewise the MCodeProvider and related classes also support [db-side] features that don’t exist in the CTP version of M. The generated M code will reflect unsupported things such as indexes, extended properties etc as code comments.

Usage example 1 - generate M from an existing database:

string connectString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
string targetFolder = "c:\temp";

//generate M from an existing db
Huagati.MGenCore.DBSchemaMGenerator mGenerator
    = new Huagati.MGenCore.DBSchemaMGenerator();
mGenerator.SchemaToM(connectString, targetFolder);
 

Usage example 2 - generate M code using the MCodeProvider:

//create a new module �
MCodeProvider code = new MCodeProvider();
MModule module
    = new MModule
    {
        Name = "TestModule",
        DescriptionComment = "Test Module"
    };
code.Modules.Add(module);

//define a simple type for identifying codes, string less than or equal to 10 characters in length �
MSimpleType idCodeType
    = new MSimpleType
    {
        Name = "CodeType",
        Inherits = new MTextType(),
        DescriptionComment = "Simple type used for storing identifying codes."
    };
idCodeType.Constraints.Add(
    new MMemberConstraint
    {
        MemberMember = ConstraintMemberEnum.Length,
        Operator = ConstraintMemberOperator.LessThanOrEqual,
        ConstraintValue
            = new MIntLiteral
            {
                Value = 10
            }
    });
module.Members.Add(idCodeType);

//define a simple type for names, string less than or equal to 255 characters in length �
MSimpleType nameType
    = new MSimpleType
    {
        Name = "NameType",
        Inherits = new MTextType(),
        DescriptionComment = "Simple type used for storing names."
    };
nameType.Constraints.Add(
    new MMemberConstraint
    {
        MemberMember = ConstraintMemberEnum.Length,
        Operator = ConstraintMemberOperator.LessThanOrEqual,
        ConstraintValue
            = new MIntLiteral
            {
                Value = 255
            }
    });
module.Members.Add(nameType);

//define a base type for all ref tables �
MComplexType refTableType
    = new MComplexType
    {
        Name = "RefTableType",
        DescriptionComment = "Reference table base type."
    };
MMember idCode
    = new MMember
    {
        Name = "Code",
        Inherits = idCodeType
    };
refTableType.Members.Add(idCode);
refTableType.Members.Add(
    new MMember
    {
        Name = "Name",
        Inherits = nameType
    });
refTableType.Members.Add(
    new MMember
    {
        Name = "SortSeq",
        Inherits = new MInt32Type()
    });
refTableType.Members.Add(
    new MMember
    {
        Name = "Active",
        Inherits = new MLogicalType()
    });
refTableType.IdentityMembers.Add(idCode);
module.Members.Add(refTableType);

//define a test table, inheriting from the ref table type �
MExtent testTable
    = new MExtent
    {
        Name = "Test",
        DescriptionComment = "Test reference table.",
        Inherits = refTableType
    };
module.Members.Add(testTable);

//add an index to the test table �
MIndex testTableIndex1 =
    new MIndex
    {
        Name = "ix_TestTable_SomeOtherField"
    };
testTableIndex1.Members.Add(refTableType.GetMember("Name"));
testTable.Indexes.Add(testTableIndex1);

//save all modules   
code.SaveCode("c:\\temp");  

The output from the code above will be an M file containing the following:

//Test Module
module TestModule
{
    //Simple type used for storing identifying codes.
    type CodeType : Text where value.Count<=10;

    //Simple type used for storing names.
    type NameType : Text where value.Count<=255;

    //Reference table base type.
    type RefTableType
    {
        Code : CodeType;
        Name : NameType;
        SortSeq : Integer32;
        Active : Logical;
    } where identity (Code);

    //Test reference table.
    //[ExtendedProperty("MS_Description", "Test reference table.")]
    Test : RefTableType*;
    // index ix_TestTable_SomeOtherField(Name);
}

EF Designer - Documentation Features

A couple of weeks ago I mentioned that the ADO.NET Entity Framework designer has a new documentation field that allows entity types and members (classes and properties) to be documented right in the designer.

Documentation fields in the EF designer's property dialog

I think this is a useful feature - once descriptions have been typed in for all classes and properties, the code editor will show the descriptions in the tooltip for the relevant classes and properties.

It has a cosmetic flaw: In the haste to release VS2008 and .net 3.5 SP1 it seems like MSFT forgot to apply the entity type documentation to the entity set accessor properties, so entity sets (and all undocumented classes and properties) will show the default “There are no comments for [name] in the schema.“. I would like to see the EntitySet using the same documentation as the EntityType, or at least have a “Documentation” property of its own. (It does in the EDMX, but not in the designer)

The “There are no comments” default value is a bit redundant and waste of screen real-estate. Why not leave the default value blank? Oh well, this is just version one so I guess that will be improved in some future service pack update.

Also missing is the ability to automatically populate the documentation field with descriptions from data model diagrams and/or the database. The descriptions may already exist in a data model diagram or in the database. In addition, knowing what indexes exist is very useful when writing queries, and what indexes exist on tables etc are of course available from the database so being able to automatically retrieve those two pieces of information from the db would be a nice-to-have feature.

Linq-to-SQL documentation features

Being a fan of the more stable and faster OR mapper Linq-to-SQL, I would like to be able to have the same kind of inline/tooltip documentation for my L2S classes and members. However, the L2S designer does not yet have the “Documentation” field that the EF designer do. I wonder why…

Maybe MSFT will add the documentation fields to some future version of the L2S designer, and maybe they will also add coupling to build documentation from the database. I’m not going to hold my breath while waiting for this so instead I decided to add this as a new feature in the Huagati DBML Tools add-in for Visual Studio.

In brief, it works like this:

The add-in has a new menu option called “Update Linq-to-SQL documentation from database”:

New menu option for updating L2S documentation from the database

When the new documentation feature is used, the add-in will retrieve table and column descriptions from the database, along with index definitions for all tables.

The add-in updates the L2S generated code with summary xml comments and description attributes for all entities/classes, members/properties and data context entity accessor properties. I would have preferred to keep the documentation in a separate file, but unfortunately I don’t see a way to document member properties from separate partial classes so for now it is done inside the generated file. Not a big problem since the documentation can be re-generated at any time by just selecting the “update” menu option.

The end-result is that I can now see both descriptions and index information in-line while writing code against the L2S generated classes.

Voilà! Finally some nice descriptions and db index information right in the code editor.

Availability

The Linq-to-SQL documentation feature is available in version 1.20 of the Huagati DBML Tools add-in that can be downloaded from http://www.huagati.com/dbmltools/ . Users of previous versions can upgrade for free by downloading the latest version and installing it over the existing version. New users can download the add-in and get a trial license or a full license from the same page.

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.

Every now and then I am commissioned to do database performance investigations for clients. This usually happens when they have a production system that has finally reached the point where users get frustrated, or - even worse - the system stops performing vital tasks because it is overloaded.

Configuration

There are a number of things that can cause database performance problems but there are a few causes I encounter much more frequently than others. Hardware configuration, OS configuration and database configuration are the first things I take a look at together with perfmon data to get an overview over where there may be bottlenecks. On Windows/SQL Server systems running large databases the most commonly overlooked or omitted configuration issues I encounter are:

• Incorrect memory configuration - systems with >3Gb RAM without using the appropriate combination of /3Gb, /PAE, and /UserVA boot flags. SQL Server can make really good use of every little chunk of memory it is allowed to access so on some systems just setting these options so they match the environment have provided a “night and day difference”.
• Less than optimal utilization of available disk bandwidth. Disk bandwidth is the most scarce resource on any database server so utilizing the available disk bandwidth by using the correct raid configuration, placing data files, transaction logs, tempdb on the right spindles etc can make a huge difference.
• Non-vital services running on the database server; antivirus, secondary [test] instances of sql server, unused web servers, indexing services and other ‘resource thieves’.

All these are issues that should have been taken care of by a DBA or sys admin [if there is a dba or sys admin]. Either there hasn’t been a DBA involved in setting up systems plagued by these issues, or the DBA/sysadmin does not have the knowledge or resources needed to perform his/her duties, or the configuration has changed since the DBA was originally involved. Whatever the cause, these simple configuration issues are things that I have stumbled over on many production systems and a couple of changes and reboots later the systems are a lot snappier. I’m not going to delve into the details of these to not go too far off-topic, and besides there are so many articles and blogs entries covering these aspects already so there is no need to repeat it.   [However, if you need assistance with reviewing configuration on an existing system, feel free to contact me for a quotation ]

Bad queries

Once I have looked at the basic configuration settings [along with perfmon and profiler data to determine the optimum configuration for a specific system] I normally take a closer look at the user activity on the system, i.e. queries, connections etc from the various client- and reporting applications typically involved. This is the area where I frequently find the worst offenders. Even a single report query, innocently added to address some business need, have been proved over and over to bring otherwise optimally performing databases on its’ knees (or even below the floorboards).

In a perfect world, which applications should have access to production databases and all queries allowed into a production system are strictly controlled and everything new is thoroughly reviewed before being allowed into a production environment. In the real world, business requirements, time- and resource constraints tend to make individuals and organizations break these rules. So in the real world, where do the queries that run against production databases originate from? A few common sources are:

• Code written by programmers that understand how the database works, and how a query will be executed usually take care to write good, optimal queries that have been thoroughly tested and reviewed to ensure that criteria and groupings use the right indexes, that joins will be picked up in the right order by the optimizer, that the necessary indexes exist in the first place, that the overall execution plan is optimal etc. These guys rarely cause a problem, and when they do it is usually because of time constraints that cause them to write sloppy code.
• Code written by programmers that don’t understand how the database works, and don’t have the interest, time, or ability to gain that knowledge/understanding. Some of the people that fall in this category know their own limitations and get their work reviewed, others don’t and can - with a few lines of code - bring disaster into a system that is otherwise in harmony with its’ enviroments. Some organizations have mechanisms in place to catch the work of this group - code reviews, test cycles with dba reviews etc while others don’t.
• Business intelligence / custom reporting. I’m not disputing the importance of these tools to address business needs, but the easier these tools become to use for laymen, the more dangerous they [potentially] become to the systems they interact with. Again, in the ‘perfect world’ scenario, all BI and custom reporting are kept on separate systems - whether log-shipped or replicated copies of the production database or separate data warehouse databases with pre-aggregated data -  well away from the transactional production systems. In the ‘real world’ they sometimes interact directly with the production system database(s) where a seemingly innocent query dragged-and-dropped together in a query builder tool can bring a system on its knees.
• Add-on web interfaces; web pages or web services - not part of the core system - that provide customers, suppliers or other external parties access to certain kinds of information from otherwise internal systems. These tend to be written by in-house IT department developers to add functionality to systems provided by ISVs, and if the people writing them either fall in category #2 or don’t have enough knowledge about the system they are interfacing with these seemingly innocent on-the-sidelines mini apps can in the real world be the root cause of serious performance problems.

Tools

If you have made it this far, you are probably asking yourself: why this rant in an article in the tools category?

The simple answer: addressing the problem of poorly written queries early on in a development cycle makes it easier to avoid them altogether. And the best way to avoid them is by using the right kind of tools.

Linq to SQL out of the box gives us type safety and intellisense when writing queries, which vastly improves productivity when writing code that interacts with the database. It makes the queries known to the compiler already at compile time in the vast majority of cases. [dynamic queries excluded].

In the past, bad queries had to be caught after they were written; in code reviews, dba reviews, using profiling tools such as SQL Server Profiler on test- and production systems etc. With Linq to SQL this is even more true when using the out-of-the box tools. In fact, the main argument used by many Linq to SQL detractors is that it takes control over query composition and “hides” the queries away from programmers and DBAs. This is true when using the out-of-the-box [first] version of Linq2SQL (and EF), but that doesn’t mean it have to be that way, and it does not necessarily mean that taking SQL composition away from the programmers or DBAs have to be a negative thing.

So, to address this problem I am working on a couple of additional tools that will plug right into Visual Studio together with the Huagati DBML Tools add-ins and add some new functionality.

Tool #3: DBML Explorer

I will start the numbering with #3 as there are already two tools included in the Huagati DBML Tools toolkit. The DBML Explorer will be the third tool to be added to the toolkit.

Today, Visual Studio gives programmers access to the database objects through the Server Explorer sidebar, and access to the ORM side of it through the class designer(s) and properties dialog. The properties dialog also shows some basic database attributes such as table name, column data types etc. However, neither of the two gives a unified view over both, and on top of that neither are particularly easy to navigate for large databases.

The DBML Explorer is intended to overcome this and bridge the gap. It combines the information from the ORM side (DBML file / Linq2SQL designer) with schema information; table details, column details, index information, foreign keys etc. With this information at hand directly in Visual Studio it will be easier to write Linq queries against the database while being aware of what indexes are available for the query, comparative size of tables etc.

Preview Screenshot of the DBML Explorer

Tool #4: DBML Query Builder

Although I don’t like using query builder tools myself - I actually enjoy  writing SQL (and nowadays LINQ) queries - I can see the productivity boost that a query builder tool can bring to those who are not familiar or comfortable with query language syntax. This tool extends the DBML Explorer with functionality to quickly select the relevant tables and columns, join types, groupings/aggregates, criteria, formulas and then let the tool compose the Linq query with on-screen advice on index usage etc. This tool will help beginners get up to speed with LINQ syntax as well as save typing for those who are not amused by writing queries.

There are already some Linq query builder tools out there, but what I intend to address with this one is how the final query will interact with the database. If it will end up doing a table scan on a large table [or several large tables] this should be visualized already while the query is being composed in the query builder.

Tool #5: LINQ Query Analyzer

Since Linq has already given us productivity enhancing features such as type safety and intellisense when writing queries, why not take it a step further? Since the query is no longer stuffed away in a string literal but is now part of the code, some basic things; index utilization, join order and other parts of the execution plan can be checked early on, inside Visual Studio, any time between the query is written and compiled. This means that bad queries can be caught already at design time in Visual Studio instead of later in SQL Server Profiler while troubleshooting performance issues in a production environment.

Preview Screenshot of the Linq2SQL Query Analyzer output in Visual Studio

Availability of the tools

These additional tools will be included in the Huagati DBML Tools package as they become ready to use. The DBML Explorer is first up and it is scheduled to be released later this week. The other two tools don’t have a release date yet but I will update this post when the date is known.

Existing license holders

Existing license holders of the tools will be able to upgrade to a new version including the additional tools free of charge.

Note: This posting is provided “AS IS” with no warranties, and confers no rights.

This is just a short release notification covering the latest updates to the DBML refresh add-in.

The add-in now detects the following database changes and will apply those to the dbml:

• Added columns
• Dropped columns
• Dropped tables
• Primary key changes
• Auto generated column changes
• Data type changes
• Added foreign keys
• Dropped foreign keys [new feature added on 22 July 2008]
• New tables [new feature added on 23 July 2008]

Still missing (but coming in a future version) are:

• New tables [feature was added on 23 July 2008]
• New, modified or dropped stored procedures
• Dropped foreign keys [feature was added on 22 July 2008]

The add-in is described in more detail in the “Tools Part 1″ article and can be downloaded from http://www.huagati.com/dbmltools/

The current version is 1.0.3120.33307, dated 17 June 2008 18:30.

Updated on 22 July 2008: Added support for dropped foreign keys and fixed a couple of issues related to recursive FKs (referencing the same table). The current version is 1.0.3125.30798 dated 22 July 2008 17:06.

Update: 23 July 2008 - added support for new tables, fixed a pile of reported and unreported bugs and re-tested against the reservation db and Northwind. The current version is 1.0.3126.38442 timestamped 23 July 2008 21:21.

Update: 26 July 2008 - numerous fixes and adjustments. Added licensing model; the free edition now supports up to 20 tables per DBML file. For larger DBML files, a license can be acquired from http://www.huagati.com/dbmltools/

Update: 30 July 2008 - added options dialog allowing the user to select what parts to synchronize (new/dropped tables, new/dropped/modified columns, PKs, new/dropped FKs). These options can be persisted along with a table exclusion list for unwanted tables. Also added a user guide detailing how to install and use the add-ins. Updates can be downloaded from http://www.huagati.com/dbmltools/

As with many new technologies, there are a couple of missing features in Linq2SQL and the DBML designer. One feature that would really help during development is the ability to re-sync the DBML with the database so any data model changes can be incorporated in the DBML and the auto-generated entity classes.

At the moment, Microsoft’s tools provides three options for refreshing/resynchronizing the DBML with the database:

1. Remove all, or only the changed entities and re-add them to the DBML designer
2. Same as above, but use SQLMetal to re-generate the entire dbml
3. Manually keep the dbml / Linq2SQL designer in sync with the database

The first two methods work fine if the naming conventions in the database match .net naming conventions, or if one prefer database naming conventions in the code. However, I always use all lowercase underscore separated table and field names in the database but pascal/proper case/.net entity names and member names in .net code. This more or less rules out options one and two when using out-of-the-box VS 2008. Re-generating the Linq2SQL diagram and then renaming entities and members by hand is a lengthy and error prone exercise. On the other hand, manually applying all changes both to the data model diagram and to the dbml is also duplicated work and also risk introducing errors/mismatches.

To remedy this, I will create a couple of Visual Studio add-ins that take over where Microsoft left off.

Add-in #1: Naming convention add-in

First off is an add-in for updating all entity and member names so they match .net naming conventions, e.g. the table airport will get a class name Airport, route_via will become RouteVia, the field airport.airport_code will simply be Airport.Code and so on. The following image shows the airport table before and after being updated by this add-in:

DBML is a fairly straightforward xml document where all tables, fields, entity classes and members are described with their database- and code attributes. Together with the dbml a layout file is also generated; this file is also an xml file and it stores the layout of the Linq2SQL designer diagram; position of tables, association lines etc. All this add-in need to do is go through those two files, update all entity and member names, update associations and then save the files to trigger the code generator to re-generate the entity classes.

The end result is that updating a Linq2SQL designer generated dbml, or sqlmetal generated dbml to use .net naming conventions after creating or refreshing the dbml from the database is as simple as hitting a button.

Add-in #2: Linq2SQL designer refresh add-in

Once the Linq2SQL base DBML has been generated it is important to keep the DBML in sync with any schema changes. If a couple of fields have been added to existing database tables, fields removed, tables removed, primary key changes, or foreign keys added or removed we may still not want to re-generate the entire DBML although this is the only option we’re left with when using the tool supplied by Microsoft.

To overcome this, I created a second add-in that will compare the dbml with the database schema of the database pointed to by the connection string embedded in the dbml and update the dbml accordingly. If any new columns have been added to tables represented in the dbml, columns removed, columns changed nullability or data type, PK members changed, foreign keys has been added/removed, or entire tables has been added or removed the add-in will detect the schema changes and apply them to the dbml.

Support for stored procedures will be added in a future version.

Downloading and installing the add-ins

The add-ins can be downloaded along with a user guide from http://www.huagati.com/dbmltools/

There are three editions; the free edition that support up to 20 tables, the Standard edition that supports up to 80 tables and the Professional edition that supports as many tables as Linq2SQL/DBML.

Using the add-ins

Whenever you have the DBML designer open in Visual Studio, the Tools menu will get two new options from the add-in:

The first one, Standardize DBML Entity and Member names, will update all entity names as described above, and the second one, Update DBML diagram from database, will connect to the database and update the DBML file so it corresponds with the database schema.

Whenever either add-in is used, all changes done by the add-in will be recorded in the output window:

Update: 16 July 2008 - fixed bug related to auto-generated columns and added PK support. The latest version is 1.0.3119.33326 with a timestamp 2008-07-16 18:30

Update: 17 July 2008 - added support for added foreign keys. The latest version is 1.0.3120.33307 with a timestamp 2008-07-17 18:30

Update: 22 July 2008 - added support for dropped foreign keys and fixed a couple of issues with recursive tables (FKs referencing the same table). The current version is 1.0.3125.30798 with a timestamp 2008-07-22 17:06.

Update: 23 July 2008 - added support for new tables, fixed a pile of reported and unreported bugs and re-tested against the reservation db and Northwind. The current version is 1.0.3126.38442 timestamped 23 July 2008 21:21.

Update: 26 July 2008 - numerous fixes and adjustments. Added licensing model; the free edition now supports up to 20 tables per DBML file. For larger DBML files, a license can be acquired from http://www.huagati.com/dbmltools/

Update: 30 July 2008 - added options dialog allowing the user to select what parts to synchronize (new/dropped tables, new/dropped/modified columns, PKs, new/dropped FKs). These options can be persisted along with a table exclusion list for unwanted tables. Also added a user guide detailing how to install and use the add-ins. Updates can be downloaded from http://www.huagati.com/dbmltools/

Update: This article is somewhat outdated. See http://www.huagati.com/dbmltools/ for an up-to-date description of the add-in, its’ features, download link etc.