LINQ to SQL (Compact Edition) Step by Step

The first time I tried out LINQ to SQL with SQL Server Compact databases I noticed LINQ Surface Designer in Visual Studio doesn't support SQL Server Compact provider. I read that the only way is getting through command line tool called sqlmetal.exe. I don't like this kind of tools cos are less productive and slow. Then, I decided to create an open source user interface and share it in www.codeplex.com/sqlmetalosui.

In this post I'm going to show you an easy way to use LINQ to SQL with SQL Server Compact database and how we use it within VS project.

LIMITATIONS: LINQ to SQL is only available for Windows Desktop platforms.

At first, let's create a Windows Form application in Visual Studio. Then, add northwind.sdf sample database allocated in %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Samples folder into the project and connect it from Server Explorer.

Once we got the connection established and we're sure the database is OK, execute SQL Metal OSUI tool and select the database file from Input File section. Click on next, and then select the output path for dbml file. Try to put the output path into Visual Studio project's folder.

If all is OK, SQLMetalOSUI would generate the output file in dbml format based on northwind.sdf (or selected input file) database schema. Then add the output file called per example, northwind.dbml into the project. By opening the northwind.sdf file from VS you can see that the entire table's definition have been included; there is no way to select (using SQLSE) only few tables.

Once we got the LINQ to SQL file created, let's use it. Indeed, there is not any particularity about using it. Is't only a set of Ienumerable classes ready to be LINQed. The visual representation of this classes, via VS Designer, is only a helpful tool. So in this way would be possible to use something like this.

using (Northwind context = new Northwind(Settings.Default.NorthwindConnectionString)) {
Table products = context.GetTable();
var res = from p in products
where p.UnitsOnOrder > 0
select new {
p.ProductID, p.ProductName,
p.EnglishName, p.CategoryID
};
this.dataGridView1.DataSource = res;
}

And the results...

CAUTION: Be sure Northwind.sdf Connection String is properly inserted into resource file in your project.

Now I'm working to create VS AddIn project and integrate SQLMetalOSUICtrl. It would be nice, to keep this tool closer while developing the application. But, by the moment, it's only a planned issue, not juts exists.