LINQ and Entity Framework Posts for 10/29/2007+

More Tidbits from Julie Lerman: An Upgrade to the EDM Designer and QueryViews vs. Defining Views

Julie's Another tidbit from the forums.. what's coming in the EDM Designer post of November 2, 2007 quotes the ADO.NET Team's Noam Ben Ami:

We are hard at work on this feature right now and hopefully we'll be able to get it into CTP2 of the designer.

Along with support for stored procedures, it is our highest priority.

and offers three prognostication of things to come for the EDM Designer.

EDM QueryViews vs Defining Queries (and read-only views) of the same date explains in great detail the difference between a QueryView that you specify in the mapping layer (MSDL) and a Defining Query for which you write the T-SQL in the physical layer (SSDL).

Added: November 3, 2007

A QueryDataSource Replacement for the LinqDataSource

Bernal Schooley's LINQ : Paging and sorting LINQ Queries with a custom QueryDataSource post of November 3, 2007 describes the LinqDataSource as "the Linq equivalent of the SqlDataSource, something many people avoid at all costs." His custom QueryDataSource server control handles server-based paging and sorting for ordinary LINQ queries and, unlike the LinqDataSource, isn't connected at the hip to LINQ to SQL.

Bernal has a brief but interesting series of posts on LINQ to SQL starting in late September 2007.

Added: November 3, 2007

Julie Lerman: Handling DB Schema Changes with the Entity Data Model Designer

One of the primary selling points of the Entity Framework (EF) and its Entity Data Model (EDM) is the ability to deal with minor changes to the underlying database's schema with edits to the XML files for the physical (SSDL) and mapping (MSDL) layers. You don't need to alter source code and recompile your data layer.

Julie's Entity Data Model - What to do when a change is made in the database of November 2, 2007 demonstrates how to edit the SSDL and MSDL files with the Beta 2 version of the EDM Designer. In this case the changes are to the names of two columns. Things get a bit trickier when DBAs make structural changes to the schema, such as splitting or consolidating tables.

Added: November 2, 2007

Beth Massi Posts Four New LINQ to XML Video Segments

Beth's New Visual Basic LINQ to XML Videos Released! post of November 2, 2007 describes four new video clips that "walk you through the basics of LINQ to XML, creating, querying and transforming documents as well as how to import XML namespaces and infer schemas to enable IntelliSense." The last video covers the LINQ to XML application described in the "Beth Massi Moves Excel Data to and from SSCE with LINQ to XML" topic below.

Beth's post also includes an updated set of links to her previous LINQ to XML articles.

Added: November 2, 2007

Multi-Tier Improvements Coming to Entity Framework

Julie Lerman reports in her A hint of what to look for in Entity Framework Beta3 post of November 1, 2007 that the ADO.NET team intends to make the EntityKey for 1:m associations (EntityRefs) serializable for WCF messaging. 

I assume in my Multi-Tier Improvements Coming to Entity Framework post of November 1, 2007 that the DataContractSerializer serializes both EntitySet(s) and EntityKey(s), but it's not entirely clear from Brian's statement that Julie quotes.

Added: November 1, 2007

Zlatko Michailov's Entity SQL Tip #1: No JOINs

Entity SQL Tip #1: A well defined query against a well defined entity data model does not need JOIN.

Navigation properties in combination with nesting sub-queries should be used instead. These latter constructs represent task requirements much more closely than JOIN does. That makes it easier to build and maintain correct Entity SQL queries.

Added: November 1, 2007

Julie Lerman Demystifies Creating Associations in the EDM Designer

If you don't have a predefined primary key:foreign key relationship predefined for a pair of related tables your mapping with the Entity Framework's Entity Data Model (EDM) designer, establishing the relationship with the designer is a painful process to some and a complete mystery to most folks. For this reason, Entity Framework pilgrims usually set up the relationships for all their tables' foreign keys in the SQL Server Management Studio [Express] Foreign Key Relationships dialog before starting the mapping process. If you're working with a database that's not under your control, you probably don't have the ALTER TABLE privileges necessary to add foreign key constraints and enforce referential integrity.

Julie comes to the rescue with another lavishly illustrated set of step-by-step instructions in her October 31, 2007 Mapping Associations in the EDM Designer post. Her tutorial for establishing a one-to-many relationship between two tables in the EDM designer keeps you out of the sand traps and away from the water hazards that the ADO.NET folks appear to have added just to make the process of setting navigation properties "interesting." Thanks, Julie.

Added: November 1, 2007

All's Quiet on the Project Jasper Front

Project Jasper was one of the ADO.NET team's next big things at the MIX 07 conference. There now seems to be a serious question about a Project Jasper CTP for Visual Studio 2008 RTM. Read more in my Has Project Jasper Been Swallowed by a Black Hole? post.

Added: November 1, 2007

Update 11/2/2007: Andrew Conrad left a detailed comment about the current status and future of Project Jasper. Andy notes in the comment that he's the Dev Lead for Astoria and still the Dev Lead for Project Jasper and LINQ to DataSet.

Beth Massi Moves Excel Data to and from SSCE with LINQ to XML

Most folks use Microsoft Access to import Excel workbooks into .mdb/.accdb files or SQL Server tables and Excel itself to import data from Access tables. But Beth picked SQL Server Compact Edition (SSCE) v.3.5, which has managed (System.Data.SqlServerCe) and OLE DB (Sqlceoledb35.dll) drivers but doesn't have the old-timey ODBC driver that Access requires.

So Beth's Quickly Import and Export Excel Data with LINQ to XML of October 31, 2007 post starts by copying the XML document for a dummy row from an Excel 2003 XML file and pasting it to VB 9.0's text editor (preceded by Dim sheet = ) and adding five Imports statements for the namespaces. She then replaces the dummy data with embedded expressions, and adds a query expression to import Northwind Customers data from an SSCE database into a new document and save it as Customers.xml. Finally, Beth demonstrates how to export data from Customers.xml back into the SSCE table.

I'm not so sure about the "Quickly" adverb, but it's an interesting example of LINQ to XML's capabilities and shows you why Silverlight ultimately will include LINQ to XML.

Note: Beth's smashed finger must be healing because this post is longer than some of mine.

Added: October 31, 2007

Rick Strahl's and My Issues with WCF WSDLs and LINQ

Rick Strahl observes in his WCF and segmented WSDL Files = Problems post of October 30, 2007 that the Web Service Definition Language (WSDL) documents generated by Windows Communication Foundation (WCF) Web services include a <wsdl:import namespace="someNamespace" location="uRLforWSDL"> element, which limits interoperability with more mature Web service clients. 

Apparently, Rick and I are working on similar WCF serialization techniques for LINQ to SQL entities. My Serializing Cyclic LINQ to SQL References with WCF of October 30, 2007 describes additional problems with serializing cyclic references with WCF's DataContractSerializer and the lack of standards for bidirectional serialization with id and idref attributes.

Added: October 30, 2007

Frans Bouma and LINQ to LLBLGen Pro: Part 8

It appears to me that Frans is making good progress in "trimming his expression tree" with reduction code. As Frans notes, his "current code can recognize predicates, field expressions, joins (not group joins yet), can evaluate local variables into values used into predicates, projections of single fields and entity projections" from his sample query:

string city = "Munchen";
var q = from c in metaData.Customer
        join o in metaData.Order
           on c.CustomerId equals o.CustomerId
        join od in metaData.OrderDetail
           on o.OrderId equals od.OrderId
        where c.City == city && c.Country == "Germany"
           && o.EmployeeId == 2 && (od.Quantity * od.UnitPrice) > 500
        select (o.EmployeeId > 10);

Frans provides screen captures of his custom Expression Tree Visualizer during the seven states from State 0 - Start to State 6 - The final tree has been reduced by combining select expressions.

Added: October 30, 2007

Julie Lerman Discovers Silverlight 1.1 Doesn't Support LINQ to XML or WCF (Yet)

Julie's Silverlight 1.1 - Look before you leap (into LINQ or into WCF) post of October 30, 2007 laments not researching the status of LINQ to XML and WCF in the current bits before .

I made the following comment in the "Julie Lerman to Present Sessions on Astoria at REMIX Boston and New England Code Camp" topic of my LINQ and Entity Framework Posts for 10/29/2007+ post:

Her resultsets are formatted as plain-old-XML (POX), which will be replaced by Microsoft's new Web3S XML format in a later CTP. Silverlight 1.1 has an Astoria client library; XML-formatted data is one reason that a future Silverlight version will include LINQ to XML. (Emphasis added.)

I quoted more about future Silverlight support for LINQ in the "Scott Guthrie on Using C#'s (New?) null Coalescing (??) Operator with LINQ to XML" topic in my LINQ and Entity Framework Posts for 9/14/2007+ post:

Tim Anderson posted on 9/19/2007 an interview with Scott, Scott Guthrie on .NET futures, conducted at the MIX07 UK conference held in London on September 11-12, 2007. Tim's September 11, 2007 Silverlight at Mix07 UK post mentions in his "Web service support and LINQ" section:

"Silverlight will support JSON, WCF and SOAP. It will also include LINQ, with the possibility of creating custom LINQ data providers - Guthrie mentioned possibilities like a LINQ provider for Amazon’s S3 service."

And here's an early reference from my MIX07 Session Videos with LINQ, EF, or VB[x] Hooks - Part 2 post of May 3, 2007:

Aaron and Tim have created Socializer, a Silverlight UI that demonstrates:

• The Silverlight 1.1 Alpha client
• LINQ to Objects in Silverlight 1.1
• LINQ to XML in future Silverlight CTPs [emphasis added]
• Semantic Web
• XML, RDF (FOAF)
• Data binding, querying, and aggregation
• A socially-aware rich Internet application (RIA) that includes personal content aggregated from MySpace, de.licio.us, and Flickr.

I didn't get around to researching WCF support in Silverlight 1.1, but Julie found a couple of workarounds for using WCF with the current bits.

Added: October 30, 2007

LINQ to SharePoint with Bart de Smet Webcast on October 31

LINQ to SharePoint developer Bart de Smet will join Glen Gordon and Lynn Langit for a geekSpeak Webcast at Noon PDT on Wednesday, October 31, 2007. Here's Bart's bio from the geekSpeak blog:

A former Visual C# MVP, Bart De Smet now works at Microsoft Corporation on the WPF dev team in an SDE role. Prior to this new challenge, Bart was active in the Belgian community evangelizing various Microsoft technologies, most of the time focusing on CLR, language innovation and frameworks. In his evangelism role, he's been speaking at various events and attended several international conferences including TechEd Europe, IT Forum and the PDC. In 2005, Bart graduated as a Master of Informatics from Ghent University, Belgium. Two years later, Bart became a Master of Computer Science Software Engineering from the same university.

Register for the Webcast here.

Added: October 30, 2007

Followup Resources for geekSpeak: LINQ to SharePoint with Bart De Smet with a Channel9 link to the clip.

Updated: November 3, 2007

New LINQ to Flickr Provider on Codeplex

MehfuzH has posted a new LINQ to Flicker provider on CodePex.

His New LINQ provider for Flickr post provides detailed instructions for using the API to select, add, or remove images.

Added: October 30, 2007

Technorati Tags: LINQ to SQL, LINQ to XML, LINQ to Entities, LINQ to SharePoint, LINQ, WCF, WSDL, Entity Framework, EntityDataModel, EDM Designer, Serializationation, DataContract, DataContractSerializer, LINQ to Flickr, LLBLGen Pro, Silverlight, SQL Server Compact Edition, SSCE, VB 9.0

Serializing Cyclic LINQ to SQL References with WCF

Rick Strahl observes in his WCF and segmented WSDL Files = Problems post of October 30, 2007 that the Web Service Definition Language (WSDL) documents generated by Windows Communication Foundation (WCF) Web services include a <wsdl:import namespace="someNamespace" location="uRLforWSDL"> element.

He found that several Web Service clients, such as Microsoft's SOAP Toolkit, some scripting clients and Adobe Flex's WSDL Import Wizard have problems reading WSDLs that use the wsdl:import feature. The .Net wsdl:import problem when parsing a wsdl made with websphere / java thread from the ASMX Web Services and XML Serialization forum indicates that .NET 2.0's Wsdl.exe implementation has a problem importing WSDL documents. (See especially the last post dated September 17, 2007.)

The Web Service Interoperability Council's WS-I Web Services Basic Profile 1.0 and later versions make specific reference to the wsdl:import feature, which is used to import other WSDL documents. Although all "modern" Web service clients should be able to handle WSDL imports, Rick contents that WCF should provide a "self-contained WSDL" option for maximum interoperability. I agree.

You can avoid the wsdl:import issue by creating .asmx services, which don't concatenate WSDL documents. However, Rick notes that you need to use WCF's DataContractSerializer to avoid serialization failures when the standard XmlContractSerializer encounters a circular reference (cycle).

I've been working on emulating the missing "mini connectionless DataContext" to enable a multi-tier, service-based architecture for LINQ to SQL. (See the "Emulating the Phantom Mini Connectionless DataContext" section of my LINQ to SQL Has No "Out-of-the-Box Multi-Tier Story!" post of October 18, 2007. LINQ to SQL marks public properties, including EntitySet (1:n) associations, as DataMembers but not EntityRef (m:1) associations to prevent circular references. It's my understanding that the DataContractSerializer requires setting the preserveObjectReferences parameter to true to support bidirectional serialization. Here's a response to the System.ServiceModel.CommunicationException - The socket connection was aborted... question in the WCF forum by Microsoft's Ed Pinto:

Enabling cyclic references requires you to create a DataContractSerializer and passing true in for the preserveObjectReferences parameter.  You can control this by deriving from the DataContractSerializerOperationBehavior, overriding CreateSerializer and applying said behavior to operations where you want this to happen.  Sowmy's got a great blog post on this including sample code: http://blogs.msdn.com/sowmy/archive/2006/03/26/561188.aspx.

Please be aware that there is no interop story for the preservation of object references.

The rumor is that Microsoft deliberately made it difficult to set this attribute value to discourage non-interoperable bidirectional serialization scenarios. Bidirectional serialization injects id and idref attributes into the message that don't appear in the schema.

Note: Rick points to Christian Weyer's workaround to generate unsegmented WSDL documents with WCF in his Improving WCF Interoperability: Flattening your WSDL post of May 10, 2007.

Technorati Tags: LINQ to SQL, LINQ, WCF, WSDL, Serialization, DataContract, DataContractSerializer

Avoid the LINQ to SQL Data Objects vs. Business Objects Debate

Dinesh Kulkarni's October 25, 2007 DTOs or Business Objects post explains the design philosophy behind LINQ to SQL without a direct answer to the question of "whether DLinq is for data objects or for business objects" or a definition that contrasts the two object types.

For starters, following are Wikipedia definitions:

In computer software, a Data Access Object (DAO) is an object that provides an abstract interface to some type of database or persistence mechanism, providing some specific operations without exposing details of the database. This isolation separates the concerns of what data accesses the application needs, in terms of domain-specific objects and data types (the public interface of the DAO), and how these needs can be satisfied with a specific DBMS, database schema, etc.

Business objects (BOs) are objects in an object-oriented computer program that abstract the entities in the domain that the program is written to represent. For example, an order entry program needs to work with concepts such as orders, line items, invoices and so on. Each of these may be represented by a business object.

Dinesh uses DTO (Data Transfer Object) in his title, so here's Martin Fowler's definition From Patterns of Enterprise Application Architecture:

An object that carries data between processes in order to reduce the number of method calls [across a connection].

The term DTO implies multiple tiers and crossing process boundaries. Dinesh has admitted that LINQ to SQL Has No "Out-of-the-Box Multi-Tier Story!", so the question resolves to DAOs or BOs. 

Dinesh says "An entity maps to a single table or view and closely matches it in shape," which implies DAO but that statement doesn't take into account associated or subobjects (EntitySets and EntityRefs) that smack of BOs. Assuming a combination of a well-designed relational schema that corresponds to a BO with correctly named properties, the distinction between BO and DAO probably isn't significant. DAOs deliver BOs from tables of relational database management systems (RDBMSs). If you're into layers, LINQ to SQL in a class library provides a data access layer (DAL) with or without access to the DataContext object. 

