DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Using LINQ-to-SQL with SQL Server Compact 4.0

Using LINQ-to-SQL with SQL Server Compact 4.0

Erik Ejlskov Jensen user avatar by
Erik Ejlskov Jensen
·
Apr. 12, 12 · Database Zone · Interview
Like (0)
Save
Tweet
9.40K Views

Join the DZone community and get the full member experience.

Join For Free

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

sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Choosing Between GraphQL Vs REST
  • How Many GPUs Should Your Deep Learning Workstation Have?
  • Slowness in Java Application Due To Increased FullGC Events: G1GC
  • How To Use Cluster Mesh for Multi-Region Kubernetes Pod Communication

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo