Sunday, July 29, 2007

LINQ to SQL to Support SQL Server Compact Edition

Despite previous statements to the contrary, LINQ to SQL will support SQL Server Compact Edition (SSCE), according to a blog post by Dinesh Kulkarni, program manager for LINQ to SQL (formerly DLinq). Microsoft's earlier plan was to limit LINQ to SQL to to use with SQL Server 200x [Express] and deliver only LINQ to Entities support for SSCE within the Entity Framework (EF). Dinesh's July 26, 2007 LINQ to SQL (fka DLinq): What's new in Orcas beta2 post included "SQL Server Compact (fka SQL CE) support" as number 7 in his 15-item list of new and improved LINQ to SQL features in VS 2008 Beta 2.

Important Update 7/30/31: Dinesh appears to have an overly generous definition of "support" and I extended this to my post's title. What he and I should have said is that SQLMetal.exe supports SSCE. Here's a quote from Dinesh's next-day post to the LINQ Project General forum's Sql Server CE and linq (Beta 2) thread:

Unfortunately, LINQ to SQL designer will not be able to support SSC in beta2 or even RTM of VS 2008. Let's see what can be done after that! [Emphasis added.]

BTW, before you run into this - there is an unfortunate known bug if you use SqlMetal against the Northwind.sdf shipped with beta2 SSC. Due to a space in one of the foreign key names, you will get an error. We are working on posting a Northwind.sdf that is closer the the "classic" Northwind database so you can try out SqlMetal and LINQ to SQL run-time on that.

Needless to say we have fixed the bug but unfortunately it didn't happen in time for beta2.

SSCE v3.5 enables developers to use SSCE's lightweight, high-performance embedded database for object persistence in specialized applications. SSCE is a single-user relational database that runs in the client application's process and supports multiple replication and synchronization protocols with SQL Server and, potentially, other databases. The production release of SSCE v3.5 will ship with VS 2008.

I had been agitating for SSCE support by LINQ to SQL since I began testing and writing about SSCE 3.5 and the new Synchronization Services runtime earlier this year. LINQ to SQL is a much lighter weight data access layer than LINQ to Entities plus the EF; thus LINQ to SQL is the preferred partner for SSCE.

However, the word from Microsoft in March and April 2007 was that LINQ to SQL was SQL Server 200x only. Here are a couple of related statements extracted from my April 19, 2007 Future LINQ to SQL Support for Multiple Databases? post.

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."

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."

Update 7/31/2007: New SSCE 3.5 Beta 2 documentation is available that VS 2008 Beta 2 doesn't install. Here are the links from Steve Lasker's SQL Server Compact 3.5 B2 & Sync Services for ADO.NET Documentation released post:

The Synchronizer blogger Rafik Robeal says in today's Sync Services Hit Beta 2.0 Milestone post that he'll update his Sync Services samples this week. Rafik also points to sync batching capabilities as a new Sync Services Beta 2 feature.

Update 8/10/2007: Version 1.0 of Sync Services doesn't have server-side push capabilities because the sync server is unaware of the location of sync clients. As an alternative to clients repeatedly pinging the server, Steve Lasker proposes an independent Notification to Pull system to send messages to sync services clients that the server has new data to deliver. In one implementation clients use a "Here I Am" Web service, similar to that used for roaming cellular phones, to track client addresses.

Here are links to my more recent articles and blog posts about SSCE and SSCE Sync Services, which also is part of VS 2008:

LINQ to SQL support for SSCE, such as it is, unquestionably was the right decision. However, designer support for SSCE should receive a high post-RTM priority and an out-of-band Web release.

P.S. Thanks to Julie Lerman for alerting me to Dinesh's post. Dinesh hadn't posted any new technical data about LINQ to SQL for more than a year so his blog wasn't on my reader's list.


Mike said...


I have been having "fun" trying to get all the bits (SSCE 3.5, Orcas Beta 2, SQL Managment Studio) in Vista 64 and Xp 32.

So far I have found at the SQLMetal does work with SSCE 3.5 but not in Vista64

BUT I cannot get any tool (tried SQL and SLQ express) to manage my SSCE db - ie make relationshipos so that SQLMatal can do its stuff

Any advice