LINQ and Entity Framework Posts for 2/25/2008+

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

New "Model Domain Objects with the Entity Framework" Article in Visual Studio Magazine for March 2008

Visual Studio Magazine's March 2008 cover story is my "Model Domain Objects with the Entity Framework" article. Here's the deck:

Microsoft's ADO.NET Team readies Entity Framework and Tools 1.0 for release as a VS 2008 add-in with enterprise-level features that LINQ to SQL doesn't offer—domain object modeling, flexible inheritance techniques, multiple database vendors, and do-it-yourself n-tier deployment.

The story covers new and modified EF features since my "Objectify Data with ADO.NET vNext" article in the October 2006 issue.

A sidebar describes Danny Simmons' EntityBag (Perseus) project for "do-it-yourself n-tier deployment."

Erratum: Entity SQL does have a JOIN command but it's used to join unrelated entities only. The eSQL NAVIGATE command is used with associations and navigation properties, which represent INNER JOINs at the conceptual level.

Added: 3/1/2008

ADO.NET Data Services (Astoria) Becomes the Framework for All Window Live Services

In honor of the announcement by Pablo Castro that "all Windows Live services can be accessed using AtomPub" ADO.NET Data Services has been rechristened the "ADO.NET Data Services Framework." [Emphasis added.] This term was occasionally used in documentation for Astoria's December 2007 CTP. Here's the official story from MIX08:

Accessing Windows Live Services via AtomPub

Friday, March 7 8:30 AM - 9:45 AM, Delfino 4105
Speaker(s): Pablo Castro
Audience(s): Technical
Session Type: Breakout

Come learn how Microsoft is moving towards a unified approach for accessing online services based on AtomPub. We cover how all Windows Live services can be accessed using AtomPub as well as how integrated Microsoft Visual Studio support including ADO.NET Data Services Framework (aka Project "Astoria") allows anyone to easily develop, deploy, and access online services using AtomPub. [Emphasis added and typo fixed.]

Whaddya bet that everyone continues to call it "Astoria?"

Added: 3/1/2008

Dan Wahlin Posts Layered LINQ to SQL Sample Application

Dan's Building an N-Layer ASP.NET Application with LINQ, Lambdas and Stored Procedures (Updated) post of February 28, 2008 provides a description of a four-layer (presentation layer, business layer, data layer and model layer) ASP.NET/LINQ to SQL demo application that lets you choose between LINQ, lambda functions, and stored procedures for queries. Dan databinds his ListView and GridView controls to an ObjectDataSource (rather than a LinqDataSource), apparently because of the multiple query types he uses.

The demo project, which is available for download from a link on his above post, is for his presentation at ASP.NET Connections Orlando in April:

ADX211: Building N-Tier Applications with LINQ
Dan Wahlin
Creating a modular and maintainable application architecture is always a key part of the application development life cycle. In this session you'll learn how to build N-Tier/N-Layer applications that leverage Language Integrated Query (LINQ) to minimize development time while still maintaining module design practices. Topics covered include using the Visual Studio 2008 LINQ to SQL designer, using data context objects, and performing different types of LINQ queries.

Dan's earlier LINQ and Lambdas and Sprocs....Oh My! post explains why he uses the three query types.

Note that the demo is N-Layer, not N-Tier.

Added: 2/29/2008

Doug Rothaus and Avner Aharoni Start LINQ to XML VB Cookbook Series

Doug says in the VB Team's VB XML Cookbook, Recipe 1: XML Transformations using XML Literals (Doug Rothaus) post of February 29, 2008:

I was working on a blog entry about replacing XSLT transforms with Visual Basic XML Literals. As the entry progressed, I noticed that it was really, really long. So, Avner Aharoni and I talked things over and we decided to break it up into multiple entries and start a series, much like the LINQ Cookbook that was started a few months back.

This first episode shows you how to replace <xsl:copy-of>, <xsl:for-each>, <xsl:template>, <xsl:if>, <xsl:value-of>, and <xsl:attribute> elements as well as use XML Axis Properties in place of XPath.

As I said in a comment to this post:

Scott Guthrie doesn't worry about long posts. His First Look at Using Expression Blend with Silverlight 2 post of February 28, 2008 was about 15 feet long. There's no question about the popularity of Scott's posts.

These linkblog posts sometime reach close to that length, but mine don't come anywhere near the popularity of his tutorials.

Added: 2/29/2008

Christian Kleinerman's Video Extols New Developer Features in SQL Server 2008

This might seem off-topic but Channel9's Christian Kleinerman: SQL Server 2008 for Developers video segment (posted February 29, 2008) covers some topics of specific interest to LINQ to SQL and Entity Framework developers, to wit:

• SQL Management Studio improvements including IntelliSense
• New Data Types for just dates or times (no more storing time when you only need the date)
• New Hierarchical data support .IsDescendent(), that can automatically pull a hierarchical view of data (no more custom recursive queries)
• New Grouping Sets statement which enables you to automatically group dimensions in a query for easy aggregation and reporting
• New Merge statement which provides automatic insert/update semantics for keeping multiple data sources in sync
• New FileStream attribute that enables you to include files that are stored in the server file system, but can be managed by SQL Server
• New data types and support for Geodata and geometry
• New support for optimizing "empty" or row/column tables using the sparse keyword

.IsDescendent() relies on the new hierarchyid data type, whose value represents the encoded position in a tree hierarchy.

You can learn more about converting a table that is structured as a parent/child hierarchy and move the data into a new table that represents the hierarchy and then manipulate that data in the Tutorial: Using the hierarchyid Data Type topic. Not surprisingly, the tutorial uses the AdventureWorks Employee table to construct a "reports-to" hierarchy to replace a self-join. The same technique could be applied to the Northwind Employees table.

Note: It's not clear yet how or if LINQ to SQL or the Entity Data Model will suppory hierarchyid tables.

Caution: Euan Garden notes in his SQL Server 2008 CTP's and "Leap Day" post of February 29, 2008 that SQL Server 2008 services will not start during the 24 hours of February 29, 2008 GMT.

He recommends: Do NOT stop or restart any SQL Server 2008 services during those 24 hours. (There are no workarounds.)

It's fortunate that Katmai didn't release at the release party or earlier. There would have been bad news today.

Added: 2/29/2008

Chris Rock Attacks Dynamic LINQ to SQL Queries

Orlando, FL developer Chris Rock (not the comedian) digs into creating Dynamic LINQ queries with Joe and Ben Albahari's LinqKit Predicate Builder in the LINQ TO SQL Dynamic Queries post of January 24, 2008 and the follow-up LINQ TO SQL Dynamic Queries #2 (Or statements) of February 27, 2008.

The latter post demonstrates that you can create dynamic LINQ to SQL Queries with AND, as well as OR clauses, in C# or VB.

Added: 2/29/2008

Use LINQ to Manipulate Excel Data

Denis van der Stelt's Getting data from Excel the fast way, using LINQ post of February 29, 2008 shows you how to process Excel data with the Microsoft.Jet.OLEDB.4.0 data provider, a DataTable for the worksheet and LINQ to DataSets.

The whole process, including enumeration, takes only nine lines of C# code (if you consider the LINQ expression to be one line.)

An alternative is to use Cozy Yamaguchi's LINQ to Excel, the source code for which is downloadable from CodePlex. (There was no release when this post was written.)

Added: 2/29/2008

Charlie Calvert Updates List of Third-Party LINQ Providers

Charlie's Link to Everything: A List of LINQ Providers post of February 28, 2008 lists 22 third-party LINQ providers (Bart De Smet renamed LINQ to LDAP to LINQ to Active Directory).

My list of LINQ to ... labels at the left has a few more items but includes Microsoft providers, such as LINQ to REST (part of the ADO.NET Data Services client library), and some that are works in progress (e.g., LINQ to LLBLGen Pro.)

A few significant third-party LINQ providers/APIs missing from Charlie's post are:

• LINQ to JSON (from LINQ and Entity Framework Posts for 2/11/2008+)
• LINQ to NCover (from LINQ and Entity Framework Posts for 2/25/2008+)
• LINQ to Mock (from LINQ and Entity Framework Posts for 12/17/2007+)
• LINQ to SimpleDB (from LINQ and Entity Framework Posts for 2/11/2008+)
• LINQ to TerraServer (from LINQ and Entity Framework Posts for 1/7/2008+ and Charlie's own blog).

Added: 2/29/2008

David Treadwell: Atom and AtomPub Support for Windows Live Photos and Application Based Storage

The February 27, 2008 David Treadwell on New and Updated Windows Live Platform Services post to the Windows Live Dev News blog includes an "Atom Publishing Protocol (AtomPub) as the future direction" topic that states:

At MIX we are enabling several new Live services with AtomPub endpoints which enable any HTTP-aware application to easily consume Atom feeds of photos and for unstructured application storage (see [here] for more details). Or you can use any Atom-aware public tools or libraries, such as .NET WCF Syndication to read or write these cloud service-based feeds. [Emphasis added.]

For more information, see Atom and AtomPub Support Extended to Windows Live Photos and Application Based Storage of February 29, 2008.

David Treadwell is Microsoft's Corporate Vice President, Live Platform Services, which include "unified identity and directory, data synchronization, transport and presence, among others." He's also the Microsoft representative to DataPortability.org.

Added: 2/28/2008 Moved to separate post: 2/29/2008

Marcin Dobosz Details Dynamic Data's Project Templates

In his Project templates just got bigger post of February 27, 2008, Marcin contrasts the precocial Visual Studio 2008 templates used by ASP.NET Dynamic Data with the altricial templates of most other VS project types. (Not being a biologist, this was the first time I can recall hearing these terms.)

ASP.NET Dynamic Data's December CTP supports LINQ to SQL data sources only because the EntityDataSource control wasn't available for the Entity Framework. David Ebbo mentioned in a comment to his Dynamic Data screencast is now available! post of December 12, 2007, that;

Linq To Entities will indeed have improved support for this [many:many associations], and generally make it easier.

As of the December Preview, DynamicData only supports Linq To Sql, but the next public build will include Entities support. [Emphasis added.]

Hopefully, Marcin's pre-MIX post means that the "next public build" will accommodate an available EntityDataSource control.

Added: 2/28/2008

Rob Conery Uses LINQ to SQL's Contains() Operator for Predictive Modeling SQL IN Predicates

Rob starts his Creating IN Queries With Linq To Sql post of February 27, 2007 with:

Props on this one go to Scott Hanselman who pulled me back from the edge of the cliff last night. I was particularly distraught in getting a MIX demo together where I had to do some queries using LINQ, and I couldn’t for the life of me figure out how to fashion an IN query! With Scott’s help (and patience) I figured it out, and thought I should blog for my own reference, at least.

Jim Wooley beat Scott and Rob to the punch on the use of Contains with an array last August. I left this comment:

I believe Jim Wooley was the first to suggest the Contains trick in his Use the new LINQ “Contains” extension method for the SQL “IN” clause post of August 06, 2007.

At least that’s the first instance I’ve seen.

But then he goes on in another post to describe "LINQ gymnastics" for predictive cross-sell modeling—the technique for showing "items that other people bought, most often, when they bought the product the user just added to their basket."

The solution, described in his LINQ Gymnastics: Creating A Predictive Query With LINQ post of the same day, requires five chained query expressions to produce this SQL to list the top five matches from the Adventure Works database:

SELECT [t0].[ProductID], [t0].[Name], [t0].[ProductNumber], [t0].[MakeFlag], [t0].[FinishedGoodsFlag], [t0].[Color], [t0].[SafetyStockLevel], [t0].[ReorderPoint], [t0].[StandardCost], [t0].[ListPrice], [t0].[Size], [t0].[SizeUnitMeasureCode], [t0].[WeightUnitMeasureCode], [t0].[Weight], [t0].[DaysToManufacture], [t0].[ProductLine],
[t0].[Class], [t0].[Style], [t0].[ProductSubcategoryID], [t0].[ProductModelID], [t0].[SellStartDate], [t0].[SellEndDate], [t0].[DiscontinuedDate], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Production].[Product] AS [t0]
INNER JOIN (
    SELECT SUM(CONVERT(Int,[t1].[OrderQty])) AS [value], [t1].[ProductID]
    FROM [Sales].[SalesOrderDetail] AS [t1]
    WHERE (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [Sales].[SalesOrderHeader] AS [t2]
        INNER JOIN [Sales].[SalesOrderDetail] AS [t3] ON [t2].[SalesOrderID] = [t3].[SalesOrderID]
        WHERE ([t2].[SalesOrderID] = [t1].[SalesOrderID]) AND ([t3].[ProductID] = @p0)
        )) AND ([t1].[ProductID] <> @p1)
    GROUP BY [t1].[ProductID]
    ) AS [t4] ON [t0].[ProductID] = [t4].[ProductID]
