Getting Started With Linq To Sql and Sql Server Compact Edition (CE)

UPDATE 7.21.2008: Well, I'm now in the middle of my third project using Sql Server CE and I've got to say that it's really kind of a piece of shit and I can't in good conscience recommend using it with LINQ or without. It's a shame because having a decent offering in this space would really be a strength for Microsoft but SqlCE just kind of sucks. The tools suck, the IDE support sucks and the database format itself is so chock-full of limitations and oddities that using it is way more trouble than it's worth. My current advice is that if you've got a project you're thinking of using SqlCE on you should either ditch LINQ and use some other embedded database or else just bite the bullet and use the full version of Sql Server. Bummer huh? Old post preserved below: Today I had a little project to get done that caused me to take a look at using Microsoft's relatively new product, Sql Server 2005 Compact Edition. As near as I can tell CE (as we'll call Compact Edition from here on out) is sort of like a blend between normal Sql Server and MS Access. In terms of its SQL support and syntax it's like Sql Server. But the big difference is that there is no server. Instead CE utilizes a single database file which has an .sdf extension. This file works similarly to the old Access .mdf files in that you can move it around, copy it, etc. and every instance of the file is its own separate database. Accessing this file from code is very like accessing a normal Sql Server instance: you create a SqlCeConnection object with a connection string and then you can use objects that are immediately familiar like SqlCeDataReader, SqlCeCommand, etc. These objects are found in the System.Data.SqlServerCe namespace and operate almost exactly like their normal System.Data.SqlClient coutnerparts. But the object of this post is twofold: firstly to explain how to create and work with CE .sdf files and secondly to then show how to use Linq To Sql rather than the SqlServerCe data client classes to access that file in an application. Neither of these tasks is particularly difficult but neither is either obvious or (in my opinion) particularly well documented. So hopefully you've searched for something like 'getting started with Linq to Sql and Sql Server CE' or 'using Linq with Sql Server Compact Edition', you've ended up here and hopefully the following will be of service to you. To get started you'll need to create your CE database (.sdf) file. You can do this either with Visual Studio 2008 or with Sql Server Management Studio Express SP2. If you're using Management Studio you need to be certain you download the SP2 version for CE support. At the time of this writing it can be found here. Note that for CE files Management Studio provides absolutely no benefits that I can see over just using the built in VS2008 Server Explorer tools so for this post I'm going to be using VS2008 to create and manage the db. So to create the db in VS2008 either create a new executable project (WinForms or Console) or open an existing one. Then in the Solution Explorer pane right-click on the project and choose 'Add . . .' -> 'New Item . . .' from the context menu. This will bring up the Add New Item dialog. You might want to filter the list by click on 'Data' in the top left pane of this dialog. The template you are looking for is called 'Local Database'. Select this and then type a logical name for the database. This name will be the physical file name as well as the default name for your Linq DataContext when we create it. For this example we'll call it 'MyAppDB.sdf'. Click OK and the .sdf file will be added to your project. VS will immediately then prompt you to create a DataSource and DataSet for your new database. Since we're going to be using Linq we don't need either of those so just cancel out of the Data Source Configuration Window. At this point you should see MyAppDB.sdf listed as a file within your project in the Solution Explorer. If you open the Server Explorer you should also see it listed under Data Connections. Expand the listing under Data Connections in the Server Explorer and you should see sub-folders for Tables and Replication. If you do then you're db is created and ready to go. Now all you need to do is add some tables. You can either use SQL to do this by right-clicking the db entry under Data Connections and selecting 'New Query' or you can use the GUI to do it by right-clicking on the db's Tables sub-folder and choosing 'Create Table'. The GUI's probably a bit easier but it's also pretty limited. It will allow you to create a primary key and an identity field. It will allow you to create indexes and to set default values on fields. And of course it allows you to add, delete and modify the columns in the table. What it won't do however is let you create foreign keys. I have absolutely no idea why this is since CE supports them just fine but that's the way it is. If you want to create a foreign key you've got to do it with SQL. Just in case you can't remember the syntax it should be something like this:

alter table myChildTable
add constraint FK_ParentID 
foreign key (ParentID)
references myParentTable(ParentID) on delete cascade   

Once you've got your db schema all set up how you want it you're ready to generate your Linq DBML file so you can start coding against your database. Once again however for whatever reason Microsoft has failed to implement the most obvious way of getting this done. The DBML designer in VS doesn't have full support for CE. You can't just create a new Linq to Sql classes object in your project and then drag tables from the Server Explorer to the .dbml files designer canvas. In fact, there is no way (as far as I can tell) within VS to create a DBML file for a CE database. To do this you have to use the command line tool SqlMetal.exe. The easiest way to get at SqlMetal in my opinion is to go into your start menu and from there choose 'Microsoft Visual Studio 2008' -> 'Visual Studio Tools' -> 'Visual Studio 2008 Command Prompt'. This will open a console window whose path and everything are set up correctly to run SqlMetal. In this console window you then need to execute a command like this:

sqlmetal /dbml:MyAppDB.dbml MyAppDB.sdf /pluralize

SqlMetal is the code generator that VS uses to create a DBML file and its code-behinds when you use the designer. While the designer doesn't fully support CE, SqlMetal does. So what we're doing here is an end-run around the designer. Let's take a look at the arguments we're passing to the SqlMetal executable above. When we specify

'/dbml:MyAppDB.dbml'

we are telling SqlMetal two things. First we're telling it that we want it to generate a DBML file. Second we're telling it that the filepath of the newly generated file should be

\MyAppDB.dbml

. The second argument,

'MyAppDB.sdf'

is the filepath to the CE database file that has contains the table schema we want SqlMetal to run against. The final argument,

'/pluralize'

tells SqlMetal to automatically handle pluralization of the names of entities. For instance the entity generated to represent rows in a table named Employees will automatically be called Employee. This works pretty well and I recommend using it as it makes your code more readable. In American English at least. Now one thing you'll note is that I didn't specify paths to either the .dbml file or the .sdf file in the above call. You certainly could, but I find it easier to just cd to your project directory first, before you execute SqlMetal at all. That way everything just refers to the current directory (your project directory) by default. So, so far your steps were to:

1. Create or open a project in Visual Studio 2008
2. Add a Local Database file to that project
3. Added one or more tables to the database created in step 2
4. Opened a Visual Studio Command Prompt window and changed directory to your project directory
5. Ran the SqlMetal command line tool with something like this:

   'sqlmetal /dbml:MyAppDB.dbml MyAppDB.sdf /pluralize'

At this point if you look in your project directory you should see that there is a new file there called MyAppDB.dbml just as you specified. Now go back into your project in VS and in the Solution Explorer right-click your project and from the context menu select 'Add' -> 'Existing Item . . .' and in the resulting dialog find MyAppDB.dbml and add it. Now you should see the .dbml file listed in the Solution Explorer under your project. Double-click it and voila! The designer opens up and you should see all of the tables in your database represented graphically, including their relationships if you defined any. That's really all there is to it. To program against your model simply create an instance of the related DataContext object and use Linq to query against it. For instance:

using (MyAppDB db = new MyAppDB("MyAppDB.sdf"))
{
 //add a row to the database
 MyTestEntity myEntity = new MyTestEntity();
 myEntity.TestProperty = "Test";
 db.MyTestEntities.InsertOnSubmit(myEntity);
 db.SubmitChanges();

 //load rows from the database into objects
 var query = from mte in db.MyTestEntities
    select mte;
 
 //should output one instance of the word 'Test'
 foreach (MyTestEntity e in query)
 {
  Console.WriteLine(e.TestProperty);
 }
}

Hopefully someone out there finds this helpful! If so leave a comment and let me know.