We’ve written our code.  Now let’s run the application. 

But what about the database?

We don’t have a database yet – and haven’t needed one so far because our “code first” development workflow hasn’t required us to have one to define and use our model classes. 

But we will need a database when we actually run our application and want to store our Dinner and RSVP objects.  We can create the database one of two ways:

Manually create and define the schema ourselves using a database tool (e.g. SQL Management Studio or Visual Studio)

Automatically create and generate the schema directly from our model classes using the EF Code-First library

This second option is pretty cool and is what we are going to use for our NerdDinner application.

Configuring our Database Connection String

To begin with, we’ll setup a connection-string to point to where we want our database to live.  We’ll do this by adding a “NerdDinners” connection-string entry to our application’s web.config file like so: 

 

By default, when you create a DbContext class with EF code-first, it will look for a connection-string that matches the name of the context-class.  Since we named our context class “NerdDinners”, it will by default look for and use the above “NerdDinners” database connection-string when it is instantiated within our ASP.NET application.

Taking advantage of SQL CE 4

You can use many different databases with EF code-first – including SQL Server, SQL Express and MySQL.

Two weeks ago I blogged about the work we are also doing to enable the embedded SQL CE 4 database engine to work within ASP.NET.  SQL CE 4 is a lightweight file-based database that is free, simple to setup, and can be embedded within your ASP.NET applications.  It supports low-cost hosting environments, and enables databases to be easily migrated to SQL Server.

SQL CE can be a useful option to use when you are in the early stages of defining (and redefining) your model layer – and want to be able to quickly create and recreate your database as you do so.  We’ll use SQL CE 4 to begin with as we develop our NerdDinner application.  We can later optionally change the connection-string to use SQL Express or SQL Server for production deployment – without having to modify a single line of code within our application.

The connection-string I specified above points to a NerdDinners.sdf database file, and specifies the SQL CE 4 database provider.  In order for this to work you need to install SQL CE 4 – either via the Standalone SQL CE Installer or by installing WebMatrix (which includes it built-in).  SQL CE 4 is a small download that only takes a few seconds to install.

Important: In the connection-string above I’m indicating that we want to create the NerdDinners.sdf file within the |DataDirectory| folder – which in an ASP.NET application is the \App_Data\ folder immediately underneath the application directory.  By default the “Empty ASP.NET MVC Web Application” project template does not create this directory.  You will need to explicitly create this directory within your project (right click on the project and choose the “Add->ASP.NET Folder->Add_Data” menu item).

Automatic Database Schema Creation

EF code-first supports the ability to automatically generate database schema and create databases from model classes – enabling you to avoid having to manually perform these steps.

This happens by default if your connection-string points to either a SQL CE or SQL Express database file that does not already exist on disk.  You do not need to take any manual steps for this to happen.

To see this in action, we can press F5 to run our NerdDinner application.  This will launch a browser at the root “/” URL of our application.  You should see a screen like below rendered back:

The “/” URL to our application invoked the HomeController.Index() action method – which instantiated and queried our NerdDinners context object to retrieve all upcoming Dinners from our database.  Because the NerdDinners.sdf database file we pointed our connection-string to didn’t already exist, the EF code-first library automatically generated it for us.  It used our NerdDinners context object to automatically infer the database schema for the database it generated. 

To see the SQL CE database file that was generated, click the “Show all Files” icon within the Visual Studio solution explorer, and then press the “Refresh” button and expand the App_Data folder:

 

We will be shipping an update to VS 2010 in the future that enables you to open up and edit SQL CE 4 databases within the “Server Explorer” tab (just like you do with SQL databases today).  This will enable you to easily see (and optionally tweak) the schema and contents of the database.  Until then you can optionally use the database tools within WebMatrix to examine the SQL CE 4 database file’s contents. 

We did not specify any custom persistence mapping rules with our NerdDinners context – so the database that was generated followed the default EF code-first naming conventions to map the schema.  If we had specified any custom mapping rules, though, the EF code-first library would have honored those and generated a database that matched them. 

Just to refresh our memory – here are the two POCO model classes and the NerdDinners context class that we defined earlier:

Below are the tables that were added when we ran our application and the database was automatically created based on the above model:

 

The definition of the “Dinners” table looks like below.  The column names and data-types map to the properties of the Dinner class we defined.  The DinnerID column has also been configured to be both a primary key and an identity column:

The definition of the “RSVPs” table looks like below.  The column names and data-types map to the properties of the RSVP class we defined.  The RsvpID column has also been configured to be both a primary key and an identity column:

A one to many primary key/foreign key relationship was also established between the Dinners and RSVPs tables.  The EF code-first library inferred that this should be established because our Dinner class has an ICollection<RSVP> property named RSVPs, and the RSVP class has a Dinner property.