VSM Cover Story

Objectify Data With ADO.NET vNext

The ADO.NET vNext August 2006 Community Technical Preview reincarnates ObjectSpaces’ object-relational mapping technology and OPath query language as the Entity Framework, Entity Data Model, Entity SQL, and LINQ to Entities for the Visual Studio “Orcas” release.

Technology Toolbox: VB.NET, C#, SQL Server 2005, XML
Other: Visual Studio 2005, or Visual Basic or Visual C# Express editions, Microsoft Visual Studio Code Name "Orcas" Language-Integrated Query, May 2006 Community Technology Preview, ADO.NET vNext August 2006 Community Technical Preview, SQL Server 2005 Express Edition or higher

The data-management landscape will undergo a sea change for .NET developers when ADO.NET vNext releases in 2007 with the .NET Framework 3.0 and Visual Studio vNext, code-named "Orcas."

Among the most significant aspects of this change: ADO.NET vNext will include an object/relational mapping (O/RM) tool, called the ADO.NET Entity Framework (EF). EF's primary goal, like that of all other O/RM tools, is to reduce the disconnect—often called an impedance mismatch—between relational and object models. EF incorporates the Entity Data Model (EDM), the Entity SQL (eSQL) query language, and LINQ for Entities extensions to VB 9.0 and C# 3.0. EDM is a traditional entity-relationship (ER) data model that defines Entities as instances of EntityTypes (Customer, for example) and EntitySets as keyed collections of Entities (Customer). An EntityKey (CustomerID) uniquely identifies an Entity for viewing and updating. The Entity Key enables Entities to participate in Relationships, which are instances of RelationshipTypes that define Associations between EntityTypes; Relationships are members of RelationshipSets.

EF offers VS developers a mapped, data-access component replacement for the Enterprise Libfrary's Data Access Application Block and an object persistence alternative to DataSets. EF automates generating an updatable client-side view of business entities from a relational data source.

According to Microsoft Research's Sergey Melnik, "Up to 40% of the code in enterprise systems" is devoted to solving object/relational impedance mismatches. That's why having a robust, cost-effective and performant O/RM solution is so important to .NET developers. In the words of Microsoft Research's Dr. Jim Gray, another O/RM objective is to minimize the programming effort required to "ask a question and obtain a collection."

I'll walk you through EF's basic components and programming techniques, as well as show you how EF and LINQ for Entities differ from the earlier SQL Server-oriented DLink (now called LINQ for SQL). Before diving into the specifics of how to use EF, it's important to understand the problem space fueling the creation of this technology. Object-oriented programming became the norm for most Windows developers during the late 1990s, but there's been no similar revolution in database technology. Object databases failed to gain significant market share or developer mindshare, but most .NET and, virtually all Java, application programmers have come to consider relational databases to be stores for persisting business objects. (DBAs, of course, would argue this classification.)

Efficiently moving complex business objects to and from row-column relational data isn't easy, as Microsoft discovered when attempting to productize their first O/RM add-in for VS called ObjectSpaces. ObjectSpaces first appeared as a technical preview named "Orcas" at Microsoft's 2001 Professional Development Conference (PDC). The Visual Studio team added ObjectSpaces to VS 2005 Beta 1, dropped it from Beta 2, and postponed it to WinFS at TechEd 2005, before finally imposing the death sentence on ObjectSpaces when WinFS bit the dust on June 23, 2006. Over that five-year time span, the .NET-specific O/RM market grew from a few to at least 36 open-source and proprietary products as of mid-2006. At this time, Microsoft didn't even offer a CTP of a general-purpose OR/M tool to support domain-model programming advocates and complement its LINQ technology for integrating query linguistics into C# 3.0 and VB 9.0.

