“Professional ADO.NET 3.5 with LINQ and the Entity Framework” Is in Stock at Amazon

Amazon lists my latest book for WROX/Wiley, Professional ADO.NET 3.5 with LINQ and the Entity Framework, as “In Stock” although it was originally scheduled for availability on 2/3/2009.

Thanks to Julie Lerman for the heads-up; her Programming Entity Framework book goes to the printer today for availability on 2/2/2009.

Good news! I didn’t expect the book to be available until mid-February.

Julie writes: “Dave Sceppa's book, Programming the Microsoft ADO.NET Entity Framework, still says ‘Dec 10, 2008’ although Amazon lists it as ‘not yet released’ so I'm not sure what's up with that.”

Microsoft Press says Dave’s book is scheduled to be published on 2/24/2009.

This earlier Professional ADO.NET 3.5 with LINQ and the Entity Framework: Table of Contents post of 11/8/2008 contains a chapter-level TOC.

Technorati Tags: Entity Framework,Entity Data Model,EDM,EDM Designer,Entity SQL,LINQ,LINQ to Entities,EntityDataSource,LINQ to SQL,LinqDataSource,LINQ to DataSets,LINQ to XML,LINQ to XSD,Object/Relational Mapping,O/RM

LINQ and Entity Framework Posts for 5/14/2008+

Note: This post is updated daily or more frequently, depending on the availability of new articles.

VS 2008 SP1/.NET 3.5 SP1 Beta-Related Posts

An Example of Updating an ADO.NET Data Services SP1 Beta Web Service and Client

My Updating Simple ADO.NET Data Services and Data Service Clients to SP1 Beta post of May 15 2008 details the process of updating a sample ADO.NET Data Services Project (client and server) from the last Astoria CTP and Entity Framework Beta 3 to the current VS 2008 SP1 Beta drop.

Added: 3/15/2008 4:00 PM

Updated ASP.NET Dynamic Data Samples for SP1 Beta

The MSDN Code Gallery's ASP.NET Dynamic Data Preview offers a May 12, 2008 release of the original Dynamic Data Samples. Here's a description of the samples (lightly edited):

• DbImageAPI.zip contains a sample field template that shows how to display and add images from a database.
• DoubleColumn.zip is an advanced example that shows how to display two columns from a database in one column in data bound control.
• DynamicLabel.zip shows how the main control inside of a Dynamic Control can be exposed and in this case used as the AssociatedControlId of a Label control.
• The ExistingWebsite.zip contains an example of using the Dynamic Data controls in an existing website. It contains both the old way and the new way of doing this and how much less code the new method uses.
• Scaffold.zip just shows an empty web application with a database and a data model. Once run it will display the dynamically created scaffold that Dynamic Data generates.

You can use SP1 Beta's new ASP.NET Routing Engine (System.Web.Routing) with ASP.NET Dynamic Data SP1 Beta.

Added: 3/15/2008 10:50 AM

ASP.NET Dynamic Data SP1 Beta Help File Link

Individual topics are a bit difficult to find in the online help files, so here's the link to ASP.NET Dynamic Data Overview.

Added: 3/15/2008 10:30 AM

David Hayden Continues his ASP.NET Dynamic Data SP1 Beta Series

His March 15, 2008 Dynamic Data Websites - Displaying Tables and Columns using ScaffoldTable and ScaffoldColumn Attributes post shows you how to control scaffolding of specific tables or columns.

Links to earlier posts on ASP.NET Dynamic Data SP1 Beta series are in the "David Hayden Posts Walkthroughs for the ASP.NET EntityDataSource and Dynamic Data Scaffolding" topic of LINQ and Entity Framework Posts for 5/12/2008+

Added: 3/15/2008 10:30 AM

ADO.NET Team: SP1 Beta Breaking Changes to the Entity Framework

The ADO.NET team posted Entity Framework Breaking Changes - Visual Studio 2008 & .NET 3.5 SP1 Beta in the morning of May 14, 2008, but the link from another ADO.NET post was broken until larger in the afternoon. The changes fall into the following categories:

• Schema Changes( covers CSDL, SSDL and ProviderManifest Xml formats)
• Object Services Changes
• Providers
• Metadata

Ryan Hauert and David DeWinter Find EDM Designer Bugs in SP1 Beta

In his New Entity Framework designer bugs in SP1 beta of May 14, 2008, Ryan Hauert finds the following bug most frustrating:

The Error List window now lists fake errors for all of our subtypes that are children of abstract entities with a message similar to “Entity types B, C, D are mapped to table A without discriminating conditions on all mappings to the table.”

I reported similar, non-fatal "fake errors" from SQL Server Compact 3.5 SP1 data sources that disappeared on building the project in my Upgraded Northwind.sdf File for SSCE v3.5 Available for Download of May 8, 2008 (Updated May 11, 2008).

Ryan reports he found another bug that "relates to result column bindings for mapped sprocs on subtypes." He includes links to the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum and Microsoft Connect for the two bugs. VS 2008 SP1 Beta has its own forum here.

Danny Simmons Offers Links to SP1 Help Files, FAQ, and Breaking Changes

Danny's A little more about SP1 Beta and the EF post of May 14, 2008 includes links to online help files for:

• ADO.NET Entity Framework
• Entity Data Model Tools
• ADO.NET Data Services Framework
• EntityDataSource Web Server Control Overview

Neither Danny or I were able to search these files. I tried LiveSearch and Google without success.

He's also upgraded his FAQ to SP1 at Updated Entity Framework FAQ.

Diego Vega Describes How to Add the Missing LinqDataSource to SP1 Beta's Toolbox

His May 14, 2008 Can't find the Entity Data Source? post gives the step-by-step instructions for adding the LinqDataSource component to the Web forms toolbox and, if necessary add the assembly manually.

The ADO.NET Team Admits There's Little New in LINQ to SQL

The What's New in the SP1 Beta for LINQ to SQL? describes these new features:

Across the LINQ to SQL we have made numerous bug fixes, better SQL translation for queries comparing nullable columns in Visual Basic, and support both in the runtime and the designer for SQL Server 2008.

New SQL Server 2008 Support includes:

• Support for connecting to SQL Server 2008 databases in Server Explorer
• Drag & drop tables in SQL Server 2008 databases from Server Explorer
• Support for the following new types: Date, Time, DateTime2, DateTimeOffset, Filestream

Apparently, LINQ to SQL doesn't plan to take advantage of the upgraded DataContractSerializer that can handle EntitySets and EntityRef for associated entities in a entity.

Diego Vega Provides Some History and Explains Why the EntityDataSource Wraps the Data-Bound Entity

Julie Lerman posted Thinking about the EntityDataSource on January 22, 2008 with the goal of preventing issues with the LinqDataSource being repeated in the EntityDataSource. Diego's New EntityDataSource Control for ASP.NET post of May 13, 2008 attempts to answer at least part of Julie's issues.

The EntityDataSource already is raising questions in the forums. In the EntityDataSource: To wrap or not to wrap of the same date, Diego explains the reasons and rules for wrapping entities instead of exposing them directly.

ADO.NET Entity Framework & LINQ to Relational Data MSDN Code Gallery Site Updated

The ADO.NET Team's UPDATED WIKI: Home link points to the location of updated EFExtensions, eSQLBlast and Entity Framework Query Samples, as well as new Entity Framework Lazy Loading and EFMappingHelper releases.

Diego Vega's ADO.NET Entity Framework Query Samples post of May 13, 2008 points to a "release[, which] contains query samples that are compatible with the .NET 3.5 SP1 Beta version of Entity Framework."

Other LINQ and Entity Framework Topics

Mike Taulty Releases a Two Seasons of Silverlight 2 Beta 1 Screencasts

Mike lists the topics of his 50 New Silverlight 2 Beta 1 Screencasts in this May 15, 2008 post. If his segments were a network weekly, he'd have two years (seasons) in the can.

Here are six segments related to data binding, LINQ to XML, and the DataGrid:

• 13. Simple Data Binding of UI to .NET Classes
• 14. List Based Data Binding
• 15. Data Binding UI to .NET Classes with Converters
• 39. Reading/Writing XML with LINQ to XML
• 49. Getting Started with the DataGrid
• 50. Insert, Update, Delete with the DataGrid

You can download a Silverlight 2 app with a clickable listbox of segments. The selected item comes from Microsoft's free Silverlight Streaming service.

Added: 3/15/2008 10:30 AM

LINQ to SharePoint Competitor INQ4SP in the Works

SharePoint developer Agnes Molnar has developed a competitor to Bart De Smet's LINQ to SharePoint implementation. Following are her three posts that describe LINQ4SP:

• Announcing LINQ4SP (May 8, 2008) compares LINQ4SP with Bart's LINQ to SharePoint
• LINQ4SP - Code Generator (May 14, 2008) has a demo of browsing a SharePoint site and selecting the items to use in the generated code.
• LINQ4SP - Simple Query (May 15, 2008) has a video that shows query generation and basic filtering

Bart appears to have abandoned support for LINQ to SharePoint after going to work for Microsoft.

P.S. OBA is an abbreviation of Microsoft Office Business Applications.

Jonathan Aneja Covers LINQ to DataSet's AsEnumerable() Method

Jonathan's post of May 14, 2008, How LINQ to Dataset works in VB (Jonathan Aneja), describes how the VB compiler handles DataTables with the System.Data.DataSetExtensions' AsEnumerable() method.  Using LINQ to Dataset in an .aspx page (Jonathan Aneja) of May 13, 2004 describes why Imports System.Data is required rather than Imports System.Data.DataSetExtensions.

Dan Whalin Solves the Problem with Timestamp Data in GridView Controls

His Working with the GridView and the System.Data.Linq.Binary Type post of May 14. 2008 coerces the timestamp column to the Binary data type to prevent exceptions during serialization. Dan says:

When I tried to get the serialized TimeStamp data back into the RowVersion property of the LINQ to SQL object (which is the Binary type I mentioned earlier) I kept getting a conversion error initially.  I tried several different options such as the Convert class, converting character arrays to byte arrays, etc. but nothing worked at first.  The main problem was that I was trying to convert the data held in key.Values[1] object to a String type and then convert that back to the Binary type.  After playing around with it more I tried the super simple way of converting the data.  I cast the serialized value directly to the Binary type which was much easier than I was expecting.

John Papa's Writing Data Access with Silverlight 2 for O'Reilly

In As it Once Was, I am Writing a Book of May 14, 2008, John Papa announced that he's back to writing books again and expects O'Reilly will publish Data Access with Silverlight 2 in November or December 2008.

LINQ to MPI.NET Is Nearing Release with a Multithreaded Scoping Model

After months of silence, Adam Sobieski 's Update: LINQ for MPI.NET post of May 14, 2008 announces that Phoster Incorporated's LINQ for MPI.NET implementation now has a multithreaded scoping model. The announcement says the new feature:

[S]implifies the utilization of distributed objects which can utilize the calling MPI communicator context while utilizing .NET interfaces such as ICollection. Before developing this multithreaded scoping model, many of the methods of the distributed data objects had a communicator as a first parameter.

For more information about MPI.NET and LINQ to MPI.NET, read the "LINQ to MPI.NET in Development at Phoster Incorporated" topic of Link and Entity Framework Posts for 11/19/2007+.

Derek Whittaker Finds LINQ to XML Three Times Faster than XPath to Find a Unique Value in a 5.25 MB Document

Derek continues his comparisons of LINQ to XML and XPath with these three posts:

• XLinq'n in the new world - Data manipulation comparisons with Xml/XPath  (May 14, 2008)
• XLinq'n in the new world - Structure Modification comparisons with Xml/XPath (May 14, 2008)
• XLinq'n in the new world - Speed comparison with Xml/XPath (May 14, 2008)

In his last post, Derek determines LINQ to XML is three times faster to XPath to find a unique value in a 5.25-MB document. Unfortunately, Derek didn't provide the code he used for the search.

Colin Meek Describes the SP1 Changes in the Updated EF Extensions Library

Colin's Using LINQ Expressions to Generate Dynamic Methods II of May 13, 2008 says:

The EF Extensions library has been updated to work with the beta and includes several public and internal changes. Source code is available at http://code.msdn.com/EFExtensions. The latest release introduces some performance improvements in the materializer (you can read about the library here). These improvements illustrate another powerful expression pattern.

To improve code clarity, the EF Extensions API encourages you to write:

var products = command.Materialize<Product>(r => new Product {
    ProductID = r.Field<int>("ProductID"),
    Name = r.Field<string>("Name"),
    …
}).ToList();

instead of a more complex (but faster) expression. Colin goes on to describe optimizations for the materializer.

Steve Lasker Announces SQL Server Compact 3.5 SP1 Beta Availability and Encryption Options

In his SQL Server Compact 3.5 SP1 Beta available announcement, Steve notes that SSCE 3.5 SP1 Beta is included in VS 2008 SP1 Beta, but the independent version lets VS 2005 users take advantage of the update.

Steve's Security and Encryption for SQL Server Compact describes the use of Encrypting File System and native SSCE password-based encryption to protect confidential data.

Rob Conery Refactors the MVC Storefront's Shopping Cart and Adds an Authorization Feature

His MVC Storefront, Part 10: Shopping Cart Refactoring and Membership post of May 14, 2008 begs for reader feedback.

Technorati tags: VS 2008 SP1, .NET 3.5 SP1, VS 2008 SP1 Beta, .NET 3.5 SP1 Beta, Visua Studio 2008 SP1, Entity Framework, Entity Framework Extensions, Entity Data Model, EDM, LINQ to SQL, LINQ to DataSets, LINQ to XML, LINQ, LINQ to MPI.NET, LinqDataSource, LINQ to Sharepoint, LINQ4SP, ASP.NET Dynamic Data, EntityDataSource

LINQ and Entity Framework Posts for 3/31/2008+

Note: This post is updated daily or more frequently, depending on the availability of new posts.

Microsoft France Academic Interns Create Visual LINQ Query Designer (VLinq)

Mitsuru Furuta, Microsoft France's manager and technical lead of the VLinq project, recruited two talented students, Simon Ferquel and Johanna Piou, who was a French Imagine Cup participant, to write a Visual Studio designer template that helps LINQ naifs write LINQ queries on a graphical design surface implemented in WPF.

Mitsu's Visual Linq query builder for Linq to Sql: VLinq post of April 2, 2008, offers a brief history of the VLinq project and a step-by-step demonstration with LINQ to SQL as the data source. He says the project took "almost one year of work and organization." The student internships lasted six months.

Here's the Visual LINQ Query Designer surface running a simple query:

Do you see something missing in the generated T-SQL prepared statement?

You can download the add-in template from MSDN Code Gallery.

Added: April 2, 2008

Microsoft Data Centers Expand the Unit of Deployment from Bricks to ISO 668 20-Foot Intermodal Containers

Mary Jo Foley reports in her Microsoft builds out its first containerized datacenter post of April 2, 2008 that the new Northlake, Ill. data center will use containers holding 1,000 to 2,000 servers as the basic unit of deployment.

Mike Manos who leads the Microsoft Global Foundations Data Center team made the first public announcement of a containerized production data center at Data Center World. According to Microsoft Data Architect James Hamilton:

The Microsoft Chicago facility is a two-floor design where the first floor is a containerized design housing 150 to 220 40’ containers each [holding] 1,000 to 2,000 servers. Chicago is a large facility with the low end of the ranges Mike quoted yielding 150k serves and the high end running to 440k servers.

The Database Bricks/Containers Backstory and Their Relation to SSDS

I wrote about bricks and containers as units of data-center deployment almost two years ago (to the day) in my Very Large Databases: Bricks, BitVault and BigTable post of April 6, 2006. That post's "Really Big Smart Bricks and Databases" topic starts with:

The ultimate brick is a Google data center in a shipping container, as postulated by PBS's Robert X. Cringely (Mark Stephens) in his 11/17/2005 column, "Google-Mart: Sam Walton Taught Google More About How to Dominate the Internet Than Microsoft Ever Did."

I concluded:

Clearly, large distributed file systems and databases must run on very big smart bricks or hardware of similar scale. Google probably probably runs the world's largest distributed file system.

It's a reasonably safe bet that future Midwest users of SQL Server Data Services will be accessing data in the container instead of data in the cloud.

The "VLDB Implementation/Deployment Issues" topic quoted Dare Obasanjo's "Greg Linden on SQL Databases and Internet-Scale Applications" post, which in turn quotes Greg Linden:

What I want is a robust, high performance virtual relational database that runs transparently over a cluster, nodes dropping in an out of service at will, read-write replication and data migration all done automatically. I want to be able to install a database on a server cloud and use it like it was all running on one machine.

and goes on to describe Adam Bosworth's database requirements of the time:

Adam Bosworth, who managed the intial development of Microsoft Access, went on to found CrossGain (together with Tod Neilsen, Access marketing honcho), sold CrossGain to BEA and became BEA's chief architect and senior vice president, and [was then] VP Engineering at Google, lists these three features that database users want but database vendors don't suppply: Dynamic schema, dynamic partitioning of data across large dynamic numbers of machines, and modern [Googlesque] indexing. Adam wants the the Open Source community to "[g]ive us systems that scale linearly, are flexible and dynamically reconfigurable and load balanced and easy to use." Adam does mean give, not sell.

It seems to me that BitVault's smart bricks with appropriate deployment and management applications would fulfill Greg's and all but Adam's economic desires for reference data, which now constitutes more than half of the data stored by North American firms

The tragedy is that Jim Gray didn't live to witness the massive scaleup and scaleout of his original CyberBrick concept.

Added: April 2, 2008

DevExpress Implements LINQ Data Sources for ASPxGrid View and XtraGrid

The Server mode using LINQ? Let's wax rhapsodic post of April 1, 2008 by DevExpress CTO Julian M. Buckness describes how DevExpress refactored its ASP.NET and Windows form grid controls from dependence on eXpress Persistent Objects (XPO) to support for any LINQ provider that returns an IQueryable<T> type.

The DevExpress's 2008 Volume 1 release adds LinqServerModeDataSource for pageable ASPxGridView controls for ASP.NET, which appears to emulate the ASP.NET LinqDataSource. The LinqServerModeSource supports the XtraGrid and adds similar paging capability. You can view a short screen cast that demos LinqServerModeDataSource here.

The "Developer Express uses LINQ for grid controls" article for SDTimes by David Worthington became one of the magazine's Top Stories by April 2, 2008.

Added: April 2, 2008

Eugenio Pace Implements a Mock SSDS for LitwareHR with SQL Server Express

Eugenio's LitwareHR on SSDS - Part IV - Data access enhancements 2: developing offline post of April 1, 2008 describes how the new SQL Server Data Services (SSDS) version of LitwareHR implements a proxy to let developers switch between SSDS and a local SQL Server Express instance as the data source for the demo project.

Eugenio's post describes his objective in implementing the proxy:

Our goal while developing LitwareHR was to actually make the dev team as independent and autonomous as possible. Notice I say the dev team, not the application itself. We were comfortable in taking a dependency with SSDS for runtime, that is when LitwareHR would be deployed in a production data center. But we wanted developers to be able to work even if they are flying on a plane with no connectivity (like I was!). In one sentence we wanted a "mock SSDS".

With this in mind, we developed a new proxy implementing the same interface the real proxy implements, but against a local SQL Server Express database.

Following are previous posts in Eugenio's LitwareHR for SSDS series:

• SQL Server Data Services - SSDS - New version of LitwareHR (3/5/2008)
• LitwareHR on SSDS - Part I - Multi-tenancy & Flexibility
• LitwareHR on SSDS - Part II - The data access layer
• LitwareHR on SSDS - Part III - Data access enhancements 1: caching (3/24/2008)

Added: April 2, 2008

Dave Robinson Writes in 20 Minutes an SSDS Client That Adds and Retrieves Red Bull Recipes to/from SSDS

Challenged to write a "full-featured" SSDS app in the 20 minutes before his CodeTrip presentation last Friday at the Red Bull headquarters in Santa Monica, Dave came up with the project he describes in his SSDS @ Red Bull post of April 1, 2008. The project uses the Excel Add-in for SSDS that Dave describes in his Using Excel with SSDS post of March 21, 2008.

