.NET Zone is brought to you in partnership with:

Erik Ejlskov Jensen is a .NET Data Developer, and SQL Server Compact MVP. He is also the author of a number of tools for SQL Server Compact. He has been working in IT for too many years, and enjoy blogging (http://erikej.blogspot.com) and tweeting (@ErikEJ) Data Development related news and tips. Erik Ejlskov is a DZone MVB and is not an employee of DZone and has posted 60 posts at DZone. You can read more from them at their website. View Full User Profile

Using LINQ-to-SQL with SQL Server Compact 4.0

04.12.2012
| 5704 views |
  • submit to reddit

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:

set sdf=C:\projects\ChinookPart2\Chinook40.sdf
set class=Chinook
c:\data\sqlce\bin\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly
del c:\temp\%class%.sdf
c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n
c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -i c:\temp\temp.sqlce –n
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:

image

New dialog:
image

Published at DZone with permission of Erik Ejlskov Jensen, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)