1

I'm totally new to Entity Framework and have done some reading, and as a test I have put together a very brief test framework with just two small Entities. I then right clicked and selected "Generate Database from Model" which takes me to the SQL Connections page. However, none of the previous connections I have used appear in the drop down list, and when I select Create New Connection I only have the options to use 'SQL Server Compact 3.5' or 'SQL Server Database File'.

I have come across this before with SQL Express and the work around is to create my own Connection String to access the required Database. However, with me using Entity Framework to create the database, it is impossible to write an appropraite connetion string.

I therefore seem to be in a Catch 22 situation.

  1. I cannot write a connection string until the database is created.
  2. I cannot create the database from EF without accessing SQL Server (via a connection string).

Anyone come across this or can point out what I'm doing wrong. Like I said I'm totally new to EF so I apologise if this is a very basic question.

PJW
  • 5,197
  • 19
  • 60
  • 74
  • Have you considered using Code first migrations to build your database for you? Here's a great [blog post](http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx) about it. – Mark Oreta Aug 21 '12 at 01:09
  • 1
    @MarkOreta Code first is the latest and greatest, but it looks like he's already done model-first and just needs to know how to create the blank database to receive the awaiting schema. See my answer below. – pseudocoder Aug 21 '12 at 04:37

2 Answers2

1

Unless you want to use a database file in a user instance, you need to either use SQL Management Studio Express, or use the SQLCMD command line tool to create the database. I would recommend SQL Management Studio Express as it is easy to learn in my opinion.

The Entity Framework tools are intended to be used to create the database schema, not the database itself. You still need to define the file groups, security information, and other basics of creating and configuring a blank database.

See this Q/A for appropriate links:

How to create DB in SQL Express using SQL commands?

Community
  • 1
  • 1
pseudocoder
  • 4,314
  • 2
  • 25
  • 40
  • I know SQL Management Studio reasonably well as that is how I have always created Databases in the past. However I thought the Entity Framework created the database for me. I did as you say and created a blank database to take the schema from the model, but I cannot access it from VS. It says I do not have permission, but I did add myself as a user with windows authentication. This is the problem I have always had with SQL Express and why I have always used connection strings to access any DBs. However, I don't see where I can put a Connection String when using an Entity Model like this. – PJW Aug 21 '12 at 05:56
  • I found this thread http://stackoverflow.com/questions/2710654/how-to-connect-to-sqlexpress-for-entity-framework-using-visual-studio-2010-expre which is the same issue it seems, yet was never adequately answered. – PJW Aug 21 '12 at 06:07
  • 1
    OK well you have taken the first correct step, now you just need to figure out why authentication fails. The data source configuration wizard should create a valid connection string for you, and Visual Studio should use your local user credentials to connect if that's what you choose in the wizard. Did you add your Windows login as database login AND a db user? Did you give yourself dbo role access? – pseudocoder Aug 21 '12 at 13:06
0

Thanks @psuedocoder, your assistance helped me resolve the problem. The answer in the end was simple, but not intuitive to me, hence my difficulty. I thought some elaboration on the answer might help others who are equally new to Entity Framework as me.

From Visual Studio

  1. Create your EF model in Visual Studio.
  2. Right click the model canvas and select 'Generate Database from Model'.
  3. You are then asked to select the database connection. Select 'New Connection'.
  4. In the 'Add Connection' dialog box, rather than 'Browse' for an existing database, just simply type the name of your new database in the Database text box and Click Connect.
  5. When you try to connect you will get a warning saying the database does not exist, but you will be asked if you want VS to 'Create It'. Select Yes.

As @psuedocoder states this does not actually create the database in SQL Server, but it does create an object in your VS soluton which contains the TSQL script required in order to create your database.

From SSMS

  1. Go to SQL Server Management Studio and create a blank database of the same name used in step 4 above.
  2. In SSMS select open file and navigate to the windows folder containing your VS solution files.
  3. Open the TSQL script file. This will have a DB Script icon and have an .edmx extension.
  4. Click Execute, and you will have your new database created from your EF model.
PJW
  • 5,197
  • 19
  • 60
  • 74