Get EF Up and Running
Two Microsoft whitepapers -- "Next-Generation Data Access: Making the Conceptual Level Real" and "ADO.NET Tech Preview: Overview" -- Channel9's "What's coming in ADO.NET?" video, and a few blog posts about a forthcoming entity-based O/RM tool appeared on the Web May 10, 2006, but disappeared a day or two later. Tech-Ed 2006 brought a more concrete representation of Microsoft's OR/M intentions with Pablo Castro's "Next-generation Data Access in .NET Applications with ADO.NET vNext" presentation on June 12, 2006. The missing articles, videos, and blog posts reappeared without fanfare on June 26, 2006. Finally, Microsoft rekindled the .NET O/RM fire with the (belated) first CTP of their fledgling O/RM tool on August 17, 2006.

Getting started with EF requires SQL Server 2005 or SQL Express. It also requires VS 2005 Standard Edition or higher, VB 2005 Express, or C# 2005 Express. (Note that EF doesn't support Visual Web Developer 2005 Express.) The only supported operating systems are Windows Server 2003 SP1 and Windows XP SP2; Windows Vista betas aren't in the list.

Download and install the Microsoft Visual Studio Code Name "Orcas" Language-Integrated Query, May 2006 Community Technology Preview (LINQ May 2006 CTP), if you haven't done so previously. Be sure to use the Add/Remove Programs tool to remove earlier LINQ versions before installing the LINQ May 2006 CTP. Next, download and install the ADO.NET vNext CTP (Community Technology Preview)—August 2006. The WebAppSample project's VB version requires that you install the VS 2005 Web Application Projects add-in to support the VS 2003-style SampleName.vbproj file.

After completing the installation, you'll have a LINQ Preview menu item with C# 3.0 Samples and Visual Basic 9.0 Samples folders, 18 documentation and readme files, and an ADO.NET vNext DTP item with Samples, and Docs folders with links to the ADO.NET Entity Framework Overview.doc and Readme.htm files. Reading the latter two docs before opening the EF projects in VS can minimize the mystery of the sample apps. The installation places all documentation and sample project files in subfolders of your \Program Files\Microsoft SDKs\ADO.NET vNext CTP\ folder. The \Data subfolder contains a NorthwindCTP.mdf sample database that installation attaches to an SQL Server 2005 instance by default. If you're running SQL Server Express, or want to use an existing Northwind.mdf database, make the appropriate changes to the <connectionString> section of each sample project's AppName.exe.config file.

You validate your ADO.NET vNext installation by navigating to the \Program Files\Microsoft SDKs\ADO.NET vNext CTP\Samples\VB or CSharp folder. Double-click on the Samples.sln solution file to load four sample console projects—LINQtoDataSet, LINQtoEntities, MappingProvider, and ObjectServices—and the WinAppSample WinForm project that demonstrates data binding to entity sets. Build and run the WinAppSample to verify that you've installed the required EF components. The CSharp solution includes the ...\WebAppSample\ file system Web site and Web Service; the VB Web Project has a separate solution: ...\VB\WebAppSample\WebAppSample.sln. All projects except LINQToDataSet reference the NorthwindLib class library that generates the EF's Entity Data Model (see Figure 1). The \Program Files\Microsoft SDKs\ADO.NET vNext CTP\Samples\{VB | CSharp}\NorthwindLib\obj\Debug\NorthwindLib.Model.{vb | cs} file contains the auto-generated source code for the NorthwindLib.Northwind top-level ObjectContext and its four strongly-typed EntityTypes: Categories, Products, Customers, and SalesOrders. Burying the source for the EF's primary component in the ...\obj\Debug folder appears to be an attempt by Microsoft to discourage developers from manually editing the file. Figure 3.

Achieving the Layered Look
EF’s three-layer EDM requires a relational store schema (SSDL file) that’s derived from the database’s physical schema, a conceptual schema (CSDL file) for the business object domain, and a mapping schema (MSL file) between the two schemas (see Figure 2). The ADO.NET team promises a graphical model designer and visual mapping tool to eliminate the current need to edit the three XML schema files by hand. There’s a good chance these tools will resemble the DLinq designer. According to Microsoft’s Stuart Kent, the DLinq designer was built with the then-current Microsoft Visual Studio 2005 SDK including Domain-Specific Language (DSL) Tools CTP. The promise of GUI editing tools for the schema files is "déjà vu all over again" for ObjectSpaces pilgrims.