LINQ to SQL's essence is an object/relational mapping (O/RM) tool and persistence mechanism that offers the advantage of LINQ as a strongly-typed compiler-checked data query languate. Dinesh makes the point that, unlike some competing O/RMs, LINQ to SQL objects aren't "loaded with persistence-infrastructure like original state." Use of the graphic mapping tool's UI and code generation, as well as property value change notification, is optional. You can use plain-old CLR objects (POCOs) and gain whatever degree of persistence ignorance you think you need.

If you want to migrate low- to medium-complexity apps from table-oriented DataSets to modern object-oriented architecture and your back-end RDBMS (persistence store) is SQL Server 2000+ [Express], give LINQ to SQL a try. If you must use a different RDBMS, try a competing commercial or open-source O/RM or wait for Entity Framework and the appropriate data provider.

Update 10/26/2007: Jiri George Moudry's DB_LINQ (LINQ to MySQL, LINQ to PostgreSQL, and LINQ to Oracle) are in the alpha stage, and Kevin Kubasik has started a LINQ to SQLite project. See the "Prototype LINQ to SQL Clone DB_LINQ v0.11 Download" and "Work on LINQ to SQLite in Progress" topics of LINQ to SQL and Entity Framework Posts for 10/22/2007+ for more details and code download links.

Technorati Tags: LINQ to SQL, LINQ, Business Object, DataSets, Data Transfer Object, Data Access Object, O/RM, Object/Relational Mapping

OakLeaf Blog Gains PageRank Parity with Bigtime Sites

The OakLeaf Blog's front page has held onto a 5.0 Google PR (PageRank) for the past year or so and its Atom 1.0 feed now ranks 4.0.

But I was surprised to learn today from DailyBlogTips' Google Changing the PageRank Algorithm? post that the following A-List sites had the same 5.0 PR:

• Engadget
• Forbes Magazine
• Washington Post
• San Francisco Chronicle (sfgate.com)
• Stanford Daily
• Chicago Sun-Times

and that notables like

• Washington Times
• Charlotte Observer
• Seattle Times
• Autoblog
• Blog Herald
• ProBlogger
• Copyblogger
• Search Engine Journal
• Search Engine Roundtable
• The Unofficial Apple Weblog
• Joystiq

had dropped to 4.0 PR in what TechCrunch calls a Jihad on blog link farms. According to Search Engine Land, Google is penalizing sites for paid links, a.k.a, accepting payment for outbound links to less popular sites, or large-scale internal links between members of blog networks. 

Search Engine Land's What Is Google PageRank? A Guide For Searchers & Webmasters is a good introductory guide to PageRank, a term that Google claims as its trademark. Robert Scoble thinks Google PageRank is dead and has been for quite some time.

Technorati Tags: OakLeaf Blog, PageRank, Search Engine Optimization, SEO

LINQ to SQL and Entity Framework Posts for 10/22/2007+

Frans Bouma Reaches Part 7 of His LINQ to LLBLGen Pro Saga

Frans posted Developing Linq to LLBLGen Pro, part 7 on October 28, 2007. The article is an introspective essay on the perils of embarking on a project based on a third-party's new features (LINQ and expression trees) and painting yourself into a corner after many days of work. In this episode, the culprit is the Join operator, and Frans decides to modify the expression tree in place, as recommended by Matt Warren, and process JoinExpressions last. He says he doesn't know know yet how to handle GroupJoin, Convert and Quote methods from the System.Linq.Expressions namespace, but I'm sure he'll find out soon.

Added: 10/28/2007

Substitute an Enum to Supply Discriminator Values for LINQ to SQL Inheritance Mapping

David Hayden's LINQ To SQL Enum Support Example Using Discriminator Column and Inheritance Mapping post of October 28, 2007 demonstrates using an Enum instead of a Class to provide the discriminator column value when mapping a table-per-hierarchy entity inheritance. This article is a continuation of his LINQ To SQL Discriminator Column Example - Inheritance Mapping Tutorial of October 26, 2007. I spent a few minutes trying to model bitwise operations on a discriminator Enum decorated with <FlagsAttribute()>, but gave up in frustration. 

Added: 10/28/2007

GridLINQ Extends PLINQ Concept from Multicore to Grid Computing

Robert Anderson, CTO of Digipede Technologies, mentioned on October 25, 2007 that he was planning to "put together a sample for using LINQ on top of the Digipede APIs" but Matt Davey of Lab49 beat him to the punch with his GridLINQ and Digipede post of the same date. Matt had proposed a GridLINQ extension in this October 21, 2007 post:

GridLINQ would differ from PLINQ in that instead of only using a single computers CPU’s/Cores, GridLINQ would use Platform/DataSynapse/CCS to process the workload.

Dan Ciruli, Digipede's Director of Products, referred to Matt's work in his Why West Coast Grid? PowerShell and LINQ are two great reasons post on the same day.

Here's how Digipede describes it's product:

The Digipede Network™ is a distributed computing solution that delivers dramatically improved performance for real-world business applications. Built entirely on the Microsoft NET platform, it is radically easier to buy, install, learn, and use than other solutions.

Note: Robert Anderson was once a member of the Gilmore Gang podcasters who disbanded in late 2006. Digipede Technologies is located in Oakland's Grand Lake district, just a few blocks from my house in Crocker Highlands. 

Added: 10/27/2007

Kevin Hoffman to Take Continuous LINQ (CLinq) Public in January

According to Kevin's Continuous LINQ will be in Dr. Dobb's Journal in January post of October 26, 2007 Dr. Dobbs Journal's February 2008 edition will open the kimono on the source code for Continuous LINQ (CLinq). Kevin first broached the idea of dynamic LINQ queries that update the UI as changes occur (instead of polling by repeating the query) in his Dynamic, Observable LINQ Views post of July 30, 2007. His Continuous LINQ post of August 6, 2007 added more detail and coined the name Continuous LINQ, not to be confused with Oren Novotny's Streaming LINQ (SLinq). SLinq filters continuous streams of data, such as stock tickers.

CLinq is more akin to SQL Query Notifications without Service Broker for Windows Presentation Foundation clients. Kevin derives his custom ContinuousCollection class from WPF's ObservableCollection class, which "[r]epresents a dynamic data collection that provides notifications when items get added, removed, or when the whole list is refreshed" by implementing INotifyPropertyChanged and INotifyCollectionChanged.

Note: If you're a computer book author, don't miss Kevin's September 13, 2007 On Writing post that extends Julie's obsessive/compulsive analysis to programmer/writers.

Added: 10/27/2007

Julie Lerman's Entity Framework Article for CoDe Magazine Is Online

Julie has two articles in the November/December issue of CoDe Magazine:

• Introducing ADO.NET Entity Framework
• Coders Anonymous?

The latter piece is about programmers' notorious obsessive/compulsive tendencies.

Added: 10/27/2007

Zlatko Michailov Starts New Entity SQL Blog

Zlatko Michailov, Program Manager for the SQL Server Data Programmability Runtime team, started a new MSDN blog devoted to Entity SQL (eSQL) on October 24, 2007. His first three posts on the same day were:

• Query Complexity Factors describes in detailed outline format the three ages of eSQL queries: compilation, query view expansion, and native SQL generation.
• Entity SQL - Quick Reference shows simple examples of eSQL SELECT queries that return entities, values from functions, keys and references, m:1 and 1:n associations, paged rows, and grouped rows with aggregates.
• Entity SQL explains the purpose of the new dialect and claims "Entity SQL is a very rich functional language whose expressiveness supersedes conventional SQL dialects."

Zlatko previously posted to a personal MSDN blog. I'm surprised that Elisa Flasko or another ADO.NET Team member didn't announce Zlatko's blog migration. I'm subscribed.

Thanks to Julie Lerman for the heads up on the new blog.

Added: 10/27/2007

Video Clip of Updated Entity Data Model Designer

The ADO.NET team posted a link to a six-minute ScreenCam segment by Noam Ben-Ami, a program manager in the Data Programmability Tools Group, that demonstrates some of the new EDM Designer features that Entity Framework CTP 2 will include. The video was shown at 1005 Media's VSLive! Las Vegas conference October 15 - 18, 2007.

