Thursday, April 12, 2012

Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

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:

   1:  set sdf=C:\projects\ChinookPart2\Chinook40.sdf
   2:  set class=Chinook
   3:   
   4:  c:\data\sqlce\bin\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly
   5:  del c:\temp\%class%.sdf
   6:   
   7:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n
   8:  c:\data\sqlce\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -i c:\temp\temp.sqlce –n
   9:   
  10:  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

19 comments:

vnt said...

hi ErikEJ

I tried to create datacontext file for my database and here is my line:

Z:\data\sql\exportsqlce40 "Data Source=%sdf%" c:\temp\temp.sqlce schemaonly

but my database got password on it. So how do i specify password for exportsqlce40?

Thanks

ErikEJ said...

Change the connection string to: "Data Source=%sdf%;Password=MyPassword"

Dunkelmann said...

I used the menu item to generate my data context.

I try to open my db with:
PortfolioContext db = new PortfolioContext(@"Data Source=C:\Users\Hamish\Documents\DB\Portfolio.sdf");

I get:
System.InvalidOperationException : Cannot open 'C:\Users\Hamish\Documents\DB\Portfolio.sdf'. Provider 'System.Data.SqlServerCe.3.5' not installed.

Both 3.5 and 4 appear in C:\Program Files (x86)\Microsoft SQL Server Compact Edition.

Why is it trying to open with 3.5 when the db is 4.0 as far as I know. I created it from a SQL Server Express db using your tools and it appears in the toolbox as Porfilio.sdf (4.0).

What am I doing wrong?

By the way the created PortfolioContext class has an attribute:
[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="C:\\Users\\Hamish\\AppData\\Local\\Temp\\Portfolio.sdf")]

Is that relevent? Is it needed? The database does not exist there but presumably was copied there to generate the class.

Thanks - I appreciate your help and toolbox.

ErikEJ said...

Use a SqlCeConnection object to initialize the DataContext, as described in the blog post sample code.
The "DatabaseAttribute" is not needed, no.

Dunkelmann said...

I got a bit further by manually (re)installing v4. I can now create the connection.

My next line:
var count = (from m in db.Types select m).Count();

fails with:
System.Data.SqlServerCe.SqlCeException : Incompatible Database Version. If this was a compatible file, run repair. For other cases refer to documentation. [ Db version = 4000000,Requested version = 3505053,File name = \\?\C:\Users\Hamish\Documents\DB\Portfolio.sdf ]

I have added this to the app.config but it makes no difference:







I have searched for a string "3.5" in all files but not found one.

The PortfolioContext file has the header line:
// Runtime Version:4.0.30319.17929

Hope this helps.

Thanks again.

Dunkelmann said...

OK, That's taken me further, I get a message about a table not existing but that may be down to me.

Thanks.

Dunkelmann said...

Not sure if this is me:
[Test]
public void TestConnection()
{
var conn = new SqlCeConnection(@"Data Source=C:\Users\Hamish\Documents\DB\Portfolio.sdf");
PortfolioContext db = new PortfolioContext(conn);
Assert.IsTrue(db.DatabaseExists());
var commodity = db.COMMODITY;
Assert.AreEqual(0, commodity.Count());
}

I get:
System.Data.SqlServerCe.SqlCeException : The specified table does not exist. [ COMMODITY ]

I can query COMMODITY from your Toolbox. It should be empty and it is.

ErikEJ said...

Please file a issue at the codeplex tracker with a repro project - comments here is not a good place to discuss this

Dunkelmann said...

Indeed.

I found the problem to be that I had the db in a DropBox folder and it must have been locking it. I copied the db to my Unit Test folder and all is fine.

PB said...

Hi ErikEJ

If you're still responding to this post here's my question:

I tried using your script to generate a data context but came to grief on the line

c:\sqlScripts\sqlcecmd -d"Data Source=C:\temp\%class%.sdf" -e create -n

the command line is reporting that it can't load sqlceme35.dll. I'm running Win7 64bit. I've got version 3.5 and 4.0 folders and dll's in the Program Files(x86) MS SQL Server CE folder..

Any Ideas welcome...

Thanx,
Paul

ErikEJ said...

Paul: You must download 3.5 SP2 and install both the MSIs in that package or just use the Toolbox...

PB said...

Thanks Eric

All good now.

This is my first attempt at working with sqlce. I wondered why I couldn't find any info on using linq with sqlce. Does it seem odd that MS doesn't support linq on their own db format shipped with Visual Studio?

Really glad that you're out there - had a couple of really frustrating days trying to get everything to hook up, but enjoyed that sweet feeling when everything clicked...

Paul

Unknown said...

Hi ErikEj,

It really helped me !! Thank you very much !!

PB said...

Hi Erik:

Just ported my sqlce4.0 project to a new computer and the toolbox isd not allowing me to generate the Linq to Sql classes. The menu item says (needs 3.5) at the end. It won't recognise v4.0 - I've installed VS2010 sp1, tools for sqlce4.0, but still get "the database is not supported by this project type..."

Any suggestions

ErikEJ said...

PB: You need to install the 3.5 SP2 runtime (or repair/re-install it) - you can probably see in About, that it is not properly configured/available.

PB said...

Hi Erik

Service pack 2 already installed but I ran repair on the 32 and 64 bit versions just to be sure. No change alas. No mention of sqlce in the 'About' dialog for VS2010 - is that the 'About' you were referring to?

Thnx,
Paul

PB said...

Hi Erik

Should have looked further - menu item reads "needs version 3.5" but menu item will now create the context class for me so all good...

Thnx
Paul

John Addo said...

Hi
I can Read data alright but saving doesnt write to the database.

//Code
context.Members.InsertOnSubmit(newMember);
context.SubmitChanges();

when i read just after the code it displays the newMember
but never writes to the database.
It also give no error as well.

ErikEJ said...

John: what is your connection string? Have you looked in the bin/debug folder?