none
Connection string magic for Entity framework? RRS feed

  • Question

  • I am trying to connect a new MVC project using Entity Framework to an existing database. When I create the ADO.NET Entity Model it gets the model from the database just fine. 

    However, when I create the controller, which is just stamped out by the IDE, there appears no hope!

    It stamps out the following connection strings in the web.config.

       
        <add name="FUST_EFEntities" connectionString="metadata=res://*/FUST_EF.csdl|res://*/FUST_EF.ssdl|res://*/FUST_EF.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DSC-YOGA\SQLEXPRESS;initial catalog=FUST_EF;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
          providerName="System.Data.EntityClient" />
        <add name="LevelContext" connectionString="Data Source=(localdb)\v11.0; Initial Catalog=LevelContext-20130223145635; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|LevelContext-20130223145635.mdf"
          providerName="System.Data.SqlClient" />
    

    The first obvious problem is that the LevelContext is pointing to another database. Apparently there is no context between the EntityModel connection and the context connection and the context does not prompt for a connection string. 

    So, I modified the connection string to:

        <add name="FUST_EFEntities" connectionString="metadata=res://*/FUST_EF.csdl|res://*/FUST_EF.ssdl|res://*/FUST_EF.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DSC-YOGA\SQLEXPRESS;initial catalog=FUST_EF;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
          providerName="System.Data.EntityClient" />
        <add name="LevelContext" connectionString="Data Source=DSC-YOGA\SQLEXPRESS;Initial Catalog=FUST_EF;Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|LevelContext-20130223145635.mdf"
          providerName="System.Data.SqlClient" />
    


    and I get the following error:

    {"An exception occurred while initializing the database. See the InnerException for details."}

    Inner exceptioon {"The underlying provider failed on Open."}

    Stack Trace  

       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
       at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)
       at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.GetEnumerator()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at FUSTA_MVC.Controllers.LevelController.Index() in c:\Users\David\Documents\Visual Studio 2012\Projects\FUSTA_MVC\FUSTA_MVC\Controllers\LevelController.cs:line 21
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.InvokeSynchronousActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
       at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
       at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
       at System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()

    Any help with the magic would be appreciated.

    Thanks

    David Coulter

    dscoulter@nchfa.com

    Saturday, February 23, 2013 8:07 PM

Answers

  • I noticed the reference to the .mdf filename in the connection string. This is not the correct location and I don't see a need for this. Perhaps it is needed for (Localdb). So, I removed that part of the connection string. They now look like this:

        <add name="FUST_EFEntities" connectionString="metadata=res://*/FUST_EF.csdl|res://*/FUST_EF.ssdl|res://*/FUST_EF.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DSC-YOGA\SQLEXPRESS;initial catalog=FUST_EF;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
        <add name="LevelContext" connectionString="Data Source=DSC-YOGA\SQLEXPRESS;Initial Catalog=FUST_EF;Integrated Security=True;" providerName="System.Data.SqlClient" />
    

    The form comes up but there is no data listed. There is data in the database! If I enter a record, it saves but where? The record is not in the table. 

    Ok, I went to grab a screen shot and now i see where the data went. There is now an entire new set of tables all ending in "s". Why is this. The model was created from the existing tables using EF. Why would it go and make new tables?

    I am so confused. Do my tables all have to end in "s" for this stuff to work correctly?  Where is the large red warning label?

    Thanks for any help you can provide.

    • Marked as answer by David Coulter Tuesday, February 26, 2013 1:20 AM
    Sunday, February 24, 2013 1:17 PM

All replies

  • I noticed the reference to the .mdf filename in the connection string. This is not the correct location and I don't see a need for this. Perhaps it is needed for (Localdb). So, I removed that part of the connection string. They now look like this:

        <add name="FUST_EFEntities" connectionString="metadata=res://*/FUST_EF.csdl|res://*/FUST_EF.ssdl|res://*/FUST_EF.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DSC-YOGA\SQLEXPRESS;initial catalog=FUST_EF;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
        <add name="LevelContext" connectionString="Data Source=DSC-YOGA\SQLEXPRESS;Initial Catalog=FUST_EF;Integrated Security=True;" providerName="System.Data.SqlClient" />
    

    The form comes up but there is no data listed. There is data in the database! If I enter a record, it saves but where? The record is not in the table. 

    Ok, I went to grab a screen shot and now i see where the data went. There is now an entire new set of tables all ending in "s". Why is this. The model was created from the existing tables using EF. Why would it go and make new tables?

    I am so confused. Do my tables all have to end in "s" for this stuff to work correctly?  Where is the large red warning label?

    Thanks for any help you can provide.

    • Marked as answer by David Coulter Tuesday, February 26, 2013 1:20 AM
    Sunday, February 24, 2013 1:17 PM
  • Ok, I found a site that recommended turning pluralization off.  However, I did this and got the same results. Now what.

    Thanks again still looking.

    Monday, February 25, 2013 12:44 AM
  • Hi,

    What database does the LevelContext connection string connect?

    Monday, February 25, 2013 8:44 AM
  • It connects to a database called FUST_EF

    <add name="LevelContext" connectionString="Data Source=DSC-YOGA\SQLEXPRESS;Initial Catalog=FUST_EF;Integrated Security=True;" providerName="System.Data.SqlClient" />

    It is the same database that Entity framework built the model from. 

    Thanks

    David

    Monday, February 25, 2013 12:13 PM