I was trying to use MS Access with one of the applications I was writing for a non-profit organization but thought about the security of the database file. That being said, I opted to use SQL Server Compact Edition as it is just a small application that requires a database. This would enable the database to be embedded with the application and minimize the need to configure a database engine as a service, like how you would do with SQL Server Express. Since I wanted to use LINQ, I was quite surprised when the Design Surface threw an error saying that the data provider for SQL Server Compact was not supported. Good thing there’s SqlMetal. I use SqlMetal to automate generation of DBML files for multiple SQL Server databases. Eventhough the Design Surface does not support SQL Server Compact, you can simply import the generated DBML file in your Visual Studio project. You can generate the DBML file for the SQL Server Compact database by running the SqlMetal command below:

SqlMetal /dbml:northwind.dbml northwind.sdf

Run the Windows SDK tool <drive>:Program FilesMicrosoft Visual Studio 9.0VCSqlMetal.exe against your generated sdf file. Make sure you navigate to the folder containing the SDF file, typically the Visual Studio project folder. You can also generate the .vb or .cs code depending on your preference

Check out this blog post from the SQL Server Compact Team on using LINQ with SQL Server Compact