LINQ and Entity Framework Posts for 1/5/2009+

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

••• Update: 1/11/2009 6:00 PM PST: Minor additions
•• Update: 1/9/2009 5:30 PM PST: Minor additions
• Update: 1/8/2009 4:30 PM PST: Minor additions

Entity Framework and Entity Data Model (EF/EDM)

••• Gil Fink’s My Current Learning List of Technologies post of 1/11/2009 lists:

• NHibernate
• ASP.NET MVC Framework
• Microsoft Sync Framework

as the .NET Technologies he’ll be studying and about. You can expect his Gil Fink on .NET blog posts to center on them in the early months of 2009.

••• Alex James’ Virtuoso – Entity Framework compatible ADO.NET provider post of 1/11/2009 reports that Kingsley Idehen’s Virtuoso database framework now supports the ADO.NET Entity Framework. His SaveChanges(false) post of the same date explains how the true and false arguments effect implicit and explicit trasactions when you invoke the SaveChanges() method on an ObjectContext.

•• David Ebbo’s A helper to easily set up change notifications in Entity Framework post of 1/9/2009 shows you how to write a helper method to simplify dealing with EF change notifications.

•• Alex James of the EF Design team discusses Model Defined Functions in EF v2 in this 1/7/2009 post and seeks comments about its usefulness and implementation from EF users. Alex also discovered LINQ… the board game and would like to know “is it a good game?”

• Danny Simmon reports repairs to last December’s revisions in his Updated EF FAQ posted post of 1/8/2009. Danny says:

I made a round of updates to the EF FAQ a while back but then had some trouble with the blog software (it doesn't really like the huge word doc chunks I cut and paste into it) and didn't get around to fixing things.  This morning I gave it another go and things worked.  So we finally have a few updates.  You can take a look at: http://blogs.msdn.com/dsimmons/pages/entity-framework-faq.aspx

• Simon Segal’s Fetching Strategies for the Entity Framework - a waste of time for now? post of 1/7/2009 questions “whether it’s worth implementing a half baked solution that only helps in eager fetching.” He then goes on to

Build calls to .Includes() in non generic methods implemented by a Repository, would probably be the best place to do this for Repositories implemented on top of Entity Framework V 1.0.

and concludes

My fear is that if the changes / upgrades to the Entity Framework for Version 2.0 don’t cut deep enough many will lose faith and patience and may be forced by default into using some other ORM. Personally this is a critical feature for me however for the moment I have decided there is no satisfying solution to this problem and so I am going to leave Fetching Strategies out of my library for now. I will post the entire code soon.

• Matthieu Mezil reports that he’s writing his own EDM Designer in this 1/7/2009 post. He says his designer:

[M]akes two interesting points compared to the current MS designer:

• The ability to show only some entity types and not all
• The ability to show Complex Type

And he plans to add the following features:

• The [cap]ability to add or remove csdl type / property / relation / inheritance (fixing these bugs of course) and to save it into an edmx file
• The [cap]ability to change csdl type / property properties like the name, the visibility, etc.
• The [cap]ability to show and change the mapping (including TPC which isn’t supported by current MS designer)
• The [cap]ability to (re)generate csdl entity types from ssdl entity types. (In current MS designer, it generates it when you add the table / view from the wizard but if you delete your csdl entity type, you have to regenerate it yourself or to delete your ssdl entity type and to use the wizard to add it again. Moreover, for SSDL View, you have nothing to generate the CSDL entity type)
• The [cap]ability to add SSDL Views or Functions
• Integrate it in VS
• The [cap]ability to save your designer

• Alex James’ Nullable=”False”… but when exactly? post of 1/6/2009 observes that whether the Nullable=”False” condition is enforced depends on the context in which the test is made; for his example the condition is to or from persistent storage. Alex notes that the contract feature, which is coming in .NET 4.0, can extend the contexts to which the test applies compared with the present Annotations approach.

• David Hayden discusses the importance of checking the number of queries issued by your chosen object/relational mapping (O/RM) tool in his Performance Profiling O/R Mapper Calls to the Database post of 1/6/2009. Dave’s post was sparked by Ayende Rahien’s release of an early beta version of NHProfiler for NHibernate, which is not free or open-source.

• Julie Lerman’s EF Support for Firebird – Beta1 post points to Jiri Cincura’s Firebird ADO.NET Data Provider 2.5.0 Beta 1 for .NET 3.5/2.0 [with Entity Framework support] post of 1/6/2009, which you can download here.

LINQ to SQL

•• Stephen Wrighton waxes ecstatic from Madison Mississippi about LINQ to SQL and LINQ in general in his LINQ to SQL post of 1/7/2009. I didn’t know there was a Madison in MS.

• David Hayden’s Quick Examples on LINQ To SQL Performance Tuning - Performance Profiling Your O/R Mapper post of 1/8/2009 was evoked by Simon Segal’s Fetching Strategies for the Entity Framework - a waste of time for now? post (see the “EF/EDM” section.) Dave says:

On this subject of O/R Mappers and their ability to be real chatty with your database if you are not careful, I thought I would show some quick examples with regards to LINQ To SQL and iterating against lazy loaded collection properties. For some background on why I am talking about this, see Performance Profiling O/R Mapper Calls to the Database.

Guy Barette, Microsoft Regional Director for Quebec, says in his 2009 Predictions - ASP.NET, BizTalk and LINQ 2 SQL are dead and so are VB, C# and Azure post of 1/5/2009 that these technologies are dead but then goes on to debunk his LINQ to SQL prediction with the “real story" for LINQ to SQL:

LINK 2 SQL vs Entity Framework: So Microsoft created some confusion by releasing two similar ORM technologies? Yes of course but if you look closely, they are somewhat similar and different at the same time. LINQ 2 SQL is RAD against SQL Server and the Entity Framework is the full blown ORM thing. Both use LINQ as the query language so if you invested in LINQ 2 SQL, your investment is not wasted and for God’s sake, LINQ 2 SQL is in the Framework; it cannot die!

One suggestion that I saw on the Web was to release LINQ 2 SQL on CodePlex so that people can update it since Microsoft has “abandoned” it.  First, Microsoft never said that it has abandoned it. They will keep it as it is right now and they will put all their efforts on the Entity Framework. My hope is that when we’ll see new SQL Server releases, Microsoft will update LINQ 2 SQL to match some of the new features. So back to the CodePlex idea: I think it’s a bad one because people will try to match the features of the Entity Framework or Nhibernate and that will create more confusion because we will have similar products competing against each other.

LINQ 2 SQL = simple 1 to 1 mapping against SQL Server
Entity Framework = complex mapping against SQL Server and more

2009 prediction: people will still be confused and FUD will run loose on the Web

Guy’s 2009 prognostications aren’t the last you hear for LINQ to SQL vs. the Entity Framework this year. You can read other Regional Directors’ predictions in their The Region blog.

Damien Guard’s Changing type, the state pattern and LINQ to SQL post of 1/5/2009 applies the Gang of Four’s State Pattern to emulate a runtime change of type (from OpenAccountState to ClosedAccountState) by a bank account class with an inheritance discrimator for Type and state indicator for Active.

LINQ to Objects, LINQ to XML, et al.

•• Eric White explains Accepting Revisions, Removing Comments, and Removing Personal Info from a SharePoint Document Library using Web Services (and LINQ to XML) in this sequel to his earlier post (below) of 1/9/2009.

•• Eric White discusses Modifying Open XML Documents that are in SharePoint Document Libraries using Web Services (and LINQ to XML) in this detailed 1/8/2009 post.

ADO.NET Data Services (Astoria) and REST

•• Steven Robbins explains in Self Hosting ADO.Net Data Services (Astoria) of 1/10/2009 how to self-host “both a “normal” WCF service, and an ADO.Net Data Services service inside the main application.” Steven continues:

The former is very straightforward and well documented, but I was a bit short of information on the latter and the information I did have hadn’t been updated for the various RTM changes.

Luckily it’s very easy to do, so I thought I’d throw together a quick example.

•• Rob Bagby’s Powerpoints for ‘Best of PDC’ sessions post of 1/9/2009 includes the slides from REST with WCF and the WCF REST Starter Kit, which Rob describes as follows:

This session will provide a broad overview of REST support in WCF, including some key new features available in the recently released WCF REST Starter Kit.  The session will begin with a theoretical discussion of what REST is.  It will continue by illustrating WCF support for developing LO-REST and HI-REST APIs.  This session will be largely demo driven, with specific demos demonstrating consuming LO-REST services being consumed by AJAX clients, as well as developing HI-REST services being consumed by Silverlight 2 clients.  This session is 60% demo-driven.

• OakLeaf posted Azure Storage Services - StorageClient Library: Table Storage API Class Reference on 1/7/2009. Here’s the connection to Astoria:

The only “official” interface exposed by the Azure Storage Services is the REST protocol. The StorageClient sample provides “example” .NET wrapper classes for REST API operations managed by the System.Data.Services.Client library.Note: Azure Table Storage is an ADO.NET Data Services provider, as described for SQL Data Services in my SQL Data Services to Gain New ADO.NET Data Services Client Model post of 1/6/2009.

Gil Fink’s Building ADO.NET Data Services Ajax Queries Using a QueryBuilder post of 1/3/2009 describes how to use Sys.Data’s QueryBuilder object to simplify the building of queries from the client side by eliminating the need to remember the syntax to create the query. Gil’s post
provides an example of how to create a QueryBuilder with to query a
Course Uri.

ASP.NET Dynamic Data (DD)

••• David Ebbo offers six Tips on getting your ASP.NET Dynamic Data questions answered quickly in this 1/11/2009 post. The tips apply to questions you post to the Dynamic Data Forum only.

Rick Anderson’s brief Dynamic Data FAQ of 1/9/2009 answers these questions:

• Q: How do I get started with Dynamic Data?
• Q: Can Dynamic Data use a data model generated by a 3rd party O/RM tool?
• Q: How do I hide columns from the Edit and other page templates?
• An example of one today is LLBLGen: http://www.llblgen.com/defaultgeneric.aspx

SQL Data Services (SDS) and Cloud Computing

This topic moved permenantly on 1/3/2009 to Azure and Cloud Computing Posts for MM/DD/YYYY+.

SQL Server Compact (SSCE) 3.5 and Sync Services

•• Gil Fink’s Getting Started with Microsoft Sync Framework post of 1/10/2009 claims that the Microsoft Sync Framework “is going to be one of the must know technologies of the near future” and provides an architectural overview of the framework.

Miscellaneous (WPF, WCF, MVC, Silverlight, etc.)

••• Shawn Wildermuth posted See my Silverlight Data Access Talk from DevReach on 11/12/2009 so folks who didn’t make it to Bucharest can watch the the video of his Silverlight Data Access talk.

Guy Burstein’s ASP.Net MVC RSS Feed Action Result post of 1/11/2009 provides a full description of the eight different Action Results returned by the ActionResult class’s ExecuteResult() method. His example delivers a RSS Feed object.

His ASP.Net MVC Route Constraints post of 1/10/2009 is a complementary essay on the MVC Routing Engine’s mapping between URL routes and controller actions and how to apply route constraints.

•• Shawn Wildermuth points to availability of Microsoft’s patterns & practices (p&p) team’s Unity: an IoC Container for Silverlight on 1/9/2009, although the Unity Application Block 1.2 for Silverlight - December 2008 was released on 12/13/2008. Shawn says:

[I]n late December, the PnP team shipped their Inversion of Control (IoC) container for Silverlight called the Unity Application Block. This is a port of the Unity Application Block that has been available for .NET for some time. For more information on IoC containers (and the Dependency Injection pattern), see Martin Fowler's article here.

To compliment this, I know the team is also working on a port of their Composite WPF Application Guidance (which optionally uses the Unity Application Block) for composing large applications from smaller pieces.  This guidance is a great way to build large Silverlight or WPF applications. They are working on the Silverlight version of this and hopefully it will be released soon.

•• Bill McCafferty’s An argument for moving ASP.NET MVC controllers to a separate assembly assesses the benefits of preventing bidirectional dependencies between controllers and data layers in ASP.NET MVC projects.

•• Craig Murphy (a.k.a. The Social Programmer) posted 049 - David Yack and Jonathan Carter on ALT.NET, MVC and Community from London on 12/18/2008 but I missed it during the Holiday Madness. Here’s the deck:

In this podcast you can listen to David Yack and Jonathan Carter very eloquently discussing ALT.NET and .NET 3.5 SP1 and beyond. You may hear me using to the word “opposite” somewhat out of context, apologies for that…I was within the “safe for podcasting alcohol limit”…it just goes to show that any alcohol and podcasting can be have consequences…

• Simon Segal’s Silverlight and Model View Presenter (is it a realistic option?) post of 1/6/2009 is about his Case In Point Silverlight application that “just now moved into preparing the ground for structuring my approach to the Business Logic and Data Access and how I was going to affect loosely coupled layers.”

Technorati Tags: Entity Framework,Entity Data Model,EDM,LINQ to SQL,LINQ to XML,LINQ,ADO.NET Data Services,Astoria Project,MVC,Silverlight 2,Firebird

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

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

Danny Simmons: Serializable Entity Framework Object Graphs Coming in Next CTP

Hidden in an innocuous "WCF & Entity Framework not working as expected" thread started on October 31, 2007 in the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum is this bombshell dated March 28, 2008:

[W]e realize that it would be nice to have graphs of entities just serialize by default without these kinds of gymnastics, but there are some hard problems with doing this using WCF in an interoperable way given that EF relationships are always bi-directional which means that EF always has graphs not trees and the cycles in graphs cause problems with the default WCF (DataContract) serializers.  Happily we've been working with the WCF team, and they came up with a great way to deal with this, so the next CTP of the EF will serialize entity graphs by default. [Emphasis added.]

If true, this new capability will obsolete Danny's EntityBag (Perseus) project. It wouldn't appear to eliminate the need for Ruurd Boeke's serialization to a lightweight client project, at least for Silverlight clients, but it might affect the market for IdeaBlade's DevForceEF product.

The inability of the DataContractSerializer to handle cyclic references appeared to be a major stumbling block to enabling an "out-of-the box multi-tier story" for LINQ to SQL. Hopefully, Matt Warren and his C# team will take another look at enabling n-tier deployment for LINQ to SQL projects.

Thanks to Julie Lerman for the head's up: Hallelujah - Entity Graphs will be XML serializable.

Added: 3/28/2008

Performance Problems Fixed for VB Queries with Filters Over Nullable Columns

Tim Ng reports in the VB Team's LINQ to SQL (and LINQ to Entities) performance improvements post of March 28, 2008 that LINQ to SQL and LINQ to Entities queries are "now smart enough to pass three-value logic from VB to SQL." This means you'll get

SELECT [t0].[CustomerID] 
FROM [dbo].[Orders] AS [t0] 
WHERE [t0].[EmployeeID] = @p0 
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [123]

instead of

SELECT [t0].[CustomerID] 
FROM [dbo].[Orders] AS [t0] 
WHERE (COALESCE( 
    (CASE 
        WHEN [t0].[EmployeeID] = @p0 THEN 1 
        WHEN NOT ([t0].[EmployeeID] = @p0) THEN 0 
        ELSE NULL 
     END),@p1)) = 1 
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [123] 
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

when you filter on the nullable Orders.EmployeeID field.

Added: 3/28/2008

Ajax World: "Microsoft SSDS - Dumb Name, Brilliant Idea!"

Tim Negris' AjaxWorld Magazine article of March 17, 2008 has this deck: "A Google News search for Microsoft SSDS, the SQL Server Data Services it announced last week, yields just six articles." If Tim had broadened his search beyond New articles, he would have found hundreds of SSDS blog posts by March 17.

Tim explains the article's title:

To understand the potential impact of Microsoft's cloud strategy and SSDS service initiative on Oracle, IBM/DB2, Sun/MySQL, Ingres, Postgres and other relational or post-relational database management systems, it's important to note that SSDS has nothing to do with SQL. SQL Server Data Services is a dumb name because, while the service is apparently based at the storage level on Microsoft's SQL Server technology, it doesn't use SQL (Structured Query Language) or a variant of it to query the information in the database. It uses a new Microsoft language called LINQ.

He goes on to say:

LINQ enables full text search, page search, and presumably things like picture and audio search against items stored in the Microsoft SSDS database.

and notes earlier:

Keep in mind that "data" [stored by SSDS] can mean way more than inventory or contact records in columns and rows of text and numbers. Data can mean documents, e-mails and web pages, or music and video.

Neither of the preceding two excerpts are correct, at least for SSDS v1.

SSDS's only connection with AJAX that I can come up with is the promise of a JSON wire format for a future version.

Thanks to the SQL Server Data Services team for the heads-up: Interesting take from Rick Negris over at AjaxWorld magazine.

Added: 3/28/2008

Bryan Dawson Compares Performance of Entity Framework' eSQL and LINQ to Entities with SqlClient and T-SQL

Bryan follows up his two previous performance-related posts (Exploring Performance Part 1 of February 4, 2008 and Exploring Performance Part 2 of February 11, 2008) with ADO.NET Entity Framework Performance Comparison of March 27, 2008.

Bryan analyzes execution time of conventional and paging queries with no tracking for object queries. His graph shows the Entity Framework is about 4 to 5 times slower than SqlClient for paging queries. However, I believe Bryan's calculations are wrong for Entity SQL and LINQ to Entities paging queries.

My calculations, based on 100 tries with 64 ms. and 87 ms. for Entity Client and LINQ to Entities respectively appear in the Corrected column:

Query Type	Standard Query	Paging Query	Corrected
SqlClient/T-SQL	4.0 ms	0.43 ms	0.43 ms
Entity SQL	6.0 ms	1.5 ms	0.64 ms
LINQ to Entities	7.5 ms	2.0 ms	0.87 ms

The ratios between execution times for Standard Query and Paging Query don't correspond, but Standard Query and Corrected ratios track closely.

Moral of this story: It pays to apply reasonableness tests before publishing performance results.

Added: 3/28/2008

Noam Ben-Ami Explains Simple Stored Procedure Mapping and Raises a Red Flag

Noam's Stored Procedure Mapping of March 26, 2008 (which appeared in the ADO.NET Team Blog on March 28) provides a script to create simple Product and Order tables as well as INSERT, UPDATE, and DELETE stored procedures for the Product table. Noam adds this caveat in his instructions:

At this point it is appropriate to note the first stumbling block: At this time, the Entity Framework requires you to map all three stored procedures. You cannot map only the delete stored procedure. In addition, the entity type must still be mapped to a table, view, or query view.

The requirement to map all three updated stored procedures is unadvertised but mentioned in a newsgroup post. Noam says the following about this requirement:

We hope to provide a solution for this issue in future versions of the Entity Framework, but for now, this is what needs to be done for associations whose target multiplicity, with respect to the type being mapped, is 1 or 0..1. In other words, the “reference” end of the association needs to be mapped to the keys of the target type.

I've never heard of a restriction that prevents executing update stored procedures against an Entity instance mapped to a FunctionImport. I've retested my initial test code that executes the INSERT, UPDATE and DELETE stored procedures and don't encounter a problem.

Added: 3/28/2008

Migrating to SQL Server Stored Procedures with the EDM Designer December CTP 2

Migrating the data source for Entity Framework (EF) EntitySets and AssociationSets from dynamic Entity SQL (eSQL) to dynamic SQL (T-SQL) commands to stored procedures (sprocs) with the Entity Data Model (EDM) Designer December CTP 2 isn't a walk in the park.

There's little in the way of official or unofficial documentation that specifies EF's requirments for supporting sprocs as data sources and data modification agents. What little information is available is buried in ADO.NET Team members' replies to a few users questions in the ADO.NET Entity Framework and LINQ to Entities (Pre-release) forum.

Earlier ADO.NET Team posts don't cover the EDM Designer's role in the process. For example, How to Map Stored Procedures using the ADO.NET Entity Framework of September 14, 2007 covered only the schema syntax and didn't discuss associations or updates. The earlier Using Stored Procedures for Change Processing in the ADO.NET Entity Framework of March 8, 2007 is more than a year old.

My Migrating to SQL Server Stored Procedures with the EDM Designer December CTP 2 post provides illustrated step-by-step instructions for moving the Products, Supplier, and Categories EntitySets from default eSQL queries and commands to a set of T-SQL stored procedures for EntitySet hydration and Entity instance updates, and covers mapping Associations as well as stored procedures for populating AssociationSets.

A future post will show the code required to test the migration.

Added: 3/27/2008

Ruurd Boeke Continues his Lightweight Client for the Entity Framework Project

The "Ruurd Boeke Plans a Entity Framework Variant with a Lightweight Client" topic of  LINQ and Entity Framework Posts for 3/17/2008+ describes Ruurd's objectives for a disconnected Entity Framework component with a lightweight client that can be used in a Silverlight project:

So, the end result should be an easy way to serialize and deserialize objects on the client and server, and a way to build up the object correctly on the server so it can be attached to the context again.

Ruurd's approach differs considerably from Danny Simmons' Perseus: Entity Framework EntityBag project on the MSDN Code Gallery, which I described in the "Retrieve and Update Entities Across Tiers with WCF and the EntityBag" sidebar for my "Model Domain Objects with the Entity Framework" for the March 2008 issue of Visual Studio Magazine.

His Relation support for n-tier EF post of March 27 provides code and message examples for serializing Entity updates with WCF's DataContractSerializer. You can compare his messages on the wire with those for Perseus that I described in my Logging EntityBag's SOAP Request and Response Messages post of January 29, 2008.

Ruurd concludes:

The API I have created is monstrous. Also, I think there are quite a few situations where this will break horribly.

Once I get just a little happier about it, I will do a screencast building a server using Entity Framework and a Silverlight client that is editing.

Added: 3/27/2008

Colin Meek: EF Extensions for Custom Stored Procedures with the Entity Framework

Colin, an EF developer, starts his new blog with ADO.Entity Framework: Stored Procedure Customization post of March 26, 2008 which provides documentation for the Entity Framework Extensions described in this week's "Entity Framework Extensions Library Available for Download from MSDN Code Gallery" topic.

His post describes how to use the extensions to simplify running arbitrary stored procedures and T-SQL commands as store commands against an ObjectContext, materialize them as objects, track changes, and deal with multiple result sets. The post describes the following coding patterns:

• Creating and executing store commands
• Materializing typed results
• Tracking results
• Multiple result sets

Alex James waxes enthusiastic about EF Extensions in his Colin Meek talks about his EF Extensions post of the same date:

Here is a little snippet that illustrates what is possible [with a typed materializer]:

var results = context
.CreateStoreCommand("GetCategories", CommandType.StoredProcedure)
.Materialize<Category>()
.Bind(categorySet);

What this does is execute the GetCategories stored proc, turning results into Category objects and binds them to the categorySet entityset so that changes can be tracked/managed by the Entity Framework.

Update 3/27/2008: Diego Vega adds more praise for the extensions in his Entity Framework Extensions (EFExtensions) Project available in CodeGallery post of March 27, 2008.

If you're populating entities with stored procedures, be sure to read Colin's post, download the EFExtensions and EFExtension Samples and give the sample project a test drive.

Added: 3/26/2008

Freek Leemhuis Scorns LINQ to SQL and Others Pile On

LINQ to SQL problems, which Freek posted on March 26, 2008, takes Anko Duizer's LINQ to SQL - TOP 5 of problems post of March 14, 2008, and contributes explanations of why Freek considers the following problems serious:

1. LINQ to SQL is designed for a two tier scenario. LINQ to SQL does not fit natural[ly into] a multi layer architecture.
2. It is not possible to tune the SQL Server queries generated by LINQ to SQL.
3. LINQ to SQL generates dynamic SQL. The technology strongly motivates use [olf] dynamic SQL instead of stored procedures.
4. LINQ to SQL encourages ... "SELECT *" quer[ies.]
5. The cache mismatch problem. LINQ to SQL materialize[s] your objects "once" in the cache, this can give unexpected query results.

Paul Gielens adds #6 in his LINQ to SQL - TOP 5 + 1 of problems post of March 25:

#6 LINQ to SQL is intended for direct 1-1 mapping of a Microsoft SQL server database to .NET classes. LINQ to SQL is not a natural fit in typical enterprise scenarios.

LLBLGen's Frans Bouma pronounces judgment in his detailed comment to Paul's post:

Item 3, 4 and 5 for example are noise or wrong. ... Your #6 point is also noise, sorry :). ... Point 2 is valid.

and recites his usual litany of complaints about LINQ to SQL. Be sure to read the rest of the comments.

Note: Freek has written about LINQ to SQL previously. Click here for his posts tagged LINQ.

More Reasoned Views of LINQ to SQL Issues

Freek also includes a reference to Bob Beauchemin's MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1 post of February 13, 2008. However, Bob generally debunked #2, #3, and #4 in this post. See the "Bob Beauchemin Starts a Series About LINQ to SQL and Entity Framework" of LINQ and Entity Framework Posts for 2/25/2008+.

Similarly, Bob's later posts offer rational evaluations of other topics. See the "Bob Beauchemin Addresses LINQ to SQL/Entity Framework Worry Points 4, 5 and 6" topic of LINQ and Entity Framework Posts for 3/6/2008+.

As to #1, LINQ to SQL fits without difficulty into a multi-layer architecture. The issue is its problems in with n-tier architecture. Keith Farmer made a strong point in his LINQ and 3-Tier Dogma post of November 6, 2007:

I know someone somewhere is going to accuse me of heresy for what I'm about to state, but it's something I've wanted to get off my chest for a couple years, since I first encountered LINQ to SQL, and started to realize what it meant...

The 3-Tier Model is just a pattern.

And, like all patterns,

It is meant to serve our needs, not the other way around.

Don't get hung up on academic concerns such as how many tiers you have in your system.  You'll just waste the time you could spend doing things you actually need to do.  Such as watching Avatar or playing Warcraft.

As evidenced by the amount of work being devoted to change tracking in disconnected (i.e., n-tier) scenarios, the n-tier issue might not remain for long.

Added: 3/26/2008

Matthew Hunter Updates LINQ to SQL Entity Base 1.0 to Beta 1.2

LINQ to SQL Entity Base 1.0 now supports change tracking for deletions and can perform cascade deletions. His New Version of LINQ to SQL Entity Base Class (Beta 1.2) post of March 26, 2008 adds:

I guess from my point of view it's feature complete for ASP.NET use, assuming you store the entities in the session.

Still have to work on serialization for WCF and other uses.

Here's the CodePlex link: LINQ to SQL Entity Base Class Version 1.0 Beta 2.0

The "Matt Hunter: Another Disconnected LINQ to SQL Project" topic provides more details on LINQ to SQL Entity Base.

Added: 3/26/2008

Update 3/28/2008: Implementing Deletion Change Tracking adds a SetAsChangeTrackingRoot() method to establish the baseline original values of an object and implements cascading deletions on the client.

Avi Wortzel Demonstrates a Complete Mock Object for LINQ to SQL

Israeli developer Avi Wortzel's Testing LINQ to SQL with Mock Object – a complete solution post of March 25, 2008 explains:

I decided to implement my own solution which includes two parts:

1. The ability to create database states [to] test the queries against ... (without the need of active SQL Server [o]n the air).

2. The ability to mock DML operations without actually execut[ing] them (and to create a database-less tests [= Tests that don’t have an active instance of SQL server to run]).

For the first part I used the Repository pattern (as the other guys suggested) and I referred the class which represent[s] the table as an in-memory one. It’ll give us the ability to query it with LINQ to Object. To achieve the second part I mocked the SubmitChanges() method from the DataContect class.

It appears to me to be a workable approach for removing SQL Server from the test loop. Avi's downloadable example uses Oren Eini's Rhino Mocks framework.

Added: 3/26/2008

Doug Rothaus: VB's XML Axis Properties and LINQ to XML for Accessing Descendent and Ancestor Elements

His VB XML Cookbook, Recipe 2: Descendants and Ancestors post of March 19, 2005, which didn't appear in the Visual Basic Team blog until March 25, "shows how you can access descendant and ancestor elements in an XML document using Visual Basic, XML Axis properties, and LINQ to XML objects."

Julie Lerman: Build Before Changing Entity Framework XML Files to Resources

Her Tip for working with embedded Ent. Framework models with Beta3 of March 25, 2008 explains Beta 3's problems with moving *.csdl, *.msl and *.ssdl files from the execution folder to project resources.

K. Scott Allen Explains Outer Joins with the LINQ GroupJoin Operator

Scott digs into creating the equivalent of SQL's LEFT OUTER JOIN with the LINQ GroupJoin, SelectMany, and DefaultIfAny operators in his Inner, Outer, Let's All Join Together With LINQ post of March 24, 2008.

Entity Framework Extensions Library Available for Download from MSDN Code Gallery

Colin Meek posted ADO.NET Entity Framework Extensions to the ADO.NET Entity Framework & LINQ to Relational Data section of the MSDN Code on March 24, 2008.

The undocumented extensions claim to simplify "various tasks in the Entity Framework including store command execution, entity state management and stored procedure mapping."

Update 3/26/2008: Documentation is available. See the "Colin Meek: EF Extensions for Custom Stored Procedures with the Entity Framework" topic.

Anything that simplifies stored procedure mapping is appreciated.

Thanks to Julie Lerman for the heads-up.

Live Demo of CSDL, MSL, and SSDL File Content for Entity Framework Inheritance and Other Scenarios

Asad Khan's downloadable Entity Framework Mapping Helper project from the MSDN Code Gallery is an interactive Windows form that displays the XML content of the CSDL, MSL, and SSDL layer for a variety of different scenarios. These include combinations of inheritance types (table per hiearchy, table per type, table per complex type, or mixtures of all three) with entity splitting, abstract types, complex types, and associations.

The project has self-contained *.csdl, *.msl and *.ssdl files for what appears to be a country club, so all you need to do is mark one of the inheritance check boxes and click the Display Mapping button to start.

Julie Lerman's EF Mapping Helper on Code Gallery post of March 24, 2008 provides a more detailed description and several screen captures of the Mapping Helper in action.

Andy Conrad: New Features in Store for ADO.NET Data Services V2

According to Andy's Astoria team is hiring! post of March 24, 2008:

We have positions open for Developers and Program Managers.  Currently, we are finishing up V1 and planning for V2, so it’s a great time to join the team.  For V2, we are thinking about expanding the project into new areas (e.g. offline support, sync, dynamic language support) so there will be plenty of new and exciting work.

Adding resources to the Astoria team also bodes well for its future as the on-premises version of SQL Server Data Services.

Eugenio Pace Continues His LitwareHR Series with Data Caching Episode

Eugenio ported the LitwareHR hosted multi-tenant demo app from SQL Server 2005 to SSDS, as described in his SQL Server Data Services - SSDS - New version of LitwareHR post during MIX 08.

His LitwareHR on SSDS - Part III - Data access enhancements 1: caching post of March 24, 2008 describes client-side caching at the Web services layer with a CacheManager (based on the Enterprise Library's caching block) and Repository to get and store cached and uncached items.

Here are links to earlier episodes:

The LitwareHR on SSDS - Part II - The data access layer post explains that the new client data model for SSDS implements an SSDS SOAP Client, ProxyFactory and Repository<T> stack, which replaces the previous SQL Server-based version's Data Access Application Block (DAAB) and MultiTenantDataAccess layer.

His LitwareHR on SSDS - Part I - Multi-tenancy & Flexibility post of March 14, 2008 explains SSDS's Authority-Container-Entity (ACE) storage architecture and how LitwareHR uses it to store individual Position and Resume entity sets for each tenant.

Justin Etheredge Continues Improvement to LINQ to Mock (Moq) Implementation for ASP.NET MVC

The Grouping expectation verification in Moq post of March 24, 2008 documents Justin's continued effort to use LINQ to Mock (a.k.a. Moq) to provide a complete mocking framework for the ASP.NET Model-View-Controller add-in.

Click here for more about LINQ to Moq and ASP.NET MVC, plus Justin's previous episodes.

LINQ to Firebird (Entity Framework Provider for Firebird) Is Progressing

Jiří Činčura's Entity Framework and Firebird (Part 2) post of March 24, 2008 describes the progress to date on creating an Entity Framework-enabled data provider for the Firebird open-source database (formerly Borland's InterBase RDBMS). The developer, jirka, says:

We're moving, slowly, step-by-step. And new functionality is coming in view. ... Yes, it's a "LINQ to Firebird", in fact LINQ to Entites connected to Firebird.

Here's the initial post of March 20, 2008: Entity Framework and Firebird.

Matt Hunter: Another Disconnected LINQ to SQL Project

Another from the How Did I Miss That? Department:

Matt released v1.0 Beta 1.1 of his LINQ to SQL Entity Base - Disconnected LINQ CodePlex project on March 20, 2008. Matt describes his project:

LINQ to SQL Entity Base is a simple base class that is primarily designed to support LINQ to SQL in a disconnected way, which is one of the shortcomings of the LINQ to SQL technology at the present time. This is highly useful in an n-Tier or ASP.NET environment where disconnected functionality is relavent.

LINQ to SQL Entity Base includes change tracking support in a "disconnected from data context" mode.

LINQ to SQL Entity Base also supports automated re-syncing of entities to a data context. This includes all entities under a entity tree which is basically all parent --> child type relationships.

There's more background information on LINQ to SQL Entity Base in Matt's .Net Blog for Software Developers blog.

Benjamin Eidelmann: A Disconnected LINQ to SQL Project

From the How Did I Miss That? Department:

Jose Marcenaro, Benjamin Eidelman and Ricardo de Guzmán of Argentine developer Tercer Planeta have posted the Unplugged LINQ to SQL Generator project (ULINQ) to CodePlex. Benjamin says:

Fitting the LINQ to SQL technology in your own N-tier application framework may require writing custom code for the entities.

This project implements a custom tool for generating code from a DBML model, which you may use instead of the default MSLinqToSQLGenerator in C# or VB projects.

Click here to read background details on the ULinqToSQLGenerator from the Tercer Planeta blog.

Technorati tags: Astoria Project, ADO.NET Data Services, Sync Services, SQL Server Data Services, SSDS, LitwareHR, Entity Framework, Entity Data Model, EF Extensions, EDM, LINQ to Firebird, Firebird, LINQ to Mock, MVC, LINQ to SQL Entity Base, LINQ to XML, EntityBag, Serializaiton, DataContractSerializer, VB 9.0

Future LINQ to SQL Support for Multiple Databases?

In my recent tests of the Orcas March 2007 CTP's implementation of LINQ to SQL and the Object/Relational Designer, I tried substituting connection strings to SQL Server Compact Edition (SSCE) and Oracle 10g Express Edition with managed providers, and other databases with OLE DB drivers. Most resulted in "unsupported product" messages and all threw exceptions when attempting to open a connection with the DataContext object. One DataContext constructor accepts an IDbConnection object, which implies at least some degree of database independence.

SqlMetal.exe generates an XML external mapping file (NwindLINQ.xml for this example) that you can use in lieu of decorating entity classes with LINQ to SQL-specific attributes. This feature enables generating POCO (plain old CLR objects), which many developers prefer. The "LINQ to SQL: .NET Language-Integrated Query for Relational Data" white paper's "External Mapping" section indicates that the top-level <database> element of NwindLINQ.xml should have a ProviderType="System.Data.Linq.SqlClient.Sql2005Provider" attribute. It's missing from the mapping file I created with SqlMetal.exe, but it did point me to the System.Data.Linq.SqlClient namespace.

Inspecting SqlClient in Object Browser shows it contains about 167 classes having interesting names such as Funcletizer, which contains Friend Shared Function Funcletize(ByVal expression As System.Linq.Expressions.Expression) As System.Linq.Expressions.Expression. I remembered Funcletizer.Funcletize from Mike Taulty's blog and found it near the end of his Deconstructing LINQ to SQL (Part 2) post:

internal QueryInfo BuildQuery(Expression query, SqlNodeAnnotations annotations)
{
    this.CheckDispose();
    query = Funcletizer.Funcletize(query);
    QueryConverter converter = new QueryConverter(this.services, this.typeProvider, this.translator, this.sqlFactory);
    converter.ConverterStrategy = (this.Mode == ProviderMode.Sql2005) ? ConverterStrategy.SkipWithRowNumber : ConverterStrategy.Default;
    SqlNode node = converter.ConvertOuter(query);
    return this.BuildQuery(this.GetResultType(query), node, annotations);
}

You have to love the naming here :-) Note that the provider looks to have a SQL 2000 and a SQL 2005 mode on it which makes sense but I hadn't thought about it before even though I had thought that functions such as Take might use something 2000/2005 specific.

