Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generate SQL Server Express database from Entity Framework 4 model

I am able to auto-generate a SQL Server CE 4.0 *.sdf file using code-first generation as explained by Scott Guthrie here. The connection string for the same is as follows:

<add name="NerdDinners" providerName="System.Data.SqlServerCe.4.0" 
connectionString="data  source=|DataDirectory|NerdDinner.sdf"/>

However if I try to generate an mdf instead using the following connection string, it fails to do so with the following error - "The provider did not return a ProviderManifestToken string.".

<add name="NerdDinners" providerName="System.Data.SqlClient" connectionString="data 
source=|DataDirectory|NerdDinner.mdf"/>

Even directly hooking into a SQLEXPRESS instance using the following connection string fails

<add name="NerdDinners" providerName="System.Data.SqlClient" connectionString="Data 
 Source=.\SQLEXPRESS;Initial Catalog=NerdDinner;Integrated Security=True"/>

Does EF 4 only support SQL CE 4.0 for database creation from a model for now or am I doing something wrong here?

like image 849
Cranialsurge Avatar asked Dec 25 '10 04:12

Cranialsurge


2 Answers

I was able to get this connection string to work with SQL Express on the same tutorial.

<add name="NerdDinners" connectionString="Data Source=.\SQLEXPRESS; Initial Catalog=NerdDinners; AttachDbFilename=|DataDirectory|NerdDinners.mdf; Integrated Security=True; User Instance=True" providerName="System.Data.SqlClient" />

Hope this helps someone in the future.

like image 74
Trevor Avatar answered Nov 08 '22 23:11

Trevor


As a side note. If you're struggling to get the database to auto-create, it could well be because the database user you are using hasn't been given sufficient permissions. I was able to simply go the Security of the SQL Instance (not the database) and amend the Securables of the specific login so that it had the permission to Create Any Database (I didn't Grant any additional rights) and it allows EF4 to do its thing.

like image 1
Amadiere Avatar answered Nov 08 '22 22:11

Amadiere