Compare the current version with an April 2007 prototype (that included glyphs) in this 3:30 Camtasia screen capture.

Added: 10/26/2007

Prototype LINQ to SQL Clone DB_LINQ v0.11 Download

If you're interested in LINQ to MySQL, LINQ to PostgreSQL, or LINQ to Oracle, Jiri George Moudry has posted the source code for the September 2007 version 0.11 of DB_LINQ to GoogleCode under the GNU LGPL. The DbLinq Project: Linq Provider for MySql, Oracle and PostgreSQL page provides project documentation.

Each version has it's own version of SqlMetal.exe—MySqlMetal.exe, PostgreSqlMetal.exe, and OracleMetal.exe—to produce a C# class file for the database, and a DbLinq.RdbmsName.Prototype.dll file to add to your C# 3.0 project.

Moudry promises a November 2007 release that includes partial support for MySQL and PostgreSQL stored procedures and functions.

Added: 10/26/2007

Work on LINQ to SQLite in Progress

Kevin Kubasik's SqlLite Linq Provider post of October 25, 2007 has an early implementation of Matt Warren's LINQ: Building an IQueryable Provider sample code for the popular SQLite database, now called System.Data.SQLite. Kevin describes Moudry's project as "bi-directional, change tracking, general awesome crazyness" in his article.

Added: 10/26/2007

Getting Started with the Entity Framework Sample Code

The ADO.NET Team posted GettingStarted.zip (8.8 MB) to CodePlex on October 17, 2007 but forgot to tell folks about it until today. The archive contains folders for C# and VB versions (GettingStarted_CS and GettingStarted_C#) of a simple ASP.NET project intended to track travel blogs. There's also a Getting Started with LINQ to Entities.mht page that describes the database schema and process for creating the MyTravelPostModel.edmx Entity Data Model.

Added: 10/26/2007

Jim Wooley Creates XHTML EMail Bodies with LINQ to XML

His Creating HTML emails using VB 9 and LINQ post of October 25, 2007 shows you how to use VB 9's XML literals feature to generate email bodies and send the messages with the System.Net.Mail.MailMessage class to an SMTP server. Cool!

Added: 10/25/2007

Adam Green Compares Ruby on Rails to VS 2008 and ActiveRecord to LINQ to SQL

In his Visual Studio 2008 vs Ruby on Rails post of October 23, 2007, Adam Green provides a detailed comparison of the features of Ruby on Rails and VS 2008's ASP.NET 3.5 with a detour that compares ActiveRecord with LINQ to SQL. It's clear from his commentary that DataSets don't rank very high on his list of favorite data access objects.

Added: 10/25/2007

Andrew Matthews Posts LINQ to RDF 0.3 and LinqToRdf Designer 0.3

LINQ to RDF "will provide a full-featured LINQ query provider for .NET using both local triple stores with Graph Matching and SPARQL queries on remote stores" under the New BSD License as an extension to Visual Studio 2008 Beta 2.

Andrew says in Announcing LinqToRdf 0.3 and LinqToRdf Designer 0.3 of October 25, 2007 that his new version offers:

• A new graphical designer to auto-generate C# entity models as well as N3 ontology specifications from UML-like designs.
• Support for SPARQL type casting
• Numerous bug fixes
• Better support for identity projections
• More SPARQL relational operators
• Latest versions of SemWeb & SPARQL Engine, incorporating recent bug fixes and enhancements of each of them

You can download version 0.3 of LINQ to RDF and the new graphic LinqToRdf Designer 0.3 from Google Code.

Added: 10/25/2007

Dinesh Kulkarni Avoids the Data Objects vs. Business Objects Debate

Dinesh's October 25, 2007 DTOs or Business Objects post explains the design philosophy behind LINQ to SQL without a direct answer to the question of "whether DLinq is for data objects or for business objects" or a definition that contrasts the two object types.

My conclusion in Avoiding the LINQ to SQL Data Objects vs. Business Objects Debate: DAOs deliver BOs from tables of relational database management systems (RDBMSs). Forget the distinction between data and business objects. If you want to migrate low- to medium-complexity apps from table-oriented DataSets to modern object-oriented architecture and your back-end RDBMS (persistence store) is SQL Server 2000+ [Express], give LINQ to SQL a try. If you must use a different RDBMS, use a competing commercial or open source O/RM or wait for Entity Framework and the appropriate data provider.

Note: According to an earlier post, Dinesh is leaving his program manager job on the LINQ to SQL team for another (undisclosed) position. Tim Mallalieu who hasn't posted to his blog since November 2006, will take over Dinesh's responsibilities. Here's Tim's bio from DevTeach 2007:

Tim Mallalieu is a Program Manager on the ADO.NET team in Microsoft. Before working in Microsoft, Tim worked as Principal Architect for Encore Development preceded by roles as architect or development manager in startups focused on frameworks for the Enterprise, E-commerce and Online Gaming spaces. Tim's focus over the last few years has primarily been on Application Frameworks, Enterprise Architecture and Model Driven Development.

Hopefully, Tim will increase his posting frequency after settling into his new job.

Added: 10/25/2007 Updated: 10/26/2007

LINQ to SQL Does Have a WinForms Databinding Story!

Despite the glaring lack of non-trivial databinding code examples from the LINQ to SQL team, this first-generation implementation has a much better story for databinding than that for multi-tier projects.

You can emulate DataSet databinding in a three-tier master-children-grandchildren DataGridView hierarchy and add, modify, and remove objects in the UI. My October 23, 2007 LINQ to SQL's WinForms Databinding Story post (updated October 24, 2007) shows you how. Emulating DataSet updates with ADO.NET 3.5's TableAdapterManager component takes more than a few lines of code, but it's not really difficult.

Added: 10/24/2007

Visual Basic 9.0 Specification Beta 2 Available

Paul Vick's October 23, 2007 Beta VB 9.0 language specification released... post reports that Beth Massi posted Beta 2 of the Beta 2 version of the Visual Basic Language specification on the VB Developer Center. This edition covers all new VB 9.0 features except

• XML Members
• XML Literals
• XML Namespaces

Grab your copy of this 1.7-MB, 328-page sure cure for insomnia today.

Added: 10/24/2007

New One-Hour LINQ, Entity Framework, and SQL Server 2008 Webcast

David Sceppa and Anthony Carrabino delivered on September 5, 2007 a 63-minute MSDN Webcast: Data Programmability and SQL Server 2008 (Level 200) that's now available on demand. According to the abstract the webcast provides "an overview of the new Microsoft data application development technologies and show how these technologies can benefit Microsoft SQL Server 2008 programmers."

David says the technologies are "LINQ (including LINQ to DataSet, LINQ to SQL, LINQ to Entities), the ADO.NET Entity Framework and SQL Server 2008. 

P.S. "Anthony owned and operated Vista Software, which created the popular VistaDB database engine for the Microsoft .NET Framework and the widely used Apollo Xbase data engine."

Added: 10/24/2007

Anders Norås Examines "Bare Naked LINQ"

To put the “this isn’t really LINQ for Java” and “cool, but this isn’t truly type-safe” issues to rest, Quaere author Anders Norås explains "how LINQ works" in his October 18, 2007 Bare naked LINQ post. Topics include

1. "LINQ to Objects" and extension methods
2. "LINQ to IQueryable" and LINQ to SQL
3. "Final Words" and type safety at compile time

Be sure to read the comments. Anders added a postscript based on one of them: Auto completion for LINQ and Quaere (take 3) provides a screencast that demonstrates autocompletion.

There's also an fledgling Quaere implementation for Java Persistence Architecture: Sneak preview: Quaere for JPA of October 2, 2007.

Added: 10/24/2007

Technorati Tags: LINQ to SQL, Databinding, Windows Forms, WinForms, DataGridView, BindingSouce, LINQ, VB 9.0, Quaere, LINQ to XML, SMTP, Ruby on Rails, ActiveRecord, Entity Framework, Entity Data Model, Entity SQL, eSQL, LINQ to MySQL, LINQ to PostgreSQL, Complex TypesLINQ to Oracle, LINQ to SQLite, Continuous LINQ, CLinq, GridLINQ, Digipede

