Trying to create a multi database solution that can either target a Sql Server database or a SQLCE database depending on client needs.
But with SQLCe I'm running into an unexpected problem.
When you call CreateDatabase() you get back a ProviderIncompatibleException with the message CreateDatabase is not supported by the provider.
This causes a big problem, I could just ship a complete CE database as an embedded resource or file then copy it to the required location on the client machine when they need a new DB but then I have to effectively maintain the database schema twice.
Is there a sensible way round it or am I stuck with this approach?
It seems that currently EF provider for SQL CE does not support this feature. As a workaround, we can save the SQL CE database as some db script and execute it when we want to deploy the database at the client side.
You are correct that the next most useful approach would seem to be to generate the ddl using entity framework designer then create a ce database programatically on the client machine and apply the schema.
However, when using a sqlce connection the generated schema does not parse (too long) and cannot even be applied bit by bit after doing a split on "GO", for some reason some of the generated chunks do not parse in a sqlce connection even though
they work fine when you copy them into a query window and run them manually.
So I have had to resort to using the generated schema locally to create a sqlce database and deploy it as an embedded resource.
This works fine for me as my application ships on CD / DVD but would not like this approach if I were doing a web deployed app. not sure what my approach would be then.