Here's one of the sample companies' customer list. Notice the similarity of the left pane to Outlook's Navigation Bar and Access 2007's Navigation Pane.

MOAX uses SQL Server 2005 Express edition (SQLX) as its back end, so the data store is robust and reliable. The 584 table names for a single company are plain English. The same is true for the 1,793 T-SQL stored procedures, 538 views, 260 T-SQL scalar functions, 56 T-SQL inline table-valued functions, 47 T-SQL table-valued functions, 825 T-SQL DML triggers, 643 foreign-key constraints, and 1,065 check constraints. Thus, it's no surprise that a a new company database I created (oakleafsystems.sbd) weighed in at 68.6 MB for starters. Notes: Exploring the sys.objects catalog view disclosed no .NET CLR aggregate functions, stored procedures, or scalar, table-valued or inline table-valued functions. If you've installed SQLX locally, you can opt to use your current instance instead of the \MSSMLBIZ instance included in the download. MOAX substitutes .sbd for .mdf and .sbl for .ldf extensions for data and log files, respectively.Microsoft's Office 2007 system requirements are a 500MHz+ CPU and 256 MB RAM (1 GHz+ CPU and 1 GB RAM for Outlook with BCM). Most users will find MOAX sluggish with CPUs slower than 2.5 GHz, and less than 1 GB RA.Microsoft encourages users to upgrade to the forthcoming $149 Microsoft Office Accounting Professional (MOAP) 2007, which replaces the underpowered Small Business Accounting 2006 product. PC Magazine offers a full review (10/2/2006) of a late MOAP beta, which concludes, in part: "Microsoft's Office Accounting 2007 beta is a vast improvement over last year's edition. It should prove an effective tool for small businesses that don't have large-scale inventory requirements." MOAP competes directly with QuickBooks Premier Edition 2007 ($325 - $350) and Peachtree Premium Accounting 2007 ($450 - $470).
Here's a MOAX customer editing form:
MOAP provides full multi-user access with Small Business Server, supports inventory maintenance, multiple currencies, and provides additional customizable reports. MOAX users must upgrade to MOAP to connect to a remote SQLX or SQL Server 2005 instance.

The accompanying Readme.htm and Overview.doc files make it clear that this EDM Designer release is indeed a prototype. The Designer has the capability to generate the initial storage schema (SSDL file), mapping layer (MSL file), conceptual layer schema (CSDL file), and C# or VB class code from an SQL Server database. The designer only manipulates the CSDL file. If you alter the data model at the CSDL level or change property values of Entity Types, Entity Sets, Associations, or Association Sets, you must manually edit the MSL file and, in some cases, the SSDL file.

Most EDM code examples use one or two entities from the AdventureWorks sample database and simple associations. Here's Visual Studio 2005 displaying a diagram of the complete Northwind sample database (Access version) with the Entity Container and Entity Type elements rearranged but before making changes to Entity Set/Type names:

Click images to display in full size (800 x 600). Click here for a Web page with more images.

The Designer inherits limitations of the ADO.NET vNext August 2006 CTP: No support for SQL Server's text, ntext, or xml data types (Employees.Notes, Products.HomePage, and Categories.Description fields), or Associations for tables with composite primary keys (Line Items). Therefor, FK_Order_Details_Orders and FK_Order_Details_Products Association lines are missing in the preceding diagram and Order_Details entities aren't updatable.

Changing Entity Type, Entity Set, and Association Names

The prototype Designer doesn't offer an option to singularize plural table names as Entity Type names. Changing Entity Type, Entity Set, Association, and Association Set names in the designer is easy, but making the requisite edits to the MSL and SSDL files isn't a walk in the park. Search and replace works for most name changes, but the CSDL file often loses the 63 suffix that distinguishes the two Employees/Employee endpoints after editing other names. You'll also encounter issues with entity or property names that conflict with C# or VB reserved words, such as Order in VB.

Here's the JPEG diagram of the preceding diagram with Entity Type names singularized, the Orders Entity Set renamed to SalesOrders, and Order Details changed to LineItems.

Notice that cardinality of the Employee.EmployeeID:Employee.ReportsTo Association in the preceding screen capture was mistakenly changed from 0..1:0..* (one-to-many) to 0..1:0..1 (one-to-one with nulls allowed) manually. The cardinality was later returned to that generated from the database, but the values in the diagram didn't change to conform the updated setting.

Testing Associations, Deferred Loading, and MARS

After changing Association names in the schema and mapping files, it's a good idea to verify that the renamed Associations continue to correspond to table relationships. This requires a more sophisticated test harness than the C# console demo projects favored by the ADO.NET vNext team. You need a grid to view properties of multiple entities and their associated foreign key values or counts of related entities.

Fortunately, you can populate a DataGridView control directly by setting its DataSource property value to a ageneric List generated by applying the ToList() method to the Entity Set returned from a LINQ to Entities query:

Additional code populates the added columns with the foreign key values. Unfortunately, the position sequence of all but the added columns have no relationship to their ordinal value in the table or their sequence in the Entity Type definition. The ADO.NET vNext team intends to improve databinding with, perhaps, a ToBindingList() method or wrapper.