ORDER BY [t4].[value] DESC

As Rob says:

I can rewrite that query into a Sproc, lose 60% of the SQL, and be done with this whole thing in very short order. In addition, all of the LINQ statements amount to a lot of extra code just to build a SQL statement. However…

If you break our your queries into steps and add some comments, like I did here, you understand a whole heck of a lot more what I, the stats wonk, am trying to do.  If you were to stumble accross a predictive Sproc in the DB, you would no doubt spend a whole lot of time trying to figure just WTF I was thinking.

Added: 2/27/2008

Bob Beauchemin Starts a Series About LINQ to SQL and Entity Framework

Bob's MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1 post of February 13, 2008 (which I missed), spells out the misgivings of a well-known SQL Server trainer (for SQLSkills), tuner and developer about the use of LINQ to SQL and Entity Framework in an enterprise IT environment. He lists the following six "worry points":

1. LINQ to SQL and EF will proliferate dynamic SQL, and will almost surely produce suboptimal dynamic SQL, causing database performance problems and plan cache pollution. And dynamic SQL is evil to start with.
2. LINQ to SQL and EF will encourage "SELECT * FROM..." style coding because you get back a nice, clean object this way instead of the less-useful anonymous type you receive by doing projection. This will also make most/all covering indexes useless.
3. LINQ to SQL and EF will encourage "SELECT to middle-tier, then update/delete" rather than issuing direct SQL UPDATE/DELETE statements that are set-based.
4. LINQ to SQL and EF will write code that gets too much or too little data at a time. This is a variation of #2. Too much data in one query is a waste. Too little data is also bad because it means extra database round-trips.
5. LINQ to SQL and EF will discourage the use of stored procedures to return rowsets; returning rowsets in stored procedures is *thought to be* superior to returning them in dynamic SQL. In addition, the code generators will use a subset of T-SQL query constructs, that is, only the constructs that the LINQ or ESQL language supports, rather than the full power of the T-SQL query language.
6. LINQ to SQL and EF queries will be "untunable" because, even if you discover a performance problem, you can't change the underlying API code to produce the exact SQL query that you want. There's too many layers of abstraction to change it

The post above covers worry point #1. Chris Rock makes some interesting observations on proliferating query plans with parameterized queries and stored procedures in his LINQ TO SQL V 1.0 Hickups post of February 19, 2008.

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 2 of February 14, 2008 tackles worry point #2

Added: 2/27/2008

Joe Feser Unveils LINQ to NCover for Code-Coverage Analysis

NCover is a commercial package for reporting the code coverage of unit tests for .NET projects. NCover has two versions: Professional (US$149) and Enterprise (US$299). The Enterprise edition provides detailed HTML reports and XML code-coverage data; Professional only outputs the XML data as Coverage.xml.

Taking advantage of the XML code-coverage data requires sophisticated querying and sorting capabilities, so Joe Feser wrote the code for LINQ to NCover, which is available for download from a link in his Linq To NCover Part 2 post of February 26, 2008. LINQ to NCover processes queries, such as the following:

c = CodeCoverage.Load(file);

var retVal =from mod in c.Modules
            from cls in mod.Classes
            from method in cls.Methods
            where method.Name == ".ctor"
            select method;

His earlier Linq To NCover Part 1 post of the same day provides additional background on the project and mentions that he went to work for Gnoso, Inc., NCover's publisher, in Taylors (a suburb of Greenville), SC.

Added: 2/27/2008

Matt Hidinger Discovers That the Entity Framework Won't Filter by Comparing Custom Classes

In his Entity Framework Comparison Frustration: Explained post of February 26, 2008, Matt attempts to run the following code

TorvusEntities entities = new TorvusEntities();

// Pull my Account Entity from the database
Account matt = entities.Accounts.First(a => a.AccountId == 10);

// Attempt to get all Teams by a Team Owner
var teams = from t in entities.Teams
            where t.Owner == matt
            select t;

And receives an exception because:

LINQ to Entities does not support using a user class as a constant. However, a property reference on a user class is considered a constant, and will be converted to a command tree constant expression and executed on the data store.

From the "Comparison Expressions" topic of the LINQ to Entities Programming Guide.

