We’ve written our code. Now let’s run the
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
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
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
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