From there on in, it gets "really hard, really fast" or at least it did to me. From BuildQuery onwards there are a lot of classes (SqlSelect, SqlBinder, SqlRetyper, SqlMultiplexer, SqlFormatter and a whole lot more) that look to be involved in taking what we have as a tree and turning it into T-SQL.

It's obvious that it won't be a walk in the park to write the corresponding "really hard" extension code to implement another managed data provider and SQL flavor, such as Oracle PL/SQL or IBM DB2 SQL.

Update 4/21/2007: David Sceppa, an ADO.NET program manager and author of Programming Microsoft ADO.NET 2.0 Core Reference, posted ADO.NET Orcas: Sample Provider to the ADO.NET Team blog on March 16, 2007. The entry begins:

Last month, the ADO.NET team hosted a number of ADO.NET Data Provider writers on campus to present information on how to enhance an existing provider to support the Entity Framework features in the upcoming Orcas release. To help provider writers who were unable to attend, we’re publishing the material we presented to our team blog. In this blog post, you’ll find information about how to extend an existing provider. The blog also has a [Resources].zip attachment that contains the slide decks presented at the event, a help file that covers the CommandTree class, and the source code for a sample ADO.NET Data Provider.

The Resources.zip file contains OrcasSampleProvider.exe, but the source code snippets in "Extending an Existing Provider" specification don't appear to me to comprise the complete source code for an extended provider.

According to David's ADO.NET vNext Provider Writer Event Summary post of August 25, 2006, representatives from DataDirect, IBM, MySQL, SQLite, and Sybase attended the event. PHX Software's Robert Simpson writes about the conference in this short-term blog.

According to the April 2007 "Looking to LINQ" story in Redmond Developer News:

Microsoft is working with other database vendors to build interoperability into their repositories as well. Oracle Corp. officials would only say they're monitoring LINQ's progress, but IBM Corp. says it plans to support LINQ in both its DB2 database and the Informix IDS platform.

Curt Cotner, an IBM Fellow and CTO for database servers, says LINQ could very well be critical in eliminating the fragmentation that exists today among programmers and database developers. Still, Cotner expects LINQ to have its share of both ardent supporters and critical detractors. "It's going to be popular for a significant segment of customers, but there will be another segment that will see it as not down the path they're trying to follow," he says. ...

But Cotner doesn't see [the Java Persistence API,] JPA and LINQ as competing specifications per se. "It addresses a different segment of the population," he says. And frankly, he admits from a developer standpoint, JPA can't touch LINQ in terms of its ability to build native queries into code from the language and development environment.

VistaDB Software, Inc. announced the intent to support LINQ in a future version of VistaDB. VistaDB 3 is an embedded SQL database for .NET, the .NET Compact Framework, and Mono that supports datatypes compatible with SQL Server 2005 (except xml) and uses T-SQL syntax. Jason Short wrote in an April 7, 2007 "LINQ and VistaDB" blog post:

I feel quite strongly that LINQ support in the future will be a vital feature of VistaDB. Since we are already a fully managed database for Dot Net, the ability to make fully type safe queries will definitely set us apart from other database systems. And the fact that we will not have to drop to SQL code to perform that queries will mean a faster running database as well (and that is what you really wanted to hear, right?). ...

LINQ support is on the list for research right now. When will be release it? I don’t know. I would really like to hear from you about your plans to support LINQ. I think it is quite obvious that LINQ is going to change the way a lot of applications are written to query their database.

Update 5/16/2007: SQLite

---

I recalled seeing references to future support for databases other than SQL Server 200x [Express/MSDE], so I ran searches on "LINQ to SQL" Oracle, "LINQ to SQL" "Compact Edition", "LINQ to SQL" MySQL, etc. Here are the most topical responses I found from Microsoft folks (in chronological order):

Dinesh Kulkarni, LINQ to SQL Sr. Program Manager (6/15/2006, in answer to LINQ Project General forum request to "Compare DLINQ, NHibernate and Typed DataSet"):

Out of the box, [LINQ to SQL] will support SQL Server 2000/2005 but will be usable with other databases once providers for those databases are available.

Scott Guthrie, a general manager in the Microsoft Developer Division (1/28/2007, response to a blog comment re LINQ to SQL support for Oracle and MySQL):

"LINQ has a provider model, which will allow other databases to be plugged-in. So you could add support for both Oracle and MySql."

Steve Lasker, SQL Server Compact Edition program manager (3/1/2007, reply to blog comment):

We don't yet have a LINQ to SQL (DLINQ), but we do have ADO.NET V3 Entities working over SQLce. If you install the Sync Services CTP, you'll see we install a Entity dll for SQLce as well. This CTP isn't directly compatible with the Orcas Feb CTP, so don't expect to do anything with it just yet. It's possible the Feb CTP that was just released would enable Entities over SQLce, but I have to check to see what made it into that build.

Terry Adams, LINQ to SQL development lead (3/29/2007, LINQ Project General forum):

"LINQ to SQL does not currently support Oracle clients."

A Microsoft program manager (4/2/2007, private correspondence):

"Our Orcas plans do not include 'LINQ to SQL' Server Compact Edition in Orcas. LINQ to SQL is only SQL Server."

Matt Warren, Microsoft product manager for LINQ to SQL (4/12/2007, response to Julie Lerman's post in the LINQ Project General forum about LINQ to SQL being for SQL Server only):

LINQ to SQL actually stands for LINQ to 'databases that use SQL' or in other words LINQ for the relational data model. LINQ to Entities means LINQ for the Entity-Data-Model which is a kind of a relational++ model.

Dinesh Kulkarni, LINQ to SQL Sr. Program Manager (4/13/2007, Microsoft Connect feedback re use of IDbConnection to create a DataContext, resolved as "Postponed"):

"Thanks for your interest in the LINQ project and feedback. Support for other providers indeed is a very important area that we are looking into. Because of schedule reasons, we were not able to fit it in V1.

"As for OLEDB/ODBC, the problem is that these standards really don't fully address the semantic differences between SQL dialects and database type systems. So I suspect that we will have to emphasize managed ADO.NET providers (e.g. Oracle provider, DB2 provider etc.) over ODBC/OLEDB. Unlike in case of ADO.NET v2 APIs where the user decides the SQL semantics by creating appropriate string, LINQ to SQL (aka DLinq) has to understand the semantics of the generated string. Hence, we need separate providers that understand the corresponding SQL flavor and type system.

"That said, there may be enough value in handling a common subset. However, such a 'generic' provider will likely have limitations."

Keith Farmer, (4/18/2007, LINQ Project General forum response to my post about LINQ to SQL dependency on SQLClient):

Actually, LINQ to SQL is rather decoupled internally. Between scheduling and other external factors, however, we've only been able to show you a SQL Server provider.

Dinesh Kulkarni (4/19/2007, Microsoft Connect feedback answer to a follow-up question about using third-party managed .NET data providers with LINQ):

"Unfortunately we haven't had enough time to do a provider SDK or even a public provider API in Orcas. This is definitely a high priority post-Orcas release item for us. Thanks again for your feedback."

Update 4/27/2007: Elisa Johnson, Program Manager, Microsoft Data Programmability (4/23/2007, comment in the ADO.NET Team's ADO.NET Entity Framework - Orcas Beta 1 post):

We are working closely with many DB vendors, including IBM, to help them update their ADO.NET providers to support the Entity Framework. Unfortunately, I do not have any information as to when they may release a new provider.

Update: 5/7/2007: Jay Hickerson of the Visual Basic Team responding to a LINQ Project General forum question about the purpose of the O/R Provider property of LINQ to SQL's DataContext object:

The O/R Provider was originally placed there so that we could generate code that would work correctly with databases other than MS SQL Server at runtime. However, the runtime support for this did not materialize in time to get it into the designer for Orcas. The property has been removed for Beta 2 and there will not be support for different providers from the designer. We are considering adding some level of support back in a future release, possibly a service pack.

---

I doubt that the ADO.NET team would devote the resources necessary to port LINQ to SQL to competing RDBMSs, especially those for which Microsoft and the database vendor have released managed .NET providers, such as Oracle, but also encompassing products such as IBM DB2 9 and VistaDB 3.0 for which managed data providers are available from the vendor RDBS only.

Presumably, the Entity Framework (EF), which was designed from the get-go as the foundation of database-agnostic O/RM, and LINQ to Entities is the solution for Oracle, DB2, Informix IDS, and PostgreSQL. The ADO.NET team is supporting developers with extending providers for the EF's CommandTree class, which isn't likely to be the same as that for LINQ to SQL. However, the EF and LINQ to Entities seems to me to be overkill for embedded and other lighter-weight RDBMSs such as SSCE, VistaDB 3, MySQL, and Firebird (formerly Interbase). These databases are more likely to be used in projects with simpler object graphs that match the underlying persistence database's schema.

I'm not sanguine about the prospects for a future "provider SDK or even a public provider API" for LINQ to SQL from Microsoft. A future Microsoft LINQ to SQL provider for SSCE might arrive, but SSCE already has a LINQ to Entities provider that, hopefully, will be operable in Beta 1.

I think it's a good bet that an IBM DB2 LINQ to SQL EntityClient provider will arrive around the same date as the Orcas RTM in 2008H1 (?) that Microsoft finally releases EF and its components. I'm not holding my breath for the announcement of an EntityClient provider from Oracle.

Update 4/29/2007: Corrected spelling of Keith Farmer's first name, error, and rescheduled EF delivery date in the preceding (last) paragraph.

Technorati tags: LINQ to SQL, Entity Framework, Entity Data Model, LINQ to Entities, SSCE, SQL Server 2005 Compact Edition, Oracle, DB2, Informix, PostgreSQL, VistaDB, MySQL, Firebird