Generating a SQL Server CE Database Schema from a SQL Server Database using Entity Framework

In a previous entry, I described how to programmatically create (& destroy) a SQL CE dB for integration testing using NUnit. Since getting that working, I ran into a couple of other problems which I've more or less solved so I thought I'd write those up. To begin with though, this is a prequel post describing how to obtain the SQL script to create the SQL CE dB.

If you happen to be working exclusively with CE, then you'll already have your schema file. In my case, I'm using SQLExpress and as this is experimental work, I created my dB by hand. However, using the EF, it's pretty easy to obtain the schema and have the EF wizard generate the CE schema. This is important as there are differences in the dialect of SQL used by SQL Express and SQL CE and it's easier to have a tool handle those, though it doesn't do all of them.

The basic flow is to generate an EF model (EDMX) file from the existing SQL Express database and then use the 'Generate database from model' functionality. It is at this point that the target SQL dB can be chosen, i.e., SQL Server, SQL Server CE or some others.

To create a model requires adding a 'New Item' of type 'ADO.NET Entity Data Model' to a VS project so first a new dummy project needs creating. This is where it gets a little complicated as not any type of project will do. I'm working with CE 4 and require a schema for that version of the dB (though creating one for 3.5 works but I like to have things as close to ideal as possible). Due to this constraint, it is necessary to chose a Web type project as for some reason the VS2010 integration provided by EF only supports the generation of CE 4 dBs for Web projects. If a simple C# Windows Console project is selected, then you're limited to CE 3.5. Thus, the simplest project type is the 'ASP.NET Empty Web Application' as shown below:

Having done this, next add a new item of type ADO.NET Entity Data Model as below. NOTE: The project will have to reference the Entity Framework assemblies. The easiest way to do this (and the one most people are probably using) is to use the NuGet package.

Then follow the wizard.

Selecting "Generate from database":

Choose your SQLExpress (or SQL Server) dB but uncheck the "Save entity connection settings in Web.Config as:" as we're converting to SQLCE so want to minimize anything related to other types of SQL Server.

Finally, select the SQL elements you require. In this example, only the existing tables were selected. As this is generating the EF model from an existing database, no SQL file is generated, just the model for which the diagram is shown, i.e.:

The next phase is to generate the SQL from the model (which was generated from the hand crafted db) but to make sure the SQL that's generated is compliant with SQL CE.

To generate the schema, right click and select "Generate model from database..."

This brings up the "Generate database" wizard which is very similar to the previously used "Entity Data Model" wizard used to create the model. From here, choose the "New Connection" option which pops up another set of dialogs. On the first, choose the type of data source as "Microsoft SQL Server Compact 4.0".

Clicking on continue then leads to the next dialog where you need to create a dB.

Ok-ing this leads back to the "Generate database wizard".

This time, check the "Save entity connection settings in Web.Config" checkbox. This information will be useful later (to be covered in a different post). Clicking "Next", the SQL is generated and present in the wizard.

This can be copied & pasted directly from here or pressing "Finish" will save the SQL to the file indicated at the top of the dialog box. This file is added to the project. The following prompt will appear when "Finish" is pressed.

This doesn't really matter as this is a throw away project but having the updated schemas may be useful so go with "Yes".

The SQL can now be used to configure an empty SQL CE 4.0 database. The easiest way is to open the SQL file and right-click selecting the "Execute SQL" menu item.

This brings up the SQL Server dialog from which if "New Database" is selected, a CE 4 one can be specified.

Having specified a location and pressed "Ok", the SQL script is executed. As can be seen below, this is not without errors. However, this isn't anything to worry about as the errors are to do with dropping tables and indices that currently don't exist as it's a newly created dB. Performing the same steps but missing out the creation of the dB file as it already exists sees the SQL script execute flawlessly.

The final picture shows the newly created database in VS2010's Server Explorer demonstrating that the tables were indeed created.

The basis for this post is my experimentation on using NUnit to programmatically test some dB based functionality. If a single instance of a database suffices for all your tests and you can execute the SQL by hand as above, then you can follow these steps. In many cases, I want to a fresh database per test so I need to automate the running of the SQL Script combined with the creation and destruction of the underlying database. The creation and deletion aspect were covered in a previous post but the next step will have to wait until a later one.