Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

This question on StackOverflow more than hinted at the fact, that it was possible to use LINQ to SQL with SQL Server Compact 4.0. (Despite “rumours” of the opposite). I decided to find out, if this would work, and what it required tooling wise. (Please be aware that this is not supported by Microsoft). The SQLMetal.exe command line utility, that is used to generate the LINQ to SQL DataContext and related table classes is hardcoded via a string constant to work with 3.5. But using my command line utilities in combination with SQLMetal would allow you to create the classes based on a 4.0 database file. Using a batch (cmd) file similar to the following:

   1:  set sdf=C:\projects\ChinookPart2\Chinook40.sdf

   2:  set class=Chinook

   3:   

   4:  c:\data\sqlce\bin\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly

   5:  del c:\temp\%class%.sdf

   6:   

   7:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n

   8:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -i c:\temp\temp.sqlce –n

   9:   

  10:  sqlmetal /code:%class%.cs C:\temp\%class%.sdf

In line 4, the exportsqlce40 utility is used to export the schema (table definitions) from the 4.0 based file.

In line 7, and empty 3.5 file is created, and in line 8 it is populated with the generated schema.

Finally, in line 10, sqlmetal is run against this newly created 3.5 file, and generates the required classes.

To test that this worked, I created a Console project, added a reference to System.Data.Linq and System.Data.SqlServerCe.dll version 4.0.0.0. I also included the Chinook.cs file generated above in the project.

Then I used the code below to test that I could access the database via LINQ to SQL:

using System;
using System.Data.SqlServerCe;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
            {
                using (Chinook db = new Chinook(conn))
                {
                    db.Log = Console.Out;
                    var list = db.Album.ToList();
                    if (list.Count > 0)
                        System.Diagnostics.Debug.Print("It works!");
                }
            }

        }
    }
}

NOTE: Notice that the Chinook DataContext class is initialized with a SqlCeConnection object, I could not make it work with a full connection string, or the name of an existing connection string setting in app.config.

In the next release of the SQL Server Compact Toolbox, the code generation process has been incorporated, allowing you to very easily add a the LINQ to SQL classes to your project, both based on 3.5 AND 4.0 database files.

New menu item on the database context menu:

New dialog: