LINQ to SQL Server CE

In Visual Studio 2008, when trying to drag a local SQL Server CE database table onto your LINQ design surface, you receive the following error:

"The selected object(s) use an unsupported data provider."

This message is a little misleading, because you can in fact LINQ over your SQL Server CE database; it's just the drag & drop LINQ design surface in Visual Studio that doesn't know how to interact with SQL CE.

To work around this limitation of the IDE, you can call upon Microsoft's SqlMetal utility to create a .dbml file (aka, a new LINQ to SQL item) on your behalf. Here's a reasonably simple way to accomplish it:

In your project's solution explorer, right-click the node containing your .sdf (database) file and "Open Folder in Windows Explorer". In the resultant window, create a new text document called MakeCeLINQ.bat and edit it, entering this command:

C:\"Program Files\Microsoft SDKs\Windows\v6.0a\bin\sqlmetal.exe" /dbml:CeLINQ.dbml Database.sdf

Replace Database with whatever your .sdf file is called. It's important that this batch file be created in the same folder as your .sdf file, since the command is structured in such a way that assumes the .sdf file is sitting next to the batch file. Close & save MakeCeLINQ.bat, and double-click to run it. SqlMetal will create CeLINQ.dbml.

In Visual Studio, click the button to show all files for your project and include your newly created CeLINQ.dbml file. Include the "MakeCeLINQ.bat" file, too - strictly speaking it's no longer required, but you may find it convenient to keep it with your project.

So now you should have something like this:



You can now work with the CeLINQ object within the IDE as normal (with the ever-present exception of drag & drop functionality). If you change any of the schemas in your CE database, simply re-run the MakeCeLINQ.bat to update the CeLINQ object.