LINQ to SQL's WinForms Databinding Story

Drag-and-drop databinding of LINQ to SQL DataContexts—like that for ADO.NET DataSets—is a "value added" Visual Studio feature that you see more often in demonstrations and conference sessions than in deployed production applications. The objective is to promote Visual Studio's "almost codeless" or "nearly codeless" databinding scenarios.

Editing online transaction processing (OLTP) data in DataGridView controls on Windows forms or ASP.NET GridView controls is a relatively uncommon practice. But databound controls that are more common in today's production applications, such as TextBoxes, ListBoxes, DropDownLists, CheckBoxes, RadioButtons and the like, have databinding characteristics similar to the DataGridView and GridView control. If one can demonstrate grid-based data display with full editing capabilities in DataGridView and with the exception of insertions in GridView controls, it follows—at least superficially—that other databound controls will behave as expected.

The Visual Studio team touts LINQ to SQL as an object-oriented substitute for the venerable DataSet but has been remarkably reluctant to demonstrate the databinding features of LINQ to SQL in Windows forms with master-child or master-child-grandchild DataGridViews. All examples I've seen that persist insertions to or deletions of LINQ to SQL objects do so with code rather than by adding or deleting DataGridView rows in the UI.

LINQ to SQL doesn't have an out-of the box multi-tier story but it does have a WinForms databinding story. The problem is that the LINQ to SQL team hasn't told it. VS 2008 Beta 2's online help provides only a trivial, single-grid databinding example. This post fills the gap with a full comparison of LINQ to SQL's fledgling databinding feature set for the presentation layer with that of the more mature typed DataSet.

The DataSet Approach to DataBinding Demos

The penultimate databinding demonstration is a three-level hierarchical set of Northwind Customer and related Order and Order_Detail objects displayed in three DataGridView controls bound to BindingSource components, which in turn bind to DataSet.TableAdapters. You drag the Customer node to a Windows form to autogenerate CustomersBindingNavigator, CustomersBindingSource, and CustomersDataGrid controls. Then you drag the FK_Orders_Customers relationship and FK_Order_Details_Orders relationship nodes to the form for editing the related Orders and Order_Details records.

VS 2008 (not .NET) adds the TableAdapterManager class, which autogenerates the sequence in which multiple DataSet insertion, modification, and deletion changes are applied to the underlying database. Online help for the TableAdapterManager class calls the technique hierarchical updates. Eliminating referential integrity conflicts requires only the following simple changes to the code generated by the Data Source Wizard:

1. Open the DataSet Designer's Relation dialog for each relationship, change the Foreign Key Only to the Both Relation and Foreign Key Constraints option, and change the Update Rule and Delete Rule from None to Cascade.*
2. Invert the sequence of the FormName_Load event handler's TableAdapter.Fill() methods so that the TableAdapters fill from the top of the hierarchy down (Customers, Orders, Order_Details).
3. Add BindingSource.EndEdit() methods to the BindingNavigator_SaveItem() event handler for the second and later foreign-key nodes you dragged to the form. (This step doesn't relate directly to hierarchical table binding.)

* You might find that the Beta 2 DataSet Designer's UI doesn't sync with the underlying changes to the underlying DataSetName.Designer.vb or .cs file's values for the rules.

Following is the source code required for these changes, plus an added Reload button for convenience in verifying database updates:

The upshot of this improved hierarchical updates scenario is that creating and testing a Windows form for a three-level hierarchy requires adding only four simple lines of code and setting six property values in the DataSet Designer. This usually takes less than half an hour. The Windows form handles hierarchical insertions, modifications, and deletions at all levels (Customers, Orders, and Order_Details) correctly.

ADO.NET 3.5's DataSet Designer also lets you autogenerate TableAdapter and typed DataSet code in different projects. This lets you protect the privileged information about the database and its connection string(s) from access by the presentation layer, which connects to TableAdapters only.

Patterns: Typed and untyped DataSets implement the Table Module and Unit of Work patterns and can be used as Data Transfer Objects. See Data Transfer Object, Implementing Data Transfer Object in .NET with a DataSet and Implementing Data Transfer Object in .NET with a Typed DataSet from Microsoft patterns & practices. Encapsulating TableAdapter and DataSet code in different projects aids in maintaining Separation of Concerns (SoC).

Hierarchical Updates to LINQ to SQL Entities 

LINQ to SQL appears to emulate DataSet databinding but the behavior of editing operations differs, especially when deleting child records. Materializing the result of LINQ to SQL queries by invoking the DataContext.Table<TEntity>.ToList() method makes substantial changes to insertion and deletion operations.

A Partial Demonstration of LINQ to SQL DataBinding, Presumably with VS 2008 Beta 2

Code examples for and demonstrations of entity insertions, updates, or deletions with LINQ to SQL seldom involve more than a single table and only a few utilize ADO.NET databinding. An exception is Young Joo's LINQ to SQL and the O/R Designer in VS 2008 Channel9 video interview with Beth Massi of August 27, 2007.

The timing of Young Joo's coverage of databinding a two-level hierarchy is as follows:

1. 16:00 He adds a new Data Source from a DataContext's Customer entity.
2. 17:00 He explains the the association between the Customer entity and Customer.Orders property.
3. 18:00 Mr. Joo drags the Customer node to the form to add the CustomerBindingSource, CustomerBindingNavigator, and CustomerDataGridView controls.
4. 18:10 He Drags the Customer.Orders node to the form to add the OrdersBindingSource and OrdersDataGridView controls.
5. 18:48 Mr. Joo admits that "you do have to write a couple of lines of code ... to instantiate your DataContext" and set the CustomerBindingSource.DataSource property value to the new NorthwinDataContext.Customer Table<TEntity> instance. "Just two lines of code."
6. 19:40 He assigns the DataContext.Log property to Console.Out to demonstrate the T-SQL statements that lazy-load the Order instances as he scrolls the CustomerDataGridView.
7. 20:45 He mentions that if had an Order Details grid, it would lazy load as he selects the associated order row.
8. 21:05 "One cool thing about LINQ to SQL is that it already takes care of all the hierarchical updates for you." (Emphasis added)
9. 21:12 He enables the Save Data button and says, "All you have to do to save the changes back to the database is to call the DataContext.SubmitChanges() method. As you make changes to your object, the DataContext will keep track of all the changes that you made, whether you've updated, inserted, [or deleted]."
10. 21:45 "It will order them in such a way that you don't get referential integrity errors." (Emphasized content rephrased to represent intent.)
11. 21:25 "In the LINQ to SQL case here, I only wrote one line of code: DataContext.SubmitChanges() ... you don't have to worry about anything else; it will take care of everything." (Emphasis added.)
12. 22:40 Mr. Joo demonstrates a "simple update case" by changing cell values in the two grids and shows the log entries in the Output window.

Mr. Joo must be using a different version of LINQ to SQL than my copy of VS 2008 Professional Edition Beta 2. Using Mr. Joo's default code, I encounter the following referential integrity and other other errors when deleting rows from the grid and submitting changes: 

1. Attempts to delete a Customer instance by deleting a CustomerDataGridView  row throw "The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers" exceptions, contrary to preceding items 8 and 10.
2. Attempts to delete an Order_Details EntitySet member by deleting an Order_DetailsDataGridView row throw an "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail. However, one of the relationship's foreign keys (Order_Detail.OrderID) cannot be set to null," which contradicts preceding item 11. This error is fatal; the Order_Detail is deleted from the DataContext but not from the persistence store. You must reload the data before you can submit any other changes.

It's unfortunate that Mr. Joo didn't attempt to demonstrate deleting a row from the CustomerDataGridView before asserting that LINQ to SQL "takes care of all the hierarchical updates for you" and "you don't get referential integrity errors."

Oddly, removing an Order and its Order_Detail(s) instances by deleting an OrderDataGridView row succeeds. The T-SQL emitted for deletion sets the CustomerID to NULL with a batch like:

UPDATE [dbo].[Orders]
SET [CustomerID] = @p13
WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11123]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/22/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/5/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p13: Input String (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

but leaves the orphaned records in the persistence store's Orders and Order Details tables. This is a truly curious approach, which probably works because the string CustomerID foreign key is nullable, whereas LINQ to SQL treats the Order Details record's integer OrderID foreign key as not nullable and throws an exception.

The DataSet Designer avoids these pitfalls by providing the Relation dialog with the ability to select the Update and Delete Rules as None, SetNull, SetDefault, or Cascade. (However, selection dropdowns in the Beta 2 version of the Relation dialog always show None and don't display the actual current setting.)

Bug Report: I consider leaving orphaned records in the persistence store to be a non-blocking bug. The Microsoft Connect report is 306378, Deleting a LINQ to SQL EntitySet Member in a Bound DataGridView Orphans Table Records (October 23, 2007).

Update October 30, 2007: Microsoft still hadn't acknowledged my week-old bug report.

Workarounds for the Two Deletion Issues

Following are event handlers that execute LINQ to SQL queries to remove 1) Order and Order_Detail and 2) Order_Detail entities:

