Entity Framework – Database First

In a previous post I highlighted how to generate a database from a POCO (Plain Old CLR Object) model in what’s known as “Code First”. However in many situations you’ll more likely have to generate your code model from your database. Either you’ll prefer to implement your DB first, maybe it’s more natural to create the DB first or maybe you’re looking at an existing project where the DB has already been created.

So let’s look at how we can use EF from an existing database.

We’re going to create a MusicDatabase (this sample used to always be a CD Database but as much of my music is bought nowadays as MP3 files I’ll update this to a MusicDatabase instead).

So steps to create this demo database are as follows, in SQL Server Management Studio, create a new database and name it MusicDatabase, then add the following tables (the scripts below were generated from my tables)

1. Create the Artists table

USE [MusicDatabase]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Artists](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Genre] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. Create the Albums table

USE [MusicDatabase]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Albums](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ArtistId] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Albums]  WITH CHECK ADD  CONSTRAINT [FK_Albums_Artists] FOREIGN KEY([ArtistId])
REFERENCES [dbo].[Artists] ([Id])
GO

ALTER TABLE [dbo].[Albums] CHECK CONSTRAINT [FK_Albums_Artists]
GO

3. Finally create the tracks table

USE [MusicDatabase]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tracks](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AlbumId] [int] NOT NULL,
	[Number] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Track] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tracks]  WITH CHECK ADD  CONSTRAINT [FK_Tracks_Albums] FOREIGN KEY([AlbumId])
REFERENCES [dbo].[Albums] ([Id])
GO

ALTER TABLE [dbo].[Tracks] CHECK CONSTRAINT [FK_Tracks_Albums]
GO

The next step we have to task is to generate the code from our database, so create a solution (I’m using a console application)

Now right click on the project and select Add | New Item. In the Visual C# Items | Data section select the ADO.NET Entity Data Model, I named my .edmx file MusicDatabase.edmx.
Press OK
Now select the Generate from database option.
Create a new connection and enter your database server details and database name.
Select Next
Finally you should see a dialog asking what tables, views and stored proces should it include in your model. We only have tables so check all the tables
Press Finish

If all went to plan you should now be viewing a database diagram of the entities and their fields and relationships. We’re currently in the MusicDatabase.edms views of our database. Switching the Solution Explorer we can see the MusicDatabase.edmx and it has a Designer.cs. Taking a look into this we can see the classes created to map to our database tables.

Now let’s add an artist to the database along with a couple of their albums and a couple of tracks for each album – just to prove it all works.

Copy/Paste the following into Program.cs Main (if like me you’ve created a simple console app. to test this in)

using(MusicDatabaseEntities context = new MusicDatabaseEntities())
{
   Artist artist = new Artist
   {
      Name = "Alice Cooper",
      Genre = "Rock",
      Albums =
      {
         new Album
         {
            Name = "School's Out",
            Tracks =
            {
               new Track
               {
                  Number = 1,
                  Name = "School's Out"
               },
	       new Track
               {
                  Number = 2,
                  Name = "Luney Tune"
               }
            }
         },
         new Album
         {
            Name = "Love it to death",
            Tracks =
            {
               new Track
               {
                  Number = 1,
                  Name = "Caught in a dream"
               },
               new Track
               {
                  Number = 2,
                  Name = "I'm Eighteen"
               }
            }
         }
      }
   };

   context.Artists.AddObject(artist);
   context.SaveChanges();
}

Now if you run this and everything went well, you should find you’ve populated the tables in the database.

You may have noticed the AddObject call at the end of the method (above). This is because Visual Studio 2010 (which I’m using for this example) requires an extra step to make use of the DbContext API that was used in the Entity Framework – Code First post.

See Database First for more on this (actually had I seen this post before I started writing this post I might not have bothered as this link points to a more comprehensive explanation, but I’ve got this far so may as well finish the post :)).

So to use the DbContext carry out the following

Double click on the MusicDatabase.edmx file to switch to the Model Browser if it’s not currently visible.
Right mouse click on the space MusicDatabase.edmx file within this view.
Select Add Code Generation Item.
Click on the Online Templates item
Type DbContext into the Search Online Template text box
Now select EF 5.x DbContext Generator for C#
Change the filename from Model.tt to MusicModel.tt
Press Add

If you now switch to the solution view in Visual Studio you’ll see some additional .tt files which will basically generate code using the DbContext style code and AddObject plus the associated old style EF code (such as the table classes being derived from EntityObject) being replaced with classes more akin to the POCO classes we defined in the Entity Framework – Code First post.

And finally…

We might need to make changes to the database over time so we need a way to update our model, so in SQL Server Management Studio go to the Tracks table and add a new column called Length, I’ve made mine of data type “real” and this will act as the track length field (I’ve allowed my field to be null just to save me issues with existing data).

Back in Visual Studio, load the Model Browser and right mouse click on the MusicDatabase.edmx file (or any free space) and select Update Model from Database. Check the tables checkbox and press Finish. Rebuild the project to get the template files to regenerate their code and the Track class will now have a Length property.

My Memory

Outsourcing my memory and thoughts (and other ramblings) to the web

Entity Framework – Database First