none
Set BaseConnectionString for DbContext RRS feed

  • Question

  • I just started working with DbContext.  My set-up requires two DbContext classes--one that connects to a database that contains a list of users and database names (DatabasePathEntities) and one that will actually connect to the correct database name for the user logged into the application (FileEntities).  I set up an edmx file for each one and pulled in the required entities.  I then generated the DbContext classes using the EF 4.x DbContext Generator.  Because I needed to determine the correct connection string for my FileEntities object, I included a class with the following code:

     public partial class FileEntities : DbContext
     {
         public FileEntities(string dbName)
         {
             this.Database.Connection.ConnectionString = GetConnectionString(dbName);
         }
    
         private string GetConnectionString(string dbName)
         {
             try
             {
                 DatabasePathEntities context = new DatabasePathEntities();
                 DatabasePath dbPath = context.GetDatabasePath(dbName).FirstOrDefault();
    
                 string strDatabaseName = dbPath.DatabaseName; 
                 string strServerName = dbPath.ServerName;
                 string strConn = "Data Source=" + strServerName + ";Initial Catalog=" + strDatabaseName + ";Integrated Security=True";
                 return strConn;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }
     }

    The autogenerated constructor for DatabasePath Entities is as follows:

     public DatabasePathEntities()
         : base("name=DatabasePathEntities")
     {  }

    When I run the GetConnectionString method, it crashes on context.GetDatabasePath with a System.InvalidOperationException--"No connection string named 'DatabasePathEntities' could be found in the application config file."  From what I have determined, there is a property called BaseConnectionString that is set to "Data Source=.\\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True"  Where is this set?  I can't find it in my App.Config file, so I assume it is in the Machine.Config file.  Is there a way to use <clear/> to remove this?  I have rewritten the <parameter> tag within the <defaultConnectionFactory> tag to show my database, but that had no effect.  My App.Config file looks like the following:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.1.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </configSections>
      <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="Data Source=MyServer; Integrated Security=True; MultipleActiveResultSets=True" />
            <!--<parameter value="Data Source=.\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True" />-->
          </parameters>
        </defaultConnectionFactory>
      </entityFramework>
      <connectionStrings>
        <add name="DatabasePathEntities" connectionString="metadata=res://*/DatabasePathModel.csdl|res://*/DatabasePathModel.ssdl|res://*/DatabasePathModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MyServer;initial catalog=MyDB;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
      </connectionStrings>
    </configuration>

    So, I have no idea where this BaseConnectionString is coming from or why my application cannot find the "DatabasePathEntities" connection string.  Thanks in advance for your help!


    Christine A. Piffat

    Friday, July 27, 2012 9:57 PM

Answers

  • Hi Christine,

    I surmise you use Database First at first. Then you want to add or modify some code in the generated class FileEntities or add some connection strings in app.config/web.config, which we call code first. Am I right?

    If so, we should know something about Database First and Code First. Please take a look at this article: http://blog.oneunicorn.com/2012/02/26/dont-use-code-first-by-mistake/

    Here is the important part I think:

    With the Database First approach an EDMX file is created by the EF Designer and (usually) embedded in the application assembly. This EDMX file contains all the information required to map between the entity classes and the database. When a Database First application is run the DbContext must load this EDMX file so that it knows how to map between entity classes and the database. This is done through a special EF connection string which is created for you and added to your config file by the EF Designer. The connection string will look something like this:

    <connectionStrings>

        <add name="NorthwindEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=Northwind;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

      </connectionStrings>

    Notice that this connection string contains references to “CSDL”, “MSL”, and “SSDL” metadata. This is the contents of the EDMX that has been embedded in the application assembly.

    With the Code First approach there is no EDMX file. There is nothing in the VS project that contains the mapping information from entity classes to database…except for the code itself. When you use the Database First approach with the DbContext T4 templates two things are setup to protect against things going wrong in this way.

    First, the generated context class makes a call to the base DbContext constructor specifying the name of this connection string. For example:

    public MyEntities()
    : base("name=MyEntities")
    {
    }

    This tells DbContext to find and use the "MyEntities" connection string in the config—i.e. the one created by the designer as described above.

    Using "name=" means that DbContext will throw if it doesn’t find the connection string—it won’t just go ahead and create a connection by convention. Be very careful if you change this call to the base constructor. Make sure that whatever change you make DbContext is still able to find the correct connection string containing the information from your EDMX. If DbContext finds a non-EF connection string or creates a connection string by convention then it will start using Code First to create a model for that connection.

    The second thing that happens is that the OnModelCreating is overridden in the generated context and made to throw:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    throw new UnintentionalCodeFirstException();
    }

    To see why this happens consider how OnModelCreating is used; OnModelCreating is a way of making calls to the Code First DbModelBuilder fluent API. In other words, it’s a way of setting up Code First mappings. This means that OnModelCreating will never be called when using the Database First approach. It will never be called because all the mappings already exist in the EDMX and so Code First and the DbModelBuilder are never used.

    In a word, if you use Databse First in the beginning, the connection string you are using now is not correct. For my personal guess, it lacks references to “CSDL”, “MSL”, and “SSDL” metadata in connection string.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 2, 2012 6:11 AM