[Dave's link to SSDS @ RedBull in his SSDS, The Code Trip & Red Bull post of April 1, 2008 doesn't work for me; it's fixed above.] Dave fixed his link.

Update April 3, 2008: You can watch a 09:54 TechjZulu video segment that includes an interview with Dave, which begins at 02:40.

TechZulu claims to be "one of the first weblog that introduces southern California's companies to the technology community."

Added: April 1, 2008 Updated: April 2, 2008

Noam Ben-Ami Describes Entity Framework's Future Update Model from Database Feature

Noam's Update Model From DB post of April 1, 2008 describes the post-CTP2 version of the Entity Data Model (EDM) Designer's Update Model from Database feature that detects changes to the data store's schema and modifies the physical schema (SSDL) and the mapping (MSL) layer as follows:

1. Adds EntityTypes, Properties and/or Associations for objects that have tables or columns in the database but are missing from the EDM. You can select which tables, columns, or both to add.
2. Modifies EntityTypes, Properties and/or Associations where database tables or columns have changed from the structure represented by the current SSDL schema.
3. Removes EntityTypes, Property and/or Associations from the SSDL only whose database tables or columns have been deleted from the database. This process doesn't delete the element from the model (CSDL section).

The post-CTP2 EDM Designer handles renamed entities or properties, but not renamed tables or columns.

Noam says that these are the limitations of the forthcoming designer:

• It does not update the types of properties when the corresponding database columns change.
• It does not “resurrect” entity types – once you have deleted a type, the only way to get it back is to recreate it manually, or delete the corresponding SSDL by hand in the XML editor.
• It will also not “resurrect” properties.
• If you change the keys that define your type, all current associations lose their identity and new associations will be brought in – you will need to delete old associations.
• It cannot detect database object renaming – renames will show up as the deletion of an old object and the addition of a new one.

Of all the improvements, my favorite is the ability to handle renamed entities because I work primarily with tables having plural names which I always singularize for entity names.

Added: April 1, 2008

Oren Eini: NHibernate 2.0 Alpha 1 Released

Ayende says in his NHibernate 2.0 Alpha is out! post of March 31, 2008:

It gives me great pleasure to announce that NHibernate 2.0 Alpha 1 was released last night and can be downloaded from this location.

We call this alpha, but many of us are using this in production, so we are really certain in its stability. The reason that this is an alpha is that we have made a lot of changes in the last nine months (since the last release), and we want to get more real world experience before we ship this. Recent estimates are of about 100,000 lines of code has changed since the last release.

NHibernate is the standard of comparison for Windows object/relational mapping (O/RM) tools. The "NHibernate Mafia" (James Kovacs, Scott Bellware, Jeffrey Palermo, and Jean-Paul Boodhoo) were responsible for making the Entity Framework folks finally understand what persistence ignorance is all about.

Added: April 1, 2008

SQL Server Data Services Team Seeks Ruby on Rails, PHP and Java Developers for April SDR

Ryan Dunn's Interested in SQL Server Data Services? post of March 31, 2008 touts early access to SSDS and a seat at an SSDS software design review (SDR) at the Microsoft Silicon Valley Campus on April 24 - 25, 2008 for developers using non-Microsoft technologies, such as Ruby on Rails, PHP and Java.

PS: Watch for my cover story about SSDS coming in the July 2008 issue of Visual Studio Magazine.

Query Caching In ADO.NET Data Services Feedback Wanted

Pablo Castro is seeking feedback from users of ADO.NET Data Services (Astoria) about the tradeoff between the performance benefit of compiling/caching data retrieval queries and the code complexity of defining the queries when taking advantage of Astoria's query interceptor feature. Pablo's Looking for feedback: query caching in data services post of March 31, 2008 dives deep into the caching issues.

Data retrieval queries involve elaborate, provider-specific composition. For example, Pablo cites this flow for Astoria with Entity Framework as the data provider:

URL -> Expression Tree -> Canonical Query Tree -> View expansion/Query simplification -> Canonical Query Tree -> SQL -> Rows (DataReader) -> Entities (DataReader) -> Objects -> Serialization (Atom/JSON)

Thus compiling and caching these queries, especially complex ones that involve deeply nested data structures resulting from use of the $expand keyword, has a very beneficial effect on performance. Undoubtedly the perf improvement will be substantially greater than that reported for LINQ to SQL by Rico Mariani in his famous five-part DLinq (LINQ to SQL) Performance series, as well as his more recent Performance Quiz #13 -- Linq to SQL compiled queries cost of January 11, 2008 and Performance Quiz #13 -- Linq to SQL compiled query cost -- solution of January 14.

Data interceptors, which enable adding a custom filter predicate to the query, are commonly used to implement data access control but complicate the compiling/caching process. What Pablo is seeking is opinions on the relative importance of query performance versus code complexity for implementing data inceptors. Without knowing how much the perf benefit is affected by the two options he offers at the end of his post's Part 2, it's difficult to make a recommendation. I'm inclined to favor performance because the interceptor code is likely to be suited to cookbook-style documentation.

The ultimate decision is likely to affect SQL Server Data Services, also.

PS: Watch for my cover article, "Retrieve and Update Data in the Cloud with Astoria," about ADO.NET Data Services coming in the May 2008 issue of Visual Studio Magazine.

PPS: My "ASP.NET MVC: Is the new MVC pattern right for you?" TechBrief is in the March 15, 2008 issue of Redmond Developer News. Unlike on p. 27 of the print edition, the electronic version has the right blog address.

Taking Advantage of Stored Procedures in the Entity Framework

The Entity Framework (EF) defaults to generating dynamic parameterized Entity SQL (eSQL) statements to populate (hydrate) entities from tables in the underlying relational data store and parameterized data manipulation language (DML) statements to create, update and delete entities and their associated tables.

Many organizations and individual DBAs prefer or insist on the use of stored procedures rather than dynamic SQL for SELECT, INSERT, UPDATE or DELETE operations. Stored procedures prevent the need to grant users direct access to tables, which could compromise database security. In many cases, the capability to efficiently execute stored procedures instead of dynamic SQL is the principal determining factor in choosing an object/relational mapping (O/RM) tool.

EF has built-in support for stored procedures but there's no central source of complete, up-to-date documentation and how-to information for using stored procedures with the current Beta 3 version and its Entity Data Model (EDM) Designer CTP 2. The EF Extensions, which add flexibility in the use of custom stored procedures and speed their execution, hasn't been widely used or thoroughly documented. (There were only 181 EF Extension downloads from the MSDN Code Gallery as of 3/31/2008, and Colin Meek only recently published his ADO.Entity Framework: Stored Procedure Customization article on 3/26/2008)

The following three articles contain detailed instructions with screen captures and code examples for creating entities, changing from dynamic SQL to stored procedures, testing EDMs with stored procedures, as well as minimizing the number of stored procedure calls and improving performance with the EF Extensions:

Migrating to SQL Server Stored Procedures with the EDM Designer December CTP 2 (March 27, 2008)

Testing Stored Procedure Replacements for Entity SQL Statements (March 29, 2008)

Minimize Stored Procedure Calls and Improve Performance with EF Extensions (March 30, 2008)

Jim Zemlin: "The Linux ecosystem should pay attention to" SQL Server Data Services

Jim Zemlin is the Executive Director of the Linux Foundation so his advice is well taken, especially by Microsoft. In his Cloud Computing - Did anybody notice Microsoft’s SQL Server Data Services Announcement? post of March 14, 2008, Jim says:

Linux has an early lead in this area with service offerings like Amazon’s Linux based S3 and IBM’s Blue Cloud which uses Xen and PowerVM virtualized Linux operating-system images.

Today Web 2.0 start ups are flocking to these services as a way to reduce their cost, have world class infrastructure, and most importantly to be able to scale up and down based on demand. It won’t be long before mainstream enterprises follow this trend. It turns out that IT operations combined with convenient tools is something that will be the core competency of companies like Google, IBM, Amazon, and Microsoft in the future. ...

A product from Microsoft that doesn’t require their tools or lock you into their platform offered as a service over the web? Time[s] are indeed a changing. The interesting thing about this is that the folks who need to adjust for this change the most are software companies that don’t have broad IT operations infrastructure and management competencies similar to the likes of Google and Amazon. Software companies like Microsoft are waking up to this. Are other going to follow suit? [Emphasis added.]

Like many others, Jim identifies the wrong services as SSDS competitors. Amazon's S3 stores raw data (blobs) and IBM touts Blue Cloud as providing on demand computing services, similar to Amazon's EC2. SSDS's only direct competitor is Amazon's SimpleDB, as far as I've been able to determine.

Thanks to the SSDS Team for the heads up.

Added: April 1, 2008 [Not an April Fool]

Video: Liam Cavanaugh and Neil Padgett Demonstrate Synchronizing SSDS Data with Access and Outlook

From the Microsoft Sync Framework blog's Sync to the Web - Enable Offline Access to Web Data from any Data Store post of March 12, 2008:

At MIX 2008 Liam Cavanagh discusses how to use the Microsoft Sync Framework along with SQL Server Data Services to provide synchronization cabability across multiple applications.

In the demonstration, he synchronizes contacts between Microsoft Access, Outlook and SQL Server Data Services. However, by building new providers, any application can participate in synchronization.

Later in the presentation Neil Padgett goes through a detailed internals discussion of the Microsoft Sync Framework metadata and provider models.

You can view the WMV (high or low bitrate) version of the 41:00 informal presentation. Here's another link to Neil's formal MIX 08 session: Using Microsoft Sync Framework and FeedSync (T32).

The OakLeaf Systems blog will start carrying more Sync Framework/Services content because of its importance to SSDS and its pending integration with Astoria to create "Astoria Offline."

Here's a link to an earlier "Introducing Microsoft Sync Framework" presentation by Moe Khosravy, the lead PM for the Sync Framework.

Added: April 1, 2008

Technorati tags: Entity Framework, Entity Data Model, EDM, EDM Designer, Entity Framework Extensions, Stored Procedures, Materializers, EntitySet, CommandScope, StoreCommand, StoreConnection, ADO.NET Data Services, Project Astoria, SQL Server Data Services, SSDS, SimpleDB, Blue Cloud, Linux, Nhibernate, Sync Services, Microsoft Sync Framework, Sync Framework, Astoria Offline, LINQ to SQL, LINQ to Entities, LINQ, LinqDataSource, VLinq, Visual Linq Designer, Bricks, Containers, Data Centers

Minimize Stored Procedure Calls and Improve Performance with EF Extensions

Colin Meek's "Multiple Result Sets" topic of his ADO.Entity Framework: Stored Procedure Customization post of March 26, 2008 provides sample code for obtaining a Category entity and its Category.Products EntitySet from an object store based on the Northwind sample database.

Substituting the Entity Framework (EF) Extension's EntitySet objects for the ObjectResult type and taking advantage of Materializers and stored procedures (sprocs) that return multiple result sets greatly reduce the number of sproc calls and execution time for moderate-size object graphs.

Updated 5/31/2008: Table comparing execution times for local and remote SQL Server 2005 instances added.

Background

My Testing Stored Procedure Replacements for Entity SQL Statements post of March 28, 2008 makes 161 stored procedure (sproc) calls to populate 80 Product entities with simple type values plus Category and Supplier EntityReferences. Category and Supplier entities don't have their Products EntitySets populated, which would require another 37 calls for a total of 198 calls. Here's the sequence:

1. 1 call to GetAllProducts populates the Products EntitySet (80 entities)
2. 80 calls to GetCategoryForProductById to populate the Product.Category EntityReferences
3. 80 calls to GetSupplierForProductById to populate the Product.Supplier EntityReferences
4. 8 calls to GetProductsByCategoryId to populate the Category.Products EntitySet
5. 29 calls to GetProductsBySupplierId to populate the Supplier.Products EntitySet

A major difference between LINQ to SQL and Entity Framework's entity mapping is that LINQ to SQL entities expose foreign key values, such as Product.CategoryID and Product.SupplierID, but EF hides them because they are a relational artifact. If these values were visible, it would be possible to reduce the number of calls to the total number of objects (80 + 8 + 37 = 117) and use client-side code to cache the objects and then construct and attach the EntitySets and EntityReferences for Associations.

Attempting to Reduce Sproc Calls with ObjectResult Types Cached to List<T>s

An potential approach to reducing the number of calls would be to start with Categories and Suppliers, rather than Products, because Categories or Suppliers can furnish the Product entities. Only 39 sproc calls would be required. Here's the drill:

1. 1 call to GetAllCategories to cache Category entities
2. 8 calls to GetProductsForCategoryId to populate Category.Products EntitySets, which contain all Product entities
3. 1 call to GetAllSuppliers to cache Category entities
4. 29 calls to GetProductsForSupplierId to populate Supplier.Products EntitySets, which also contain all Product entities
5. Cache a Products list from the Category.Products EntitySets
6. Sort the Products list in ProductID order
7. Attempt to Attach the appropriate Product.CategoryReference EntityReference from a Category entity whose Category.Products EntitySet contains the Product
8. Attempt to Attach the appropriate Product.SupplierReference EntityReference from a Supplier entity whose Suppliers.Products EntitySet contains the Product

Here's the code for the preceding steps:

// Get Category entities
catList = ctxNwind.GetAllCategories().ToList();
foreach (Category cat in catList)
{
    foreach (Product prod in ctxNwind.GetProductsByCategoryID(cat.CategoryID))
        cat.Products.Add(prod);
    
    prodList.AddRange(cat.Products);
}

// Get Supplier entities
vendList = ctxNwind.GetAllSuppliers().ToList();
foreach (Supplier vend in vendList)
{
    foreach (Product prod in ctxNwind.GetProductsBySupplierId(vend.SupplierID))
        vend.Products.Add(prod);
}

// Sort prodList by ProductID 
prodList.Sort(delegate(Product p1, Product p2)
    { return p1.ProductID.CompareTo(p2.ProductID); });

// Get Catetory and Supplier EntityReferences
foreach (Product prod in prodList)
{
    foreach (Category cat in catList)
    {
        if (cat.Products.Contains(prod))
        {
            // Type conflict between Category cat and required CategoryReference
            // Nocando: prod.CategoryReference.Attach(cat);
            prod.Category = cat;
            break;
        }
    }
    foreach (Supplier vend in vendList)
    {
        if (vend.Products.Contains(prod))
        {
            // Type conflict between Supplier vend and required SupplierReference
            // Nocando: prod.SupplierReference.Attach(vend);
            prod.Supplier = vend;
            break;
        }
    }
}

Here's the updated test harness displaying 0 Relationships as the result of missing CategoryReference and SupplierReference EntityReferences.

Note: Observe the speedy execution of the Get Product button handler when no EntityReferences are attached as Relationships.

Unfortunately, you can't attach a Category or Product type as a CategoryReference or ProductReference, so you're stuck with executing

prod.CategoryReference
    .Attach(ctxNwind.GetCategoryForProductById(prod.ProductID).First());
prod.SupplierReference
    .Attach(ctxNwind.GetSupplierForProductById(prod.ProductID).First());

in the foreach (Product prod in prodList) loop as described in the "Retrieving Products and Attaching Category and Supplier EntityReferences" topic of the previous post. This adds 160 additional sproc calls for a total of 199.

Here's the test harness after attaching the EntityReferences:

Note: Execution time is almost 4x that for no relationships, so it's obvious that attaching EntityReferences for relationships under these condistions is a heavyweight process. However, the original code with relationships runs in 0.083 seconds, so this entire approach is suboptimal with conventional ObjectResult types, at best.

Solving the Problem with EF Extensions' Materializers, EntitySets and Multiple Result Sets

The following code performs the same operations as the preceding example but calls GetCategoryAndRelatedProducts() and GetProductAndRelatedProducts() methods, which in turn call sprocs that return multiple resultsets. The methods use Materializers with the default "shaper" delegate to return Product, Category, and Supplier types and substitute ObjectContext.EntitySet types for ObjectContext.ObjectResult types.

// Keep connection open for all operations
DbCommand command = ctxNwind.CreateStoreCommand("usp_GetCategoryAndProductsByID",
    CommandType.StoredProcedure, new SqlParameter("CategoryID", 0));
using (command.Connection.CreateConnectionScope())
{
    // Get Categories and Products EntitySets, cache Categories
    // Production version would return a list of CategoryIDs with an sproc
    for (int i = 1; i <= 8; i++)
    {
        catList.Add(GetCategoryAndRelatedProducts(i, command));

        //Add Products in Category to Product list
        prodList.AddRange(catList.Last().Products);
    }

    // Get Suppliers and Products EntitySets, cache Suppliers
    command = ctxNwind.CreateStoreCommand("usp_GetSupplierAndProductsByID",
        CommandType.StoredProcedure, new SqlParameter("SupplierID", 0));
    for (int i = 1; i <= 29; i++)
    {
        vendList.Add(GetSupplierAndRelatedProducts(i, command));
    }
    // Total of 37 sprocs (would be 39 in production)
}

// Sort prodList by ProductID 
prodList.Sort(delegate(Product p1, Product p2) 
    { return p1.ProductID.CompareTo(p2.ProductID); });

// Iterate Products in ProductID order
foreach (Product prod in prodList)
{
    // Get Supplier for each Product
    foreach (Supplier vend in vendList)
    {
        if (vend.Products.Contains(prod))
        {
            prod.Supplier = vend;
            break;
        }
    }
}

The magic that handles attaching the Products.Category and Product.Supplier properties as EntityReferences appears to be in the EntitySet type and the Bind() method.

private static Materializer<Product> s_prodMaterializer = 
    new Materializer<Product>();
private static Materializer<Category> s_catMaterializer = 
    new Materializer<Category>();
private static Materializer<Supplier>; s_vendMaterializer = 
    new Materializer<Supplier>();

// Modified version of Colin Meek's original code for this method
public Category GetCategoryAndRelatedProducts(int categoryID, DbCommand command)
{
    Category category;
    command.Parameters[0].Value = categoryID;
    using (DbDataReader reader = command.ExecuteReader())
    {
        // First result set includes the Category
        EntitySet<Product> ProductSet = 
            new EntitySet<Product>(ctxNwind, "Products");
        EntitySet<Category> CategorySet = 
            new EntitySet<Category>;(ctxNwind, "Categories");

        category = s_catMaterializer
            .Materialize(reader)
            .Bind(CategorySet)
            .SingleOrDefault();
        // Second result set includes the related Products
        if (null != category && reader.NextResult())
        {
            category.Products.Attach(s_prodMaterializer
                .Materialize(reader)
                .Bind(ProductSet));
        }
    }
    return category;
}

// Clone of preceding method for suppliers
public Supplier GetSupplierAndRelatedProducts(int supplierID, DbCommand command)
{
    Supplier supplier;
    command.Parameters[0].Value = supplierID;
    using (DbDataReader reader = command.ExecuteReader())
    {
        // First result set includes the Supplier
        EntitySet<Supplier> SupplierSet = 
            new EntitySet<Supplier>(ctxNwind, "Suppliers");
        EntitySet<Product> ProductSet = 
            new EntitySet<Product>(ctxNwind, "Products");

        supplier = s_vendMaterializer
            .Materialize(reader)
            .Bind(SupplierSet)
            .SingleOrDefault();
        // Second result set includes the related Products
        if (null != supplier && reader.NextResult())
        {
            supplier.Products.Attach(s_prodMaterializer
                .Materialize(reader)
                .Bind(ProductSet));
        }
    }
    return supplier;
}

Note: The preceding methods have been modified to utilize a single Command and ConnectionScope object for all sproc calls in the sequence.

Colin says the following about the EntitySet class:

To facilitate tracking, EFExtensions includes an EntitySet class which manages activities related to Entity Data Model (EDM) entity sets. The class includes a FindOrAttach method which either attaches the given entity to the state manager for tracking purposes or returns an existing entity with the same key. This simulates the identity resolution behavior that the EF provides when running queries.

And this about the Bind() method:

For convenience, a Bind extension method that attaches a sequence of entities is also included.

The upshot is that the code executes only 37 sprocs (39 in production) and runs in 0.033 seconds, which is 2.5 times faster than the 0.083 seconds of the original version with the database on the test machine.

The following table compares the execution times for a local and networked instance of SQL Server 2005 Express for 80 products for four test conditions:

Test Conditions	Calls	Local	Network
Original version (with Entity.Products EntitySets added)	161	0.083 s.	0.255 s.
Categories and Suppliers first (no relationships created)	37	0.043 s.	0.060 s.
Categories and Suppliers first (160 relationships created)	197	0.169 s.	0.290 s.
Final Categories and Products first (with EF Extensions)	37	0.033 s.	0.059 s.

Here's the proof that the code calls only 37 sprocs (1 is for opening the connection):

And there's no problem with adding, editing, and removing entities.

Note: Julie Lerman observes in her EFExtensions and stored procs with shaped results post of March 30, 2008:

What Colin's post shows, however is sprocs that return shaped results. A standard function import can't do this...it needs to map to a single entity type.

I believe that multiple result set capabilities, freedom from the necessity to declare FunctionImports, fewer stored procedure calls for object graphs of similar complexity, and what appears to be a significant performance boost will be the most interesting features of the EF Extensions.

Technorati tags: Entity Framework, Entity Data Model, EDM, EDM Designer, Entity Framework Extensions, EF Extensions, Materializers, EntitySet, Stored Procedures, CommandScope, StoreCommand, StoreConnection