EF4 Generate Database

Question

I am trying my hardest to find the simplest way to create a basic "model first" entity framework example. However I am struggling with the actually generation of the database, particularly the running of the SQL against the database.

Tools

• Visual Studio 2010
• SQL Server 2008 Express

Process

1. Create a new class project
2. Add a new Server-Database item (mdf) named "Database1.mdf" to the project
3. Add an empty ADO.net Entity Model
4. Create a simple entity (Person: Id, Name)
5. Generate the Script selecting the Database1 connection created for me by visual studio
6. Right click the script editor and select the "Execute SQL..." option
7. Log in to SQLEXPRESS

This is where is falls over saying it cant find a database name "Database1".

The "problem" is that the SQL server has not had Database1 attached to it. I am 100% positive that Visual Studio use to attach a database to SQLExpress when it created a new database (Step 2). This appears to not be the case any more (even the beta of VS2010 did it). Can someone confirm this? or tell me how to get this to happen?

Is there a way that I can modify the TSQL script to use an un-attached database. ie a file.

I know I can use SQL Management Studio or sqlcmd to attach the database, but I would ideally like to avoid the solutions as I would like to see the cleanest method of just using visual studio.

Ideal Solutions (in order of most prefered)

1. Get visual studio to attach the newly created database
2. Modify the generated SQL to point to file

Thanks in advance.

Felicific

I swear, from Microsoft's tooling, you would think they want you to base your model off the database schema instead of the other way around. – Cephalometer

Yes, M$ seem very heavily focused on Database first methods. Look at things like the DataGrid or every single example for the RIA services stuff. I thought 'finally they have had a crack at model first', but it isn't seamless just yet. Hopefully time will get us there. – Felicific

Answer 1

Thanks HighTechRider, the connection string created for the DataConnection in the server explorer did indeed have the "AttachDBFilename" parameter.

But this idea let me to try something else.

Following the same steps listed above I did something different at the log in to sqlexpress step. This time, in the Connect to Database Engine screen, I clicked the show more options button and went to the "Additional Connection Parameter" tab.

In here i added AttachDBFilename=c:\src\mydatabasehome\Database1.mdf;database=Database1;. Note I used the full path, not AttachDbFilename=|DataDirectory|\Database1.mdf

This then executed my sql against the database without an issue.

Then I went back to the server explorer and went to expand my database connection it so I could see my new tables, however the connection failed. After a bit of farting around, I worked out that for whatever reason, when I connected to the SQLExpress instance using the "Connect to Database Engine" screen the database file I specified in the AttachDBFilename actually made a permanent connection. Thus the previous connection string (using the AttachDBFilename parameter) was no longer valid as this database is already attached.

So I deleted that connection and created a new connection directly to the SQLServer which had a database in it named "database1" for me to select.

Now I think it has all worked out ok.

This is was a weird process, and certainly unexpected for me, so if anyone would like to explain what is going on I would certainly appreciate it. For now I have a way (even if a bit weird) of doing what I want, better solutions appreciated.

Hope this helps someone.

Felicific

Connection strings are stored in App.config, that may have been updated in one of these steps making the change seem 'permanent'. BTW don't forget to mark the answer as accepted. – Blair

Answer 2

The method I've found to consistently work (at least with VS 2010 and SQL Server (2008|2008 R2) Express) is to:

1. Create the ADO.NET Entity Model
2. Create the .mdf database, but don't add any tables to it
3. Copy the connection string from Server Explorer -> Modify Connection -> Advanced (bottom row)
4. Close the connection in Server Explorer
5. Generate the SQL script through "Generate Database from Model"
6. When the SQL script is open in the editor, click "Connect", then go Options -> Additional Connection Parameters, and paste the connection string here. I also add ";Database=DATABASENAME;" to the end of the connection string.
7. Execute SQL
8. Disconnect and restart Visual Studio
9. Now the tables should be available in Server Explorer after expanding the tree control for the .mdf file.

Probably not all of the steps are required (especially not in the exact order shown above), as I've not really optimized the process -- if it works, don't fix it. :-) The one crucial step seems to be #8, i.e. restarting Visual Studio, because otherwise Server Explorer isn't able to reopen the database.

Jerilynjeritah

The key here is step 3. Just throwing in AttachDBFilename was not enough for me. Thanks Tomi! – Elfland

Could you elaborate on step 5? I'm not quite sure what option you're referring to. – Moreville

I'm no longer using VS2010, and I use Code First nowadays when doing EF work, but in VS2012, "Generate Database from Model" is available from the popup menu when right-clicking in the .edmx model editor. Completing the wizard will create you a SQL script for creating the database. – Jerilynjeritah

Answer 3

What does your connection string look like? Have you tried putting an AttachDbFilenamein the connection string?

Blair

Answer 4

Where is the "Additional Connection Parameter" tab? I only have and "Advanced" tab and it is not clear where you added the attach statement.

Kotz

Hi Zacho, If you give more info I might be able to help, but as far as it goes, if I follow the steps listed above, I get a dialog with an "Options" button on it, which expands to reveal the "Additional Connection Parameter" tab. I am using SQL Server express 2008 if that makes a difference? – Felicific