All replies

  • Hi Christine,

    Could you please provide the implementation of GetDatabasePath method?

    In addition, why don't you use this directly (after you added a connection string in App.Config)?

    public class FileEntities: DbContext
    {
        public FileEntities()
            : base("FileEntities")//FileEntities is the name of a connection string in App.Config
        {
        }
    }

    Monday, July 30, 2012 8:31 AM
  • Unfortunately, I am unable to use the App.Config when creating FileEntities because the connection string depends on the database name information passed in by the user.  Different users are on different databases.  As a result, I have to generate that connection string from the database name and other information stored in a separate database, the one using the "DatabasePathEntities" connection string. 

    The method for GetDatabasePath was generated with the DbContext Generator. 

     public virtual ObjectResult<DatabasePath> GetDatabasePath(string dbName)
     {
         ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace.LoadFromAssembly(typeof(DatabasePath).Assembly);
    
         var dbNumberParameter = dbName != null ?
             new ObjectParameter("DbName", dbName) :
             new ObjectParameter("DbName", typeof(string));
    
         return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<DatabasePath>("GetDatabasePath", dbNameParameter);
     }

    I stepped through the code, and it crashes on the first line.  I tried calling the DatabasePathEntities directly instead of through my FileEntities and have the same error.   From what I have read since posting this, it seems the problem has to do with using a stored procedure, which is a requirement due to the complexity of what I have to generate.  I know I COULD put that code in the application, but then I would be pulling back significant data just to get this one connection string created.  I have used stored procs with EF 4.0, but I am wondering if it is just not possible to use them with EF 4.3 or if there is something more I need to do with the autogenerated code. 


    Christine A. Piffat

    Monday, July 30, 2012 2:45 PM
  • Just to test my theory that the stored proc could be the problem, I added a random table to my model, generated the DbContext classes, put them in the Entities project, and still have the same error.  I have also tried a number of other permutations, but I continue to get the same System.InvalidOperationException--"No connection string named 'DatabasePathEntities' could be found in the application config file." 

    So, my question is: where is the App.config that contains this Connection String supposed to be located?  Should it be in the project that contains the model, the one that contains the DbContext, or the one that contains the code calling the model project (in this case, a WCF service project)?


    Christine A. Piffat

    Tuesday, July 31, 2012 2:35 PM
  • Hi Christine,

    That's strange. You can try to use this:

    public DatabasePathEntities()
         : base("DatabasePathEntities")
     {  }

    Since I found a link about this, you can take a look at: http://stackoverflow.com/questions/10978017/no-connection-string-named-myapplicationentities-could-be-found-in-the-applica

    Wednesday, August 1, 2012 9:02 AM
  • I made that change, but now I have a different set of errors. 

    When I looked at the Database properties in Debug, I still had a BaseConnectionString that pointed to SQLEXPRESS: "Data Source=.\SQLEXPRESS;Initial Catalog=DatabasePathEntities;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFrameworkMUE"

    When I tried to pull any data, I got an UnintentionalCodeFirstException on the OnModelCreating: "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."

    I looked at a couple links on StackOverflow and edited the connection strings to remove all the EF model information (so they were just regular connection strings).  I then copied them to my Web host.  Now, the data source and initial catalog are correct on the connection string, but it still includes "Application Name=EntityFrameworkMUE" when I run it.  Unfortunately, it also continues to hit the OnModelCreating with the same UnintentionalCodeFirstException. 

    Do I need to add something to OnModelCreating?  If I comment out the throw statement in there, I still have an error.  In the case of pulling information from a table, it had to do with the table not having a primary key.  In the case of the stored procedure, I have an error "The FunctionImport 'MyStoredProc' could not be found in the container 'DatabasePathEntities'." 

    Finally, if I look at the properties of my DatabasePathEntities object right after it is created and no exception has been thrown yet, I find the following error listed in the base.Database.Connection.ServerVersion property:  "Invalid operation. The connection is closed."  The base is the DbContext object. 

    Are my errors due to having a database initially and pulling in the stored procedures prior to setting up the DbContext?  I'm not sure how else to do this efficiently.


    Christine A. Piffat

    Wednesday, August 1, 2012 4:07 PM
  • Hi Christine,

    I surmise you use Database First at first. Then you want to add or modify some code in the generated class FileEntities or add some connection strings in app.config/web.config, which we call code first. Am I right?

    If so, we should know something about Database First and Code First. Please take a look at this article: http://blog.oneunicorn.com/2012/02/26/dont-use-code-first-by-mistake/

    Here is the important part I think:

    With the Database First approach an EDMX file is created by the EF Designer and (usually) embedded in the application assembly. This EDMX file contains all the information required to map between the entity classes and the database. When a Database First application is run the DbContext must load this EDMX file so that it knows how to map between entity classes and the database. This is done through a special EF connection string which is created for you and added to your config file by the EF Designer. The connection string will look something like this:

    <connectionStrings>

        <add name="NorthwindEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=Northwind;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

      </connectionStrings>

    Notice that this connection string contains references to “CSDL”, “MSL”, and “SSDL” metadata. This is the contents of the EDMX that has been embedded in the application assembly.

    With the Code First approach there is no EDMX file. There is nothing in the VS project that contains the mapping information from entity classes to database…except for the code itself. When you use the Database First approach with the DbContext T4 templates two things are setup to protect against things going wrong in this way.

    First, the generated context class makes a call to the base DbContext constructor specifying the name of this connection string. For example:

    public MyEntities()
    : base("name=MyEntities")
    {
    }

    This tells DbContext to find and use the "MyEntities" connection string in the config—i.e. the one created by the designer as described above.

    Using "name=" means that DbContext will throw if it doesn’t find the connection string—it won’t just go ahead and create a connection by convention. Be very careful if you change this call to the base constructor. Make sure that whatever change you make DbContext is still able to find the correct connection string containing the information from your EDMX. If DbContext finds a non-EF connection string or creates a connection string by convention then it will start using Code First to create a model for that connection.

    The second thing that happens is that the OnModelCreating is overridden in the generated context and made to throw:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    throw new UnintentionalCodeFirstException();
    }

    To see why this happens consider how OnModelCreating is used; OnModelCreating is a way of making calls to the Code First DbModelBuilder fluent API. In other words, it’s a way of setting up Code First mappings. This means that OnModelCreating will never be called when using the Database First approach. It will never be called because all the mappings already exist in the EDMX and so Code First and the DbModelBuilder are never used.

    In a word, if you use Databse First in the beginning, the connection string you are using now is not correct. For my personal guess, it lacks references to “CSDL”, “MSL”, and “SSDL” metadata in connection string.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 2, 2012 6:11 AM