Associations have the GetEntityTypeName() method to return a single related entity and the GetEntityTypeNameRef() method to return a reference to an entity. Like LINQ to SQL (DLinq), the Designer "lazyloads" related entities; that is, the entities aren't retrieved unless or until your code requests them. DLinq has the Including() operator to return related instances such as SalesOrder.Customer or Product.Category. The EDM requires you to load related entities explicitly with a procedure such as:

Public Sub GetCustomerIDForSalesOrder(ByVal so As SalesOrder, _
ByVal intRow As Integer)
If Not FK_SalesOrders_Customers.GetCustomerRef(so).IsLoaded Then
FK_SalesOrders_Customers.GetCustomerRef(so).Load()
End If
Dim Cust As Customer
Cust = FK_SalesOrders_Customers.GetCustomer(so)
If Cust IsNot Nothing Then
dgvList.Rows(intRow).Cells("CustomerID").Value = Cust.CustomerID
End If
Cust = Nothing
End Sub

If you execute the preceding code within a For...Next loop to retrieve the SalesOrder (so) entities, you must enable SQL Server 2005's Multiple Active Resultsets (MARS) feature to open a second DataReader on the same database connection:

The alternative, which provides a very slight performance gain, is to iterate the List of SalesOrder instances three times to retrieve the foreign key values:

'Retreive foreign key values sequentially
For Each so In lstOrders
GetCustomerIDForSalesOrder(so, intRow)
intRow += 1
Next
intRow = 0
For Each so In lstOrders
GetEmployeeIDForSalesOrder(so, intRow)
intRow += 1
Next
intRow = 0
For Each so In lstOrders
GetShipperIDForSalesOrder(so, intRow)
intRow += 1
Next

Here's the WinForm test harness's DataGridView displaying a few foreign key values in the last three columns:

Notice the relatively long time (80 seconds) required for the almost 2,500 T-SQL queries required to retrieve the three foreign key values for 830 SalesOrder entities. Once the related entities are loaded in memory, execution time decreases dramatically, as shown here:

Note: The design of the Employees table implements the adjacency technique for representing a hierarchy in a table of a relational database. Path and nested sets are alternative—but less performant—approaches. Anthony Bloesch's February 15, 2006 "Hierarchies (trees) in SQL Server 2005" post compares the three techniques and their performance in a 20,000-node implementation.

Cache Uniqueing Issues and Lost Values

EDM employes uniquing, the process by which Object/Relational Mapping (O/RM) tools maintain a single copy of each unique entity instance, such as a Customer, Order, or Product, regardless of how many times a query retrieves an instance. The test harness's 91 Customers placed 830 Orders, thus the (830 - 91) or 739 Customer instances retrieved from the database to obtain foreign-key value are duplicates. Similarly, one of nine employees receive credit for orders so 821 retrieved Employee instances are duplicates. Finally, there are three shipping companies, so 827 Shipper instances are duplicates. Adding counters for the Load method of Customer, Employee, Shipper, Categorie, and Supplier entity instances confirms the retrieval of duplicates. (These counters aren't don't appear in the test harness screen captures.)

Uniquing is implemented by an Identity Map. Martin Fowler states in Patterns of Application Architecture that an Identity Map: "Ensures that each object gets loaded only once by keeping every loaded object in a map. Looks up objects using the map when referring to them." Most O/RMs implement Identity Maps in cache, often as a hashtable of values calculated from primary keys. The fact that the missing ReportsTo values are duplicates of the two values returned from the cache indicates a problem with the uniqueing algorithm.

Note: A counter on Employee entity instance retrievals indicates that Employee instances aren't cached. The first time cached Order instances are traversed, the Employee retrieval instance counter increments by 839; additional traversals increment by 830.

Frans Bouma, developer of the LLBGen O/RM for .NET and Microsoft MVP, contends in his "Why a cache in an O/R mapper doesn't make it fetch data faster" blog post that caching doesn't necessarily make data retrieval more efficient. The time to look up the newly retreived object's ID in an Indentity Map that contains a large number of hashes might contribute to the apparent poor performance when initially filling the Orders grid.

Composite Keys, Surrogate Keys, Natural Keys, and Ruby on Rails

Bouma also observes that "As ... compound [composite] PKs can complicate the calculation process, it's not that straight forward to get the lookup process of entities very efficient."

Natural keys, which are values in the table, prevent entering duplicate data. The Northwind Order Details table has a composite natural primary key that consists of the OrderID and ProductID columns. In addition to uniquely identifying a row, the primary key prevents data entry operators from entering rows that duplicate a product line item in an order. Hopefully, support for composite primary keys will arrive in ADO.NET vNext shortly.

The dual Web role application has been running in Microsoft's South Central US (San Antonio) data center since September 2009. I believe it is the oldest continuously running Windows Azure application.

About Me

I'm a Windows Azure Insider, a retired Windows Azure MVP, the principal developer for OakLeaf Systems and the author of 30+ books on Microsoft software. The books have more than 1.25 million English copies in print and have been translated into 20+ languages.

Full disclosure: I make part of my livelihood by writing about Microsoft products in books and for magazines. I regularly receive free evaluation software from Microsoft and press credentials for Microsoft Tech•Ed and PDC. I'm also a member of the Microsoft Partner Network.