Matt's earlier post on the topic is ADO.NET Entity Framework Comparison Frustration.

Julie Lerman Delivers a Hit .NET Rocks! Podcast About Entity Framework (and Admits to an Entity Framework Book in Progress)

Don't miss it. You can listen to it here.

She also comes clean in her Okay, so I'm writing a book post of February 27, 2008 that she's writing Programming Entity Framework for O'Reilly which she hopes will release in October. I'm really sorry she couldn't have a Newfi (Newfoundland dog) engraving on the cover because it had been used for a VBA book. :-(

The title has a bit in common with David Sceppa's Programming the Microsoft® ADO.NET Entity Framework for Microsoft Press (due in July).

Added: 2/27/2008

Michael DeMond Adds an EDMX Code Generator Plug-in to EFContrib

Michael's Plug-in Edmx Code Generator Released post of February 25, 2008 describes the code-generator plug-in he derived from the earlier Sample Edmx Code Generator by Sanjay Nagamangalam, an ADO.NET program manager. Michael's is the second project contributed to CodePlex's EFContrib project.

The first comment to Sanjay's SampleEdmxCodeGenerator sources post of January 24, 2008 is the request for a plug-in version. It was nice of Michael to comply.

Thanks to Julie Lerman for the heads-up.

Added: 2/27/2008

Alex James Completes His Teasers About Entity Teamwork Support for Associations with Payloads

Alex observed in his first Associations with Payloads post of 2/24/2008, that Entity Framework (EF) automatically creates a many:many association instead of an entity for a table that consists entirely of two (or more) foreign key fields, but won't ordinarily do this if the association carries a payload, such as quantity. (See LINQ and Entity Framework Posts for 2/18/2008+).

Next, his Associations with Payloads - part Deux post of 2/25/2008 concludes that a read-only association is the only way to circumvent the restriction without resorting (?) to a conventional entity.

Finally (!), Alex's Associations with Payloads - closing the loop later the same day describes the seven hand-hewn modifications to the mapping files that are required to implement a read-only many:many association with a payload.

Sounds to me like a lot of work to enable this LINQ to Entities query:

var products = from o in ctx.Orders 
               from p in o.Products 
               where o.ID == 3435 
               select p;

Entity Framework Workaround for SQL Server 2008 Spatial Data Type

Alex James points in his strangely-named All the power at your disposal post of February 25, 2008 to a SQL 2008 and spartial data post by 'Benys' in the ADO.NET (Pre-release) forum. Benys' post is a great example of developer self-reliance in the face of what appear to be poor odds.

Benys asked if EF supports SQL Server 2008's spatial datatype. According to Danny Simmons, who should know:

Unfortunately, no, the EF will not support spatial data from SQL Server 2008 in EF v1.  Spatial data in SS08 is a kind of user-defined-type, and while UDTs are something that the EF will support in future releases they do present some unique challenges and in the end were postponed to a later release.

Benys solved the problem with a defining query and a partial class to return the spatial value as a binary data type.

Beth Massi Demonstrates LINQ to SQL Data Validation with Partial Classes and Partial Methods

Beth's Simple Validation with LINQ to SQL Classes post of February 25, 2008 describes in detail the steps required to:

1. Implement the IDataErrorInfo interface
2. Inherit the implementing class from partial EntityName classes
3. Write the business rules to perform the validation
4. Display the validation errors in the UI

Beth's tutorials have become to VB projects what Scott Guthrie's are to C# and ASP.NET projects.

Andrew Robinson: Set LINQ to SQL Text Box MaxLength with Reflection

In his TextBox MaxLength from LINQ Meta Data post of February 24, 2008, Andrew shows you how to write an ExpressionBuilder to set the MaxLength properties of bound ASP.NET text box controls by reflection. He also includes the addition to the Web.config's <system.web><compilation> section to load the ExpressionBuilder.

Technorati tags: Entity Framework, Entity Data Model, EDM, LINQ to Entities, LINQ to SQL, LINQ to REST, LINQ to Atom, LINQ to NCover, LINQ, LINQ to Excel, LINQ to DataSets, SQL Server 2008, Spatial Datatype, Data Validation, ExpressionBuilder, Julie Lerman, EDMX, ADO.NET Data Services, MIX08, ASP.NET Dynamic Data, Dynamic Queries, LinqKit, Katmai