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