The SSDL store schema—NorthwindLib.Target.ssdl for the sample projects—is an XML file that corresponds to ObjectSpace's Relational Schema Document (RSD) and describes the database as an EntityContainer whose tables are EntitySets. A row is an EntityType with Key attribute(s) and each column is a Property. The Type attribute value of a Property is a member of the database's type system, such as smallint, money, or datetime for SQL Server. Note that the CTP doesn't support SQL Server's ntext or image data types. The hand-crafted NorthwindLib.Target.ssdl file contains no information about the relationships between EntitySets—called AssociationSets—or between EntityTypes—called Associations (see Listing 1).

The CSDL conceptual schema—NorthwindLib.Model.csdl—is the counterpart of ObjectSpace's Object Schema Document (OSD) and generates the C# or VB source code to create the strongly-typed EntityContainer, EntitySets, AssociationSets, and EntityTypes partial classes and their Properties (see Listing 2). If you alter the conceptual schema significantly, you must recompile the library file and, usually, also conform your application layer's source code to the new domain model. Partial classes let you implement behavior independent of generated code.

The MSL mapping schema—NorthwindLib.cs.msl—is similar to ObjectSpace's Mapping Schema Document (MSD) and maps the SSDL data store's tables and fields to the corresponding EntityContainer, EntitySets, Properties, and AssociationSets (see Listing 3). The SSDL and MSL files provide the object/relational-model/schema independence that LINQ for SQL lacks. The DLinq designer generates classes with fixed, one-to-one relationships between tables and EntityTypes, and restricts you to predefined column names as business object field (property) names. With EDM, if your relational model changes, you need to edit the SSDL and MSL files only; modifying the CSDL and recompiling the class library or application isn't required. EF emphasizes a relational-to-object modeling (R2OM) approach because most early projects are likely to use existing databases. In a future version, Microsoft might add bidirectional capabilities to their graphical O/RM designer that would enable creating or updating databases from object designs.

Create an Object Class Library
To create a new EF Model Object class library, open a new project, expand the New Project dialog's ADO.NET vNext CTP node, accept the default Model Object template, and click on OK to start the Entity Data Model Wizard. Select the Generate from Database method, and click on Next to open the Database Connection dialog. Select the server, database, and authentication method, accept the connection string defaults (see Figure 4), and then click on Next to open the Table Selection dialog. Mark the check boxes for the tables to include in your object model (see Figure 5), and click on Finish to generate the three schema files. Check the Task List to discover initial issues, such as missing properties (fields) of the ntext or image type, and then build the LibraryName.Model.cs or .vb file. This article's sample VB class library is AdventureWorksV3.dll; its references to the System.Data.CTP, System.Data.Entity, and System.Query namespaces have been added automatically.

A WinForm project with a text box or a bound DataGridView control is the best choice for testing non-trivial object-model classes. Create a Windows Form project from the ADO.NET vNext CTP template to add the same CTP and LINQ references; add the SSDL, MSL, and CSDL files to your project folder as links; and set each file's Copy to Output Directory property value to Copy if Newer. Your executable app must have compile-time and runtime access to the current schema files with auto-updating links. Copy the class library's <connectionStrings> elements from the App.Config file to the same relative position in the AppName.exe.config file. Change the connection string's name from the library's default DatabaseNameConnectionString to the executable application's default SchemaNamespaceName.EntityContainerName that you obtain from the CSDL file, as shown here for the sample code's AWv3TestWinApp.exe application.

   <add name=
   connection string=
   "Data Source=localhost;Initial 

   Security=True"" providerName= 
   "System.Data.Mapping" />

Note that the preceding connection string specifies the System.Data.Mapping provider, which provides an EDM-compliant database connection called the mapping provider. The mapping provider uses the MSL file to translate from, and to, the relational and conceptual schemas. EDM mapping supports: property aliases for column names; 1:1, 1:n, 1:0..1, and n:m relationships; combining tables having a 1:1 relationship into a single entity; and three types of inheritance mapping.

