How should I edit an Entity Framework connection string?

Question

I recently had to edit my app.config file to change the connection string for an Entity Framework data model (.edmx file). But I'd like to know: Is there a way to edit the EF connection string using the designer?

The original connection string is auto-generated by the Entity Data Model wizard. The connection string then cannot be changed - it remains disabled in the properties window of the .edmx designer. I like to avoid editing XML by hand if I can (in app.config), so I was wondering if there is a way to get back into the original wizard screen to make connection string changes and let the tool edit the app.config file for me.

Propinquity

Answer 1

If you remove the connection string from the app.config file, re-running the entity Data Model wizard will guide you to build a new connection.

Daybreak

Excellent. This is exactly the answer I was looking for. Just to be safe I commented out the existing string (rather than delete it), saved the app.config changes, right-clicked the designer and chose Update Model From Database. The wizard then let me include the sensitive info (uid & pwd) in the connection string. Thanks! – Propinquity

If you're doing model-first then right-click the designer and click 'Generate Database from Model' instead – Endoergic

This worked for me as well, except I had to update the App.config file, then restart VS.NET 2012. It was not detecting that the App.config file changed. – Anomalistic

I had to explicitly call save on the app.config file for the designer to recognise the connection string had been deleted. – Plebiscite

Thanks, @Rossco. I had been editing app.config in an external editor. Even though I have VS2010 set to automatically read in externally-changed files, I still need to open up app.config in VS and save it there. Previous to seeing your comment, I was restarting VS to force the change into effect. – Biggs

An alternative to restarting VS is to 1. comment-out the connection string in app.config 2. rebuild the project that contains the .edmx 3. right-click the .edmx design surface and Update Model From Database... which should bring up the Connection String wizard. – Minter

you may need to delete the context.tt file that was originally there in order to do this or it may yell out you for having a duplicate. – Slusher

This was an excellent resolution, especially @Minter suggesting that I just comment out the connection in the app.config and then Update the Model. This works great in those weird cases where Entity Framework somehow gets disjointed with respect to the original connection. For example, I was accessing objects in other schemas than what the connection logged in as, and the usual fixes (opening the connection in Server Explorer BEFORE updating the model) stopped working. This got things back on track without rebuilding the entire model! – Laval

Answer 2

No, you can't edit the connection string in the designer. The connection string is not part of the EDMX file it is just referenced value from the configuration file and probably because of that it is just readonly in the properties window.

Modifying configuration file is common task because you sometimes wants to make change without rebuilding the application. That is the reason why configuration files exist.

Rightist

+1 for reminding that : if they put it in a configuration file, it means that they want you to be able to change it. I was mainly concerned about the fact that my changes could be overriden – Lifeanddeath

You should go and change the connection string in the app config of the project edmx belongs to. Not the config of top level application (which is used when it runs). o_0 – Basir

Answer 3

You normally define your connection strings in Web.config. After generating the edmx the connection string will get stored in the App.Config. If you want to change the connection string go to the app.config and remove all the connection strings. Now go to the edmx, right click on the designer surface, select Update model from database, choose the connection string from the dropdown, Click next, Add or Refresh (select what you want) and finish.

In the output window it will show something like this,

Generated model file: UpostDataModel.edmx. Loading metadata from the database took 00:00:00.4258157. Generating the model took 00:00:01.5623765. Added the connection string to the App.Config file.

Aldoaldol

I am having a similar issue but when I comment out the connection string, update the model, choose a new connection string, my context.cs class within the model gets emptied out. Not sure why its doing this unless its just failing to generate the context class? – Syman

It should not do that. May be your T4 templating tool is corrupted. – Aldoaldol

I agree, it "shouldnt". However, Ive lost count of the number of times when making more than a few non-trivial changes to the underlying schema, that I had eventually to simply blow away the model and create a new one. Maybe with EF7 and its removing of the edmx file might help things. Either way, thats I what I did this time as well, just highlighted everything in the model, hit delete and brought those items back in. – Syman

Answer 4

Follow the next steps:

1. Open the app.config and comment on the connection string (save file)
2. Open the edmx (go to properties, the connection string should be blank), close the edmx file again
3. Open the app.config and uncomment the connection string (save file)
4. Open the edmx, go to properties, you should see the connection string uptated!!

Flickertail

Answer 5

Open .edmx file any text editor change the Schema="your required schema" and also open the app.config/web.config, change the user id and password from the connection string. you are done.

Flummery

Yeap, this did it. – Skedaddle

What does this mean? Schema is an xml element. It cannot be changed to "your required schema" – Infection