Here's the typical T-SQL emitted, which includes a query to retrieve the Customer entity from the persistence store:

SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Order Details] WHERE ([OrderID] = @p0) AND ([ProductID] = @p1) AND ([UnitPrice] = @p2) AND ([Quantity] = @p3) AND ([Discount] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11126]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Currency (Size = 0; Prec = 19; Scale = 4) [20.0000]
-- @p3: Input Int16 (Size = 0; Prec = 0; Scale = 0) [24]
-- @p4: Input Single (Size = 0; Prec = 0; Scale = 0) [0.1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Orders] WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11126]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/22/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/5/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

An alternative workaround, fixing the "one of the relationship's foreign keys (Order_Detail.OrderID) cannot be set to null'." exception, isn't easy as noted in my Cascade Deletion Problem with LINQ to SQL Beta 2 post.

Adding a DeleteOnNull="true" attribute after the IsForeignKey="true" attribute of the <Association Name="CustomerOrder ... /> and <Association Name="OrderOrder_Detail" ... /> elements as described in Beth Massi's LINQ to SQL and One-To-Many Relationships post of October 2, 2007 throws an "Error DBML1055: The  DeleteOnNull attribute of the Association element 'Customer_Order' can only be true for singleton association members mapped to non-nullable foreign key columns." The Yet another DeleteOnNull problem thread in the LINQ Project General forum illustrates other frustrations with the DeleteOnNull attribute.

Patterns: According to Ian Cooper in his seminal Being Ignorant with LINQ to SQL post of June 10, 2007:

LINQ to SQL uses the Data Mapper architectural pattern. ... IQueryable resembles a Query Object; an IQueryable<T> also resembles what [Eric] Evans calls a Specification in Domain Driven Design in its composability. Collectively the DataContext and Table<T> ... do Metadata Mapping to handle loading and saving of objects from the store. DataContext implements the Unit of Work pattern; LINQ provides an Identity Map, a consequence of providing a unit of work, and also provides support for Lazy Loading. LINQ supports Foreign Key Mapping through the EntitySet and EntityRef collection types. LINQ to SQL also supports ... Single Table Inheritance as opposed to a table per type (Class Table Inheritance) or a table per concrete type (Concrete Table Inheritance).

AntiPatterns: This two-tier client/server configuration doesn't maintain SoC because the presentation layer's BindingSource components and code-behind connect directly to the DataContext, which exposes privileged database information and provides direct access to the connection string.

Caching DataContext.Table<TEntity> Data to Minimize Roundtrips and Separate Concerns

It's a common practice to invoke the the DataContext.Table<Customer>.ToList() method to create a concrete List<Customer> instance (cache) and eliminate the need for a persistence store roundtrip for each LINQ to SQL query against the Table<TEntity>. The capability to pass List<T> instances between layers and serialize them for crossing process boundaries (tiers) lets you isolate the DataContext from the business logic and presentation layers.

Note: Lazy loading Orders and Order_Details EntitySets requires two round-trips to the persistance store for each row you select in the CustomerDataGridView. You can minimize the resources consumed by EntitySets by preloading them with the method described in my Save Server Round Trips by Preloading LINQ to SQL EntityRefs post of October 1, 2007. Stored procedures can use TOP n ... ORDER BY OrderDate DESC statements or otherwise restrict the number of EntitySet members.

Following are the problems you'll encounter when you bind the UI's BindingSources to concrete List<Customer> cache and its EntitySets:

1. Adding object(s) in the CustomerDataGridView and invoking DataContext.SubmitChanges() doesn't persist the change(s) to the underlying data store because the List<Customer> is a different data type than Table<Customer>. Therefore, the IdentityManager allows multiple copies of the instances in memory.
2. Attempts to delete a Customer instance by deleting a CustomerDataGridView row fail silently for the same reason.
3. Attempts to delete an Order_Detail instance by deleting an Order_DetailDataGridView row fail with the same "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail" exception. (Same as for the default Table<TEntity> binding.)
4. If the Order has more than one Order_Details row, attempting to delete one Order_Detail instance causes attempts to delete an Order to throw the same same "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail" exception.
5. The CustomerDataGridView doesn't support sorting (because List<T> doesn't implement IBindingList<T>) but sorting is enabled for the Orders and Order_Details EntitySets. (My Support Sorting and Filtering for List<T> Collections post of September 7, 2007 shows you how to add these features to a List<T> with a class library.)

The workaround for problems 1 through 4 is to process instances added or deleted in the UI in List<Customer> collections (lstCustomer and delCustomers):

1. Populate a form-scoped List<Customer> collection (delCustomers) with a DataContext.Table<Customer>.ToList() invocation.
2. Add  a form-scoped List<Customer> collection to hold instances of deleted List<Customer> items, which you add in the CustomerDataGridView_UserDeletingRow() event handler. (A deletion confirmation message box is a useful addition to this event handler.) 
3. In the event handler for saving changes, Invoke the DataContext.Table<Customer>.Add(entity) method to add each new Customer item.
4. For each deleted Customer item, invoke the DataContext.Table<Customer>.Remove(entity) method on its Order_Details and Orders EntitySets and finally on the Customer item. 
5. Call the DataContext.SubmitChanges() method 
6. Clear the delCustomers collection.

Here's the code to process the changes by adding or removing their instances from the DataContext:

The preceding example needs additional generic collections and code to save original and modified values for updated items if your DataContext runs out-of-process. One approach is to use a "maxi connectionless DataContext", as described in my LINQ to SQL Has No "Out-of-the-Box Multi-Tier Story!" post of October 18, 2007.

An alternative is to create a deep clone (which includes the EntitySets) of lstCustomer and a modCustomers list to store copies of updated records and call the DataContext.Table<Customer>.AttachAll(modCustomers, origCustomers) method, where origCustomers is the subset of lstCustomer that corresponds to modCustomers items. If you designate a timestamp field for concurrency conflict management, you don't need origCustomers.

Removing references to the DataContext in the presentation layer requires implementing AddCustomers(newCustomers), ModifyCustomers(modCustomers, origCustomers) and RemoveCustomers(delCustomers) methods in the DataContext class library. I'll cover this approach and providing default foreign-key and other property values for new entities in a later post.

Updated 10/24/2007: Added code and text to "Caching DataContext.Table<TEntity> Data to Minimize Roundtrips and Separate Concerns" topic.

Applicability of This Approach to the Entity Framework

The Entity Framework's ObjectContext is quite similar to LINQ to SQL's DataContext, so much of this code and that of later posts on this and related databinding topics should apply to the EF and LINQ to Entities. I'll provide some examples in future posts.

Updated 10/25/2007: Added Entity Framework applicability as an afterthought.

Technorati Tags: LINQ to SQL, Databinding, LINQ, DataSet, DataGridView, BindingSource, Table Model, Data Transfer Object, Query Object, Metadata Mapping, Unit of Work, Identity Map, Lazy Loading, Foreign Key Mapping, Single Table Inheritance, Doman-Driven Design, Test-Driven Development, Entity Framework, LINQ to Entities, Entity Data Model

Why I Use Desktop Apps, Part II

Microsoft Connect wasn't down, but it didn't like requests from my Windows XP SP2 virtual machine today. Here's a link to Part I.

Notice that the suggestion to "[c]lick the Contact Us link" doesn't work when the site throws "unexpected errors."