Testing your EDM class library reference and mapping files requires creating an ObjectContext:

Using AWv3 As New AdventureWorksV3

This statement constructs an AWv3 ObjectContext. AWv3 uses the default AdventureWorksV3Mode.AdventureWorksV3 connection string described previously.

Writing Your First eSQL Query
Next, execute an eSQL query against an ObjectContext to return a collection. The eSQL query language replaces ObjectSpaces' arcane OPath syntax with an extended version of ANSI SQL. eSQL supports inheritance with an IS [NOT] OF([ONLY] Type) test and FROM OFTYPE(Collection, Type) to return only specific EntityTypes from a collection. Use SELECT to return a collection of DataRecords and SELECT VALUE to return a collection of CLR types, as in this query against the AWv3 Object Context:

SELECT VALUE sp FROM AdventureWorksV3Model.
   AdventureWorksV3.SalesPerson AS sp

If your query consists solely of a SELECT VALUE ... FROM statement and your AppName.exe.config file uses the default connection string name, you can substitute "SalesPerson" for the entire eSQL statement. For example, this code block loads the 17-member SalesPersons collection as a List(Of SalesPerson) into a DataGridView control:

Using AWv3 As New AdventureWorksV3
   Dim SalesPersons As Query(Of SalesPerson) _
      = AWv3.GetQuery(Of _
   dgvSalesPersons.DataSource = _
End Using

I doubt if you could find a competitive O/RM technology to fill a grid with fewer lines of code, but the column order of the DataGridView doesn't come close to resembling the EntityType's Property elements's order. Key (System.Data.EntityKey), EntityState (System.Data.DataRowState = Unchanged) and Relationships (System.Data.Objects.EntityRelationships) columns are scattered among the columns with data from the table. It takes a substantial amount of code to rearrange and format the DataGridView's columns (see Figure 6). The August CTP's "Entity SQL Quick-Reference"document provides simple syntax command and expression examples; watch for the upcoming whitepaper -- "eSQL: An Entity SQL Language, ADO.NET Technical Preview" --which will include more detailed specifications and syntax examples.

Updating an entity is equally simple. For example, this code adds $1,000 to each SalesPerson's Bonus and displays the new values:

Using AWv3 As New AdventureWorksV3
   Dim SalesPersons As Query(Of SalesPerson) _
      = _AWv3.GetQuery(Of _
   For Each sp In SalesPersons
      sp.Bonus = sp.Bonus + 1000D
   dgvSalesPersons.DataSource = _
End Using

The August 2006 CTP doesn't support stored procedures, but the "ADO.NET Entity Framework Overview" whitepaper explains that the ADO.NET team is "working on introducing stored-procedure support to the system in a way that integrates naturally with the rest of the Entity Framework." Similarly, full databinding now requires adding a substantial amount of code, but the team "would like to include more explicit support for data binding, probably by exposing binding-list implementations that know about the Entity Framework and can coordinate the interactions with it (e.g., add/remove to notify the object-context appropriately)." The August 2006 CTP's samples' WinAppSample.exe application illustrates a master-child form implemented with updatable DataGridView controls, BindingNavigators, and BindingSources.

Executing eSQL queries against the mapping layer returns the familiar DbDataReader object, which lets you apply your "classic ADO.NET" skills to entity sets. MapConnection uses the same connection string as ObjectContext, and MapCommand requires eSQL as its CommandText property value, as shown here:

Dim strConn As String = _
   ConfigurationManager.ConnectionStrings( _
   "AdventureWorksV3Model.AdventureWorksV3" _
Using mapConn As New MapConnection(strConn)
   Dim mapCmd As MapCommand = _
   mapCmd.CommandText = _
      "SELECT VALUE sp FROM “ + _
      “AdventureWorksV3Model.Adventure” + _
      “WorksV3.SalesPerson AS sp"
   Dim drSP As DbDataReader = mapCmd._
   While drSP.Read
      ‘Process rows and columns sequentially
   End While
End Using

Note that you must specify CommandBehavior.SequentialAccess as the ExecuteReader method's argument, which requires that you process both columns and rows sequentially.

Test Drive LINQ to Entities
LINQ to Entities lets you take advantage of compile-time query syntax, element-name validation, and other LINQ features. For example, this LINQ query adds 1000 to the Bonus amount for SalesPersons with SalesPersonID values above 1020:

Using AWv3 As New AdventureWorksV3
   Dim EligibleSPs = From sp In _
      AWv3.SalesPerson _
      Where sp.SalesPersonID > 1020 _
      Select sp _
      Order By sp.LastName, sp.FirstName
   For Each Dim esp As SalesPerson In EligibleSPs
      esp.Bonus = esp.Bonus + 1000D
   dgvSalesPersons.DataSource = _
End Using

Some areas remain underdeveloped at this time. For example, consider this case from the whitepaper's "4.2 LINQ and the ADO.NET Entity Framework" section. Using the more business-like Where p.HireDate > New DateTime(2002, 1, 1) constraint expression throws an anonymous exception when attempting to execute the query by assigning the List(Of AdventureWorksV3.SalesPerson) to the DataGridView.DataSource property. The same is true for using other expressions, such as Where sp.HireDate < datHireDate, Where sp.HireDate < #8/2/2001#, or Where sp.HireDate.Value.Year = 2001. As the whitepaper's section "LINQ to Entities" states, "there are various scenarios that are not implemented today and will fail with NotImplementedException." LINQ to Entity expressions in this article's sample code fail with this error: "An unhandled exception of type 'System.Reflection.TargetInvocationException' occurred in mscorlib.dll. Additional information: Exception has been thrown by the target of an invocation." Presumably, a future CTP will fix this problem. In the meantime, check out the CTP sample solution's LINQtoEntities project—especially the RelationshipTraversal and LazyLoadingRelatedEntitities procedures.

There's no question that the ADO.NET vNext EF is an early work in progress. For example, schema files hand-crafted by the ADO.NET team differ greatly from those generated by the August 2006 CTP's Entity Data Model Wizard. I can't access related entities, such as SalesPerson.SalesOrder, Categories.Products, or vice-versa from ObjectContexts of Wizard-generated AdventureWorksV3 or NwindModelLib VB class libraries. Association and AssociationSet elements are missing for EntityTypes that have multi-column keys, such as Order_Details. This problem that might be related to a similar issue I discovered in my earlier "Link Takes Shape in May CTP" article on FTPOnline.com.

Relational database management systems (RDBMSs) must evolve, according to Dr. Gray, to "containers for information services," and "ecosystems [where] object orientation is the key structuring strategy [and] everything is a class." He sees a database as a complex object and the core object as the DataSet, a nested relation with metadata. However, Dr. Gray considers EF's Entities to be the "next step in DataSets"and and describes LINQ for SQL and LINQ for XML as "a BIG deal." I believe that EF and EDM will gain much wider acceptance than LINQ for SQL because of LINQ for SQL's very restrictive one-to-one relationship between tables and EntityTypes.

Domain-driven modeling, object persistence, and O/RM are evolving rapidly, and are becoming increasingly complex. To check out this early iteration of Microsoft's promise of impedance-matched O/RM nirvana, download the ADO.NET v.Next August 2006 CTP, and this article's AdventureWorksV3 class library and AWv3TestWinApp. Prepare for the object/relational future by getting up to speed on Entity Framework features and foibles. But be forewarned; there's no guarantee that the EF, EDM, and even LINQ, won't suffer ObjectSpaces' fate before Orcas's release date.

More Information

- If you plan to run the VB version of the WebForm sample app, download the VS 2005 Web Application Projects add-in to enable using the WebAppSample.vbproj file.

-Download and install the ADO.NET vNext CTP (Community Technology Preview)

- a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=1E902C21-340C-4D13-9F04-70EB5E3DCEEA&displaylang=en)" target="_blank">Download and run the Microsoft Visual Studio Code Name “Orcas” Language-Integrated Query, May 2006 Community Technology Preview installer from MSDN (required to install the ADO.NET vNext CTP)

comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.