How can I generate the database from .edmx file in Entity Framework?

Question

I have had to suddenly switch to working on Code First Entity Framework 4.1. I started off not knowing anything about this framework but in the last 8 hrs I am now much more comfortable having read blogs and articles.

This blog in particular is one of the best blogs I have seen so far on the topic but the steps given do not match with my experience. In particular, I need more focus on the 3rd and 4th steps ('Create the Model' and 'Swap to DbContext Code Generation', respectively). I am unable to generate the database from my defined EntitySet. I am getting the SQL and I can execute but I'm getting the following error:

Could not locate entry in sysdatabases for "MyBD" database . No entry found with that name. Make sure that the name is entered correctly entity framework.

If I execute the SQL again, I get the same error following the names of tables that already exist in database.

If refresh the DataConnection in Server Explorer, there are no such tables created as I defined in Entity Framework.

How can I get rid of this error and successfully generate the tables in my .edmx?

Also I am unable to find the option on right-click in Solution Explorer to "Generate Database" from selected class file that has the context class inherited from the DBContext object. I installed the Entity framework 4.1 from Microsoft, so it should appear there... How can I get the Generate Database option?

Answer 1

Here's the definitive guide from MSDN on

How to: Generate a Database from a Conceptual Model (Entity Data Model Tools) [.edmx] file.

Copy/Pasting here just for the sake of completeness:

To generate a database from a conceptual model

1 - Add an .edmx file to your project.

For information about adding an .edmx file to a project, see How to: Create a New .edmx File (Entity Data Model Tools) and How to: Add an Existing .edmx File (Entity Data Model Tools).

2 - Build the conceptual model.

You can use the ADO.NET Entity Data Model Designer (Entity Designer) to create entities and relationships or you can manually edit the .edmx file to build a conceptual model. For more information, see Implementing Advanced Entity Framework Features and CSDL, SSDL, and MSL Specifications.

NoteNote When you build the conceptual model, warnings about unmapped entities and associations may appear in the Error List. You can ignore these warnings because the Create Database Wizard will add the storage model and mapping information (see step 3).

3 - Right-click an empty space on the Entity Designer surface and select Generate Database from Model.

The Choose Your Data Connection Dialog Box of the Generate Database Wizard (Entity Data Model Tools) is displayed.

4 - Click the New Connection button or select an existing connection button from the drop-down list to provide a database connection.

You must supply a database connection so that column types for the target database can be determined based on property types in your model, and so that connection string information can be added to your application. Note that supplying connection information does not initiate data definition language (DDL) generation.

5 - Click Next.

The Create Database Wizard generates data definition language for creating a database. The generated DDL is displayed in the Summary and Settings Dialog Box (Generate Database Wizard).

6 - Click Finish.

Upon completion, the Create Database Wizard does the following:

Generates the store schema definition language (SSDL) and mapping specification language (MSL) that correspond to the provided conceptual schema definition language (CSDL). The .edmx file is updated with the generated SSDL and MSL. Note that the wizard overwrites existing SSDL and MSL.

Saves the generated DDL in the location specified in the Save DDL As text box. For more information about the generated DDL, see Database Generation Rules (Generate Database Wizard).

NoteNote If a storage model is already defined when you run the Create Database Wizard, the generated DDL will contain a DROP TABLE statement and DROP CONSTRAINT statement for each EntitySet and each AssociationSet (respectively) that are inferred from the storage model.

Adds connection string information to your App.config or Web.config file.

It is important to note that the Create Database Wizard does not execute the generated DDL. To create the database schema that corresponds to your conceptual model, you must execute the generated DDL independently (for example, execute the DDL in SQL Server Management Studio).

Answer 2

If you are creating the database from the model, you need to select the empty model first. Here are the other steps to create db:

1. Select new connection
2. Set Server name: if you installed it, just type . to select default. You can also try (local)
3. Set new database name
4. Copy DDL script to your SQL server management studio's query screen
5. Run the script to create your db

After running the script, you will have the initial table. Config file will have connection string named as container name.

Now, when you want to switch to code generation similar to example with TT files, you can right click and add code generation. It will create partial class for the entity model and one file for dbcontext. Similar to this:

 using System;
    using System.Collections.Generic;

    public partial class Contact
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Context will have only one table.

 public partial class PersonModelContainer : DbContext
    {
        public PersonModelContainer()
            : base("name=PersonModelContainer")
        {
        }

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

        public DbSet<Contact> Contacts { get; set; }
    }

You dont need TT model. You can add these files directly. You need one context class inheriting from DbContext and one partial class file for each type of entity. If you make a change to model, EF will detect that. You need to define Db initializer. For the sample demo on that webpage, you can add initializer to another method. If it is a web project, you add this init function to Global.asax->application_Start for the initial development. You have different options for initializers. I use drop and create for initial development.

 static void InitDbCheck()
        {
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<PersonModelContainer>());
            using (var db = new PersonModelContainer())
            {
                //accessing a record will trigger to check db.
                int recordCount = db.Contacts.Count();
            }
        }

        static void Main(string[] args)
        {



            using (var db = new PersonModelContainer())
            {
                // Save some data
                db.Contacts.Add(new Contact { Name = "Bob" });
                db.Contacts.Add(new Contact { Name = "Ted" });
                db.Contacts.Add(new Contact { Name = "Jane" });
                db.SaveChanges();

                // Use LINQ to access data
                var people = from p in db.Contacts
                             orderby p.Name
                             select p;

                Console.WriteLine("All People:");
                foreach (var person in people)
                {
                    Console.WriteLine("- {0}", person.Name);
                }

                // Change someones name
                db.Contacts.First().Name = "Janet";
                db.SaveChanges();
            }
        }