Using LINQ-to-SQL with SQL Server Compact 4.0
Join the DZone community and get the full member experience.
Join For FreeThis 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 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:
Opinions expressed by DZone contributors are their own.
Comments