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