Using LINQ-to-SQL with SQL Server Compact 4.0
Using LINQ-to-SQL with SQL Server Compact 4.0
Join the DZone community and get the full member experience.
Join For FreeFind out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate
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 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:
Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}