Using LINQ to SQL and EDM with SQL Server Compact Edition 3.5

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "different" data sources. This post covers using SQLCE 3.5. The latest version of SQLCE 3.5 comes with Visual Studio 2008 Beta 2.

Using SQLCE 3.5 data sources doesn't work with the built-in LINQ for SQL designer in Visual Studio 2008 beta 2 or the CTP EDM designer released shortly after beta 2. You can add a Data Connection for a SQLCE 3.5 database. I used the Northwind.sdf sample database that was supplied. But dragging a SQLCE table on to the LINQ to SQL designer produces "The selected object(s) use an unsupported provider". The EDM Wizard doesn't even list the SQLCE ADO.NET data provider as a choice. So there's no LINQ to SQL or EDM designer support for SQLCE 3.5.

SQLCE works quite well with SQLmetal, although my attempt to generate a .dbml for the Northwind.sdf sample database produces the error message:

error DBML1055: The DeleteOnNull attribute of the Association element 'Order Det
ails_FK01' can only be true for singleton association members mapped to non-null
able foreign key columns.

Removing the "Order Details" table provides a 'hit it with a hammer' solution; I haven't figured out a nicer way to solve this problem yet. Then, the SQLMetal-generated classes worked fine.

Using EDMgen against the Northwind.sdf file produced the following error:

error 7001: Could not load System.Data.SqlServerCe.Entity.dll. Reinstall SQL Server Compact.
Could not load file or assembly 'System.Data.SqlServerCe.Entity, Version
=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its depend
encies. The system cannot find the file specified.

Hmmm…the version of SQLCE that came with Visual Studio 2008 B1 did come with this DLL. And, I had it working with EDM then. But the version in VS2008 B2 doesn't. As a last resort, I installed the SQLCE 3.5 version from VS 2008 B1, it's out on the web as a separate download. This changed the error message to:

error 7001: Method 'CreateDbCommandDefinition' in type 'System.Data.SqlServerCe.
SqlCeProviderServices' from assembly 'System.Data.SqlServerCe.Entity, Version=3.
5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not have an implementation.

Sure enough, the required implementation of the ProviderServices class required for EDM support has changed between VS Beta1 and VS Beta2. I confirmed this by checking the methods in the SqlProviderServices in System.Data.Entity.dll, they did change recently, adding CreateDbCommandDefinition. Unless I can find an updated System.Data.SqlServerCe.Entity.dll somewhere, it doesn't look like SQLCE is usable with EDM at this point in beta-time.

That's all for now.