Technorati Tags: OakLeaf Blog, Desktop Apps, Online Apps

LINQ to SQL Has No "Out-of-the-Box Multi-Tier Story!"

The LINQ to SQL Team bites the bullet: Dinesh Kulkarni admits in his October 15, 2007 LINQ to SQL: What is NOT in RTM (V1) post that LINQ to SQL v1 will be missing (at least) the following features:

1. Support for other databases with or without a public provider model.
2. Out-of-the-box multi-tier story.
3. Handling schema changes.
4. Support for specific mappings (value types and m:n relationships with no relation table.)
5. Identity between designer, SqlMetal and run-time (external mapping file and SSC support in designer)
6. LINQ to SQL in compact framework.

[I've shortened and edited the feature descriptions for emphasis and further emphasized point 2.]

Dinesh says the missing features are ranked by "frequency/strength of the feedback and consistency with what LINQ to SQL was designed for." In my view, LINQ to SQL was never intended to support databases other than SQL Server (including Compact Edition); its expression trees are connected at the hip to Transact-SQL. This makes lack of an "Out-of-the-box multi-tier story" my number one missing feature of LINQ to SQL's v1 release.

The Missing Multi-Tier and Remoting Story

The problem I see is that LINQ to SQL has no multi-tier or remoting story. Searching for 'tier' in the LINQ Project General Forum today returned 172 hits. Most questions concerned the following three- or four-tier scenario:

1. Request with a single message from the UI tier a set of related business objects for editing from a Windows Communication Foundation (WCF) service client that uses HTTP[S] or TCP-IP as the transport.
2. Supply the business objects in a single message from a business services tier (BSL) that in turn connects to a LINQ to SQL persistence layer (PL, a.k.a. Data Access Layer or DAL). The BSL provides authentication and authorization services; WCF handles digital signing, encryption, and reliable messaging operations.
3. Add, edit, and/or remove one or more root and/or related business objects in the UI, which is most commonly an ASP.NET Web page.
4. Send all modified root and related business objects to the BSL in as few messages as possible, validate the changes in the BSL, update the local business objects and persist the updated objects in the PL, which reports concurrency conflicts to the BSL (and UI, if necessary) for resolution.

In some questions, the BSL and DAL/PL are combined in a single layer.

If you replace the term business objects with DataTables from a DataSet, the preceding scenario is in common use today with .NET 2.0 Web services. .NET 3.0's WCF simplifies creating and managing services that use HTTP and TCP-IP transports for Internet services and intranet remoting. As noted in my LINQ to SQL and Entity Framework XML Serialization Issues with WCF - Part 1 post of October 9, 2007, the DataContractSerializer serializes EntitySets by default and EntityRefs with a relatively simple patch.

The LINQ Project General Forum's Update existing disconnected objects (DLinq) thread that started February 1, 2007 is typical of requests for information on how to implement step 4 of the preceding scenario. Here's Matt Warren's February 3, 2007 response to Zoinky's question of February 2:

The usage pattern you are describing is not supported by the feature set currently available.  The only scenario with 'disconnected' objects supported so far requires you to re-attach your objects and play-back the changes against them. This is intended for a multi-tier scenario where you are responsible for serializing the object's data to the client tier or browser and back again along with change information.  More DLINQ features in this area are still to come.

The scenario you are describing sounds like it would be better served as a two-tier model using normal attached objects databound to UI.

There are four problems with the preceding answer:

1. Re-attaching is "intended for a multi-tier scenario", but Zoinky's typical multi-tier scenario isn't supported. Now there's no support for multi-tier scenarios.
2. Playing back changes implies sending an individual message for each change, which results in an CRUDy (chatty) service, which is an anti-pattern.
3. More LINQ to SQL "features in this area" didn't come, as noted in the next topic.
4. If your feature doesn't support three or more tiers, suggesting that two-tier would be better won't suffice. It's obvious that Zoinky and others requesting a solution to three-tier issues would use two tiers if it suited their requirements.

After complaints about lack of a three-tier solution, Matt concluded the thread on April 12, 2007 with the following:

The reconnecting API's you have available to you at this time do not support the scenario you guys are discussing. There is more work coming that makes 3-tier scenario's easier. The re-attach and playback API's are included because they are the bare minimum required to make any reconnecting work at all, however cumbersome it may seem at this time. There is an upcoming feature that automatically databinds LINQ to SQL objects directly to webforms that use these API's to communicate changes back into the DataContext. 

There is also a technology in the works that automatically serializes LINQ to SQL objects to other tiers, change tracks and data binds the objects for you on that tier and serializes the objects back, solving the cyclic reference serialization problem and the indescribable schema problem for objects with change sets; which seems to match what many of you are describing.

Here's my take on this answer:  

1. The "upcoming feature that automatically databinds LINQ to SQL objects directly to webforms that use these API's to communicate changes back into the DataContext" is ASP.NET 3.5's LinqDataSource component, which must connect to a local DataContext and dictates client/server (two-tier) architecture.
2. I haven't seen a "playback API." The above answer is the only LINQ-related original document returned by a Google search on '"playback API" LINQ -MIDI'.
3. We're still waiting for the "technology in the works that automatically serializes LINQ to SQL objects to other tiers," which turned out to be the phantom "mini connectionless DataContext" described in my Changes Coming to LINQ to SQL post of May 15, 2007.

And here's Matt's announcement of the mini connectionless DataContext's demise in LINQ to SQL v1 from the Forum's LINQ: Next CTP thread:

No, this feature will not be in the first release of LINQ to SQL. (June 19, 2007)

It is only missing because of lack of time.  When I mentioned it, I was talking about our forward thinking and a sample I'm trying to put together. (June 27, 2007)

Needless to say, no sample has been forthcoming as of mid-October.

Considering the Microsoft Developer Division's emphasis on tiered data-access architectures, initially with Web services and now with WCF, "no out-of-the-box multi-tier support" by what promised to be the most widely used LINQ implementation boggles the imagination.

Emulating the Phantom Mini Connectionless DataContext

The "Mike Taulty Implements an Unbound DataContext in a WCF Client" topic of my LINQ and Entity Framework Posts for 10/5/2007+ post points to an article that describes how to write your own connectionless DataContext class and related code to support disconnected addition, modification, and deletion of a root object without child objects. Mike's ClientSideContext class wraps a standard DataContext that has an empty connection string and uses the DataContext.GetTable() method to deliver Table<TEntity> objects without sending T-SQL queries to a non-existent persistence store. The client and server apps share a common NorthwindDataContext class (called DataTypes) generated by SqlMetal.exe.

Note: The client's DataContext class copy includes persistence-related [Table ...] and  [Column ...] attributes, as well as connection string information that could be removed to provide persistence ignorance.

Supporting Associated Objects

I modified Mike's approach by using the O/R Designer to create the Northwind.dbml and related designer files for Customer, Order, and Order_Detail entities, created a Data Source from the Customer entity, and dragged-and-dropped DataGridView controls with BindingSource components bound to the the Customer, Customer.Orders, and Order.Order_Details entity and EntitySets. (The DataContext class doesn't serialize EntityRefs because WCF's DataContractSerializer doesn't support cyclic references unless you add a source code patch.)

Note: I use the parent/child/grandchild grids and a new Customer/Order/Order_Details item for almost all Windows form test harnesses. Failing to reproduce the archtypical DataSet demo with hierarchical data sources and associated EntitySets and EntityRefs has left several LINQ to SQL bugs undetected by the LINQ to SQL team's QA folks. Here's a screen capture of the ContextClient.exe test harness that resembles most others I've built:

The time shown in the preceding capture is that for the client to send a SOAP response message (GetUSCustomers.xml, 177 KB) for the 14 US Customer objects (including BOGUS), with include their associated Order and Order_Detail object instances.

The following capture shows the ContextService.exe harness with part of the T-SQL batch for deleting the BOGUS Customer and it's dependent Order and Order_Detail objects:

The full T-SQL batch for BOGUS order deletion with provision for referential integrity and value-based concurrency management is:

Method: DeleteCustomers

DELETE FROM [dbo].[Order Details] WHERE ([OrderID] = @p0) AND ([ProductID] = @p1) AND ([UnitPrice] = @p2) AND ([Quantity] = @p3) AND ([Discount] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [12278]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p3: Input Int16 (Size = 0; Prec = 0; Scale = 0) [12]
-- @p4: Input Single (Size = 0; Prec = 0; Scale = 0) [0.05]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Order Details] WHERE ([OrderID] = @p0) AND ([ProductID] = @p1) AND ([UnitPrice] = @p2) AND ([Quantity] = @p3) AND ([Discount] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [12278]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Currency (Size = 0; Prec = 19; Scale = 4) [20.0000]
-- @p3: Input Int16 (Size = 0; Prec = 0; Scale = 0) [24]
-- @p4: Input Single (Size = 0; Prec = 0; Scale = 0) [0.1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Orders] WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [12278]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/18/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/1/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Customers] WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2) AND ([ContactTitle] = @p3) AND ([Address] = @p4) AND ([City] = @p5) AND ([Region] = @p6) AND ([PostalCode] = @p7) AND ([Country] = @p8) AND ([Phone] = @p9) AND ([Fax] = @p10)
-- @p0: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p1: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p2: Input String (Size = 9; Prec = 0; Scale = 0) [Joe Bogus]
-- @p3: Input String (Size = 10; Prec = 0; Scale = 0) [Proprietor]
-- @p4: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p5: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p6: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p7: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p8: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p9: Input String (Size = 14; Prec = 0; Scale = 0) [(510) 555-1212]
-- @p10: Input String (Size = 14; Prec = 0; Scale = 0) [(510) 555-1213]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

Elapsed time: 0.550 seconds.

The corresponding SOAP message from the WCF Service Trace Viewer is 3,242 bytes; click the link to view the DeleteBogus.xml trace in IE 6/7. Notice that the <before ...> node includes a serialized <a:Orders> node and the <after ...> node doesn't. You must Insert() an object before you can Remove() it. In this case the original and current values are swapped so that the before node has the original values for Order and Order_Detail EntitySets and it wasn't necessary to execute the code to add them.

Adding Missing Orders and Order_Details EntitySets to Original Values 

I'm still hoping for an explanation of the lack of support by the default DataContext's GetOriginalEntityState(modifiedEntity) method for EntitySets. Here's a copy of Matt Warren's comment to the "LINQ to SQL's GetOriginalEntityState() Method Has Serious Bug" topic of my LINQ and Entity Framework Posts for 10/15+ post:

Roger, its not possible for GetOriginalEntityState to return copies of entities with EntityRef's and EntitySet's set. These associations are normally bi-directional, so only one entity instance can ever refer to another without duplicating the entire graph. The purpose of GetOriginalEntityState is to give back a copy of the entity with only the data fields set.

The upshot of this message is that the GetOriginalEntityState() method returns what amounts to a shallow clone of the desired entity when what's needed is the equivalent of a deep clone. (For more about this topic, see An insight into cloning objects in .NET.)

My current workaround for this problem is to create List<Customer> lists from two individual SOAP requests to the client and not attach the second list to the ClientSideContext. (I'm investigating more efficient techniques, such as creating a deep clone of the original EntitySets on loading data.)

Here's the client-side code that attaches the first (customers) list to the ClientSideContext to make EntitySets accessible for editing or deletion:

ctx = new ClientSideContext();
foreach (Customer c in customers)
{
    ctx.Attach(c);
    // Attach the Orders entity from its EntitySet
    // EntitySet objects don't have an iterator
    Order ord = null;
    for (int i = 0; i < c.Orders.Count; i++)
    {
        ord = c.Orders[i];
        ctx.Attach(ord);
        // Attach the Order_Details entity from its EntitySet      
        for (int j = 0; j < ord.Order_Details.Count; j++)
        {
            ctx.Attach(ord.Order_Details[j]);
        }
    }
}

Note: The preceding is a modification of Rick Strahl's approach in his Complex Detached Entities in LINQ to SQL - more Nightmares post of October 1, 2007.

The SOAP request message to update the BOGUS Customer with a minor edit is 5,043 bytes in the WCF Service Trace Viewer; click the link to view the UpdateBogus.xml trace, which has only a change to the <ContactName> element value, in IE 6/7. Notice that both the <after ...> and <before ...>  nodes include serialized <a:Orders> nodes. (Code adds the <a:Orders> nodes to the <before ...> node immediately prior to sending the message.) The current and original values are required for comparison when returning the entire Customer object to the service for updates.

Here's the code to add the original EntityRef values from the origCusts List<Customer> that result in the message's <a:Orders> subnodes of the <before ...> (original values) node:

// Modify Customer objects, including EntitySets
var modified = ctx.GetModified<Customer>();
if (ctx.GetModified<Customer>().Current.Count > 0)
{
    foreach (Customer c in modified.Originals)
    {
        if (c.Orders.Count == 0)
        {
            foreach (Customer o in origCusts)
            {
                if (o.CustomerID == c.CustomerID)
                // Add EntityRefs to modified.Originals
                {
                    // Adding items removes them from origCusts.Orders
                    int ordCount = o.Orders.Count;
                    for (int i = 0; i < ordCount; i++)
                    {
                        c.Orders.Add(o.Orders[0]);
                    }
                    break;
                }
            }
        }
    }
    proxy.UpdateCustomers(modified.Current, modified.Originals);
}

Comparing the elements of the <after ...> and <before ...> nodes' <a:Orders> subnodes of UpdateBogusOrders.xml proves that the preceding code works. The <after ...> node reflects DataGridView changes to the <ContactName>, <EmployeeID>, <ShippedDate>, <ProductID> and <ShippedDate> element values. It's impossible to prove that the service updates the order correctly because the blocking 295402 Incorrect Update Behavior - Attaching an Entity after Calling a Relationship Property bug reported on 8/27/2007 causes EntityRef updates to insert rather than update orders. (The inserted Order and Order Detail records reflect the DataGridView edits.)

As is the case with two-tier (client/server) implementations, only object updates in DataGridViews work as expected, but edits to EntityRef members don't mark the root object dirty (reported as bug ID 305828, Change to LINQ to SQL EntitySet Member Doesn't Mark Root Object Dirty.) You also must write code to handle adding and removing EntityRef members, as well as a hack to add the root object to the lists for updates.

Updated 10/19/2007 1300 PST: Clarified, added code and provided an additional XML document link to the preceding section.

Future Work Required

For this initial post, I've implemented Mike's proxy.InsertCustomers(ctx.GetInserted<Customer>()), proxy.DeleteCustomers(deleted.Current, deleted.Originals) and proxy.UpdateCustomers(modified.Current, modified.Originals) methods for one or more Customer objects with two levels of sub-objects. This demonstrates that there is a multi-tier story for LINQ to SQL but it's unlikely to win a Pulitzer prize. In fact, it's a downright discouraging story.

The next steps are to investigate tradeoffs between CRUDy (chatty) proxy.InsertOrders(), proxy.DeleteOrders(), proxy.UpdateOrders(), proxy.InsertOrder_Details(), proxy.DeleteOrder_Details(), and proxy.UpdateOrder_Details() methods versus using chunky proxy.UpdateCustomers() to handle changes to child objects. Tests with ASP.NET and the LinqDataSource also are in the works. (The LinqDataSource requires a DataContext as its data source.)

Here's Hoping that the Entity Framework Team Doesn't Fall into this Trap

Mike Taulty discusses strangeness in Entity Framework deletions in his October 3, 2007 LINQ to Entities - Deleting and August 27, 2007 Entity Framework - Object Services Level. Deleting and ObjectStateManager posts. LINQ to SQL has similar oddities with removing object instances from the persistence store when deleting rows in bound DataGridViews, as described in my 10/23/2007 The LINQ to SQL DataBinding Story post. (Coming soon.)

He also compares the problem of managing optimistic concurrency with the Entity Framework and LINQ to SQL in his September 19, 2007 Disconnected LINQ to Entities post. Hopefully, Mike will soon try a similar hack to create a "maxi connectionless ObjectContext."

Updated 10/23/2007 0930 PST: Added and updated content to this section.

Technorati Tags: LINQ to SQL, LINQ, DataContext, Multi-Tier, n-Tier, WCF, Windows Communication Foundation