3

I'm developing a class library that deals with an exiting db using EF. I want to avoid the consumer of the class library (and .exe or a web site) to have in the *.config file the Entity connection string. I want the connection string set a run-time.

How do I set the connection string with Database First approach? There is no constructor overload that takes a connection string and when I created one (in a separate partial class) I got an "UnintentionalCodeFirstException".

I have reviewed already the following links:

Community
  • 1
  • 1
Felipe Lopez
  • 61
  • 1
  • 4
  • Check out this post: http://stackoverflow.com/questions/12737289/ef-5-changing-connection-string-at-runtime – lintmouse Feb 20 '13 at 15:45
  • Please post the code you are using that is giving you the exception (edit your question to include this code, do not post it as an answer or comment). I assume you are using your Database First method and generating POCO's with a code generation template? – Erik Funkenbusch Feb 20 '13 at 18:43
  • Did you use the method described here? http://msdn.microsoft.com/en-US/data/jj206878 Including using the code generator option? Did it create a set of POCO classes, or is it a single file with all your classes under the .edmx file? – Erik Funkenbusch Feb 20 '13 at 22:35

1 Answers1

6

There is a constructor on DbContext that takes a DbConnection, and you need to use an EntityConnection object for it:

SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

// Set the properties for the data source.
sqlBuilder.DataSource = "server name";
sqlBuilder.InitialCatalog = "database name";
sqlBuilder.IntegratedSecurity = true;

// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();

var entityBuilder = new EntityConnectionStringBuilder();

// Initialize the EntityConnectionStringBuilder.
//Set the provider name.
entityBuilder.Provider = "System.Data.SqlClient";

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;

// Set the Metadata location.
entityBuilder.Metadata = @"res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";

using(var context = new YourDbContext(entityBuilder.ToString())){
    //do stuff here
}

The important thing to note is the metadata part - "Model1" obviously needs to be replaced for your model name.

Ref: http://msdn.microsoft.com/en-us/library/bb738533.aspx

EDIT 20/02/2013 22:25

So as an addition you'll need to extend the created DbContext class with a partial class that adds a constructor to support the above code, like this:

public partial class YourDbContext
{
    public YourDbContext(string connection) : base(connection) {}
}

This class needs to be in the same namespace as the DbContext that is generated by the entity framework wizard.

Matt Whetton
  • 6,616
  • 5
  • 36
  • 57
  • As mentioned before, If I do that I get an "UnintentionalCodeFirstException" (prob. because it is "Database first" approach) – Felipe Lopez Feb 20 '13 at 17:28
  • I understand the code, but as mentioned, I don't have such constructor overload for the context that would receive a connection. Is not there... Again, I created the model using the wizard and "Generate from Database", could that be the reason for the missing constructor? Anyway the question remains. I'm using VS2012, EF 5. – Felipe Lopez Feb 20 '13 at 20:31
  • Ok, I added an extra bit to my answer - you essentially need to add a partial class for your DbContext which adds a constructor that supports this behavior. – Matt Whetton Feb 20 '13 at 22:29
  • Your solution worked "partially": the constructor took the connection object as proposed and did some data access, then a bit later, it throws an exception: "There is already an open DataReader associated with this Command which must be closed first.". I understand what the error means, but don't know what to change. Specially because if I use the parameterless constructos, all the data access runs perfectly, without exceptions. – Felipe Lopez Feb 21 '13 at 19:49
  • Can you post the code that's causing the exception - I believe this is normally relating to deferred execution or something like that. You might need to wrap DbConnection in a using statement - but I'm not sure. – Matt Whetton Feb 22 '13 at 07:58
  • I can't post the code, sorry. The using is there. The point is why it would throw that exception when I use a different constructor overload... – Felipe Lopez Feb 22 '13 at 13:38
  • Well it is likely something to do with the fact that we're passing in an existing connection - passing true means EF should be managing this but it clearly isnt! I've made another amend above which will hopefully sort out your issue. Notice replacing using the connection object with a entity connection string. – Matt Whetton Feb 22 '13 at 13:59