Geeks With Blogs

Steve Clements .Net and then some....

You are all undoubtedly aware of LINQ to SQL, its fair to say its had a large amount of publicity and coverage in the .net circle.  VS 2008 makes getting going with LINQ to SQL very easy but that is not the case with SQL Compact (yet!).  However its not difficult if you have heard of a wonderful little app called SqlMetal, this little beauty will generate the .dbml files for you...happy days :)

I have a small SQL Compact database to use for this demo, you can see it here in VS 2008 server explorer...


Run VS 2008 Command Prompt, navigate to the location of your .sdf file (you can of course run SqlMetal using the full file path to the .sdf but I just prefer the prior) and type...

>SqlMetal SqlCeFileName.sdf /dbml:LinqClassName.dbml

That will create the dbml file in the same directory as the .sdf file, now add it to your project in the normal way.


You can open the .dbml file in the same way and you will get the VS 2008 designer because its doesn't care what provider you are using (SQL CE) that will be sorted at runtime - right now you are just looking at a .dbml file.



You can now use LINQ in the same way as you would for SQL Server...

Tageditor te = new Tageditor(TagEditor.Properties.Settings.Default.TagEditorConnectionString);

Note: I am passing the location to the SQL Compact database file to the constructor of the LINQ class ( "Data Source=|DataDirectory|\\Data\\TagEditor.sdf" ), as I said before, I don't need to specify anywhere that the provider is SqlCE, that is inferred at runtime! :)

You can now use LINQ in the same way to work with your database - I wont go into LINQ here, there are plenty of resource around to are a couple I found useful.


ScottGu's 9 part series on LINQ - all you need to get going really.


SQL Compact Edition

MS SqlCe Home

SqlCe Blog

SqlCe FAQ by Steve Lasker


Technorati Tags: , , ,
Posted on Tuesday, November 13, 2007 10:26 AM .NET 3.5 , SQL | Back to top

Comments on this post: LINQ to SQL...compact!

# re: LINQ to SQL...compact!
Requesting Gravatar...
It is probably worth pointing out that this only works for SQL CE on the desktop. I.e. LINQ to SQL is not supported by teh Compact Framework and hence this would not work for SQL CE on Windows Mobile etc...

Wouldn't want to get anyone's hopes up ;-)
Left by Daniel Moth on Nov 13, 2007 11:03 AM

# re: LINQ to SQL...compact!
Requesting Gravatar...
Thanks Daniel, good point well made!

Consider my hopes truly slashed :(
Left by Steve Clements on Nov 13, 2007 11:08 AM

# re: LINQ to SQL...compact!
Requesting Gravatar...
I can't manually create dbml files for my SqlServerCE database like the exact way you're doing it. I keep getting "Provider 'System.Data.SqlServerCe.3.5' not installed". Note, I am doing this under the regular command prompt window because I am runing VC#2008 Express Edition as I do not own a copy of the retail stanard version. I just upgraded to VC#2008 and it comes with the Compact 3.5 provider already. I think the problem is because I need to run the the command under VS 2008 command prompt so it'll look into the correct visual studio folder where it'll find the Compact 2.5 Provider but I don't know how I can do this with regular command prompt. I tried looking for VS 2008 command prompt as a seperate single download but I don't think it's available. If you can help me, I would greatly appreaciate it. Thanks.
Left by Eddie on Nov 22, 2007 4:38 AM

# re: LINQ to SQL...compact!
Requesting Gravatar...
Eddie and others with the same problem. You have to build for x86, not "Any CPU".
More information is here:
Left by Francisco Ramos on Sep 16, 2008 6:35 PM

# re: LINQ to SQL...compact!
Requesting Gravatar...
Can we use dbml built from SQL Server in SQL Compact?
Left by jasonlaw on Dec 17, 2008 4:36 AM

# re: LINQ to SQL...compact!
Requesting Gravatar...
Hi, I have created a test app using SQLCE and Linq and all works fine. I understand that by coping the required DLL' from the CE directory I could deploy the App with the need to install SQL compact on that machine. The database is said to be local to the App itself. I have the following error when running the App: not installed

Left by Ron Nash on Feb 02, 2009 2:19 PM

# re: LINQ to SQL...compact!
Requesting Gravatar...
Great - thanks! :-D
Left by Simon T on Feb 16, 2010 3:09 AM

# re: LINQ to SQL...compact!
Requesting Gravatar...
I am trying to generate a dbml file but i am getting error as

Error : The specified password does not match the database password. [ Data Sour
ce = E:\Projects\smc thanigai\Afvc\AFVC_24_1_2011\Module1\Module1\Database\AFVC_
SCT.sdf ]

I dont know wats the problem in this,please help me out guys
Left by Naresh on Jan 24, 2011 9:57 PM

# re: LINQ to SQL...compact!
Requesting Gravatar...
I'm so greatful. Thank you so much. You giveme the info i need to complete my app.
Left by Alexis González on Dec 17, 2011 4:00 AM

Your comment:
 (will show your gravatar)

Copyright © Steve Clements | Powered by: