venerdì, aprile 20, 2012

sql server compact 4.0 & entity framework & VS 2010 (no web app)

Entity framework of VS2010 SP1 has a good support for sql server compact 4.0. But... it works only in web application projects. It's known issue... or is it feature?  ;)
I've found a simple way to use EF + SqlCompact4.0 with console and winform application in VS2010 SP1. It requires a bit of hack of the db connection string.

1 - Create an EF edmx pointing to a "normal" Sql Server (I used tempdb of a sql server express). Do not map any table.  The resulting connection string in the app.config will be something like this:
<add name="MyEntities" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sqlexpress;initial catalog=tempdb;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

2 - Edit the app.config. Modify the connection string and point it to the sql server 4.0 compact file. Save the file.
<add name="MyEntities" connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlServerCe.4.0;provider connection string='Data Source=&quot;C:\path_to_my_file\MyDB.sdf&quot;' " providerName="System.Data.EntityClient" />

3 - Update the EF model from database (right click on the backgrounf of the model). Now it shows the table of the sdf file.

Ad Perpetuam Rei Memoriam