Changing connection string after moving database to App_Data folder?

Question

I am trying to learn mvc.net. I created a small project in which I used Model first approach. The problem is I wanted my database to be shown in App_data folder for that I followed this article:

http://www.dotnetfunda.com/articles/show/2182/how-to-embed-sql-database-in-appdata-folder

In short I detached the database and then I include the database in app_data folder. Now I am facing problem in changing the connection string, Previously I generated connection string automatically using entity framework, It was like:

<add name="KeepitrememberEntities" 
connectionString="metadata=res://*/EDM.csdl|res://*/EDM.ssdl|res://*/EDM.msl;
provider=System.Data.SqlClient;provider connection string=&quot;
data source=mypcname\SQLEXPRESS;initial catalog=Keepitremember;
integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
providerName="System.Data.EntityClient" />

and now I changed it to:

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

When I am executing the code and trying to save the value using a form, it's showing me the error in EDM.Context.cs and error is:

An exception of type 'System.Data.Entity.Infrastructure.UnintentionalCodeFirstException' occurred in Emptymvc.dll but was not handled in user code

Additional information: Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode. To continue using Database First or Model First ensure that the Entity Framework connection string is specified in the config file of executing application. To use these classes, that were generated from Database First or Model First, with Code First add any additional configuration using attributes or the DbModelBuilder API and then remove the code that throws this exception.

What I need to do now, any solution!!

Thanks for your time.

Answer 1

As far as I see, the first connection string is Entity Framework connection string, and the second one is SQL Express connection string. In an EF project, both of them should be exist in web.config file for DbContext and code generation template usage - hence they're not interchangeable (i.e. you should not change EF connection string into SQL Express one and vice versa).

Since you have changed EF connection string contained CSDL, SSDL & MSL information required by EF into SQL Express one, EF assumes the existing database metadata isn't exist anymore and trying to create a new database like Code First has, hence it triggers UnintentionalCodeFirstException after executing OnModelCreating method.

Instead of changing EF connection string in web.config, just add SQL Express connection string on the same connectionStrings element as this (you may need to define initial catalog name when required):

<connectionStrings>
    <!-- SQL Express connection string -->
    <add name="KeepitrememberConnection"
    connectionString="Data Source=mypcname\SQLEXPRESS;Initial Catalog=Keepitremember;
    AttachDbFilename=|DataDirectory|Keepitremember.mdf;Integrated Security=True;
    User Instance=True"
    providerName="System.Data.SqlClient" />

    <!-- EF connection string -->
    <add name="KeepitrememberEntities" 
    connectionString="metadata=res://*/EDM.csdl|res://*/EDM.ssdl|res://*/EDM.msl;
    provider=System.Data.SqlClient;provider connection string=&quot;
    data source=mypcname\SQLEXPRESS;initial catalog=Keepitremember;
    integrated security=True;MultipleActiveResultSets=True App=EntityFramework&quot;" 
    providerName="System.Data.EntityClient" />
</connectionStrings>

NB: The connection string names should be same with predefined names in EF model generation schema.

Related issues:

Model First with DbContext, Fails to initialize new DataBase

Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode

Entity Framework cant use the DbContext, model being created