SQL Server Compact Edition – newest stuff – SQLCE and entities

SQLCE 3.5 comes with an ADO.NET data provider that supports entities, by supporting the ADO.NET 3.5 entity data model (EDM). I noticed this in Orcas March CTP with the inclusion of a new DLL, System.Data.SqlCe.Entities. Although the DLL was there, there was no support in Visual Studio for any provider but SqlClient, and trying to do this manually failed (DbProviderFactory.GetService returned null, IIRC).

In Orcas B1, there still seems to be no support in the Visual Studio "Add ADO.NET Entity Data Model" dialog, but you can do this manually. To set things up, I used a SQLCE table that was roughly the same as the jobs table in the pubs database, used an EDM generated in Mar CTP by pointing at SQL Server's pubs database and tweaked the SSDL file a bit to be consistant with the SQLCE table. One thing I was surprised with was that SQLCE's EDM implementation didn't seem to mind SSDL's EntityContainer being named "dbo" and generated the correct query anyhow (in SQLCE "select * from dbo.jobs" fails, and I hoped this wouldn't be the query that was generated). It used the right query.

Here's a simple example of using EDM with SQLCE 3.5, database included. You need to put the pubs.sdf database in c:\temp or change the connection string in the app.config to make this work. The sample uses both EntityClient and Entity Services. It could just as easily use LINQ for Entities.

An interesting idea is that if the internals of Sync Services were tweaked sightly, they could use the EDM and EDM's generated SQL in addition to (or instead of) DataSets. Imagine replicating/synchronizing Employee entities with associated Job information instead of synchronizing Employee and Jobs tables. The logic could be hooked in from what AcceptChanges does.

CeEDM.zip (39.65 KB)

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.