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.

Following on from my post of Code First with entity framework. Each time I wanted to make changes to the database I went through the process of dropping the database and then regenerating it. This is fine during the development phase and especially if there’s no data to reimport to it. But let’s look at an alternative to this and something more likely to be of use when amending an existing database with a changed schema.

Migration commands are used to generate change tracking files. The commands are run via Visual Studio using the Tools | Library Packager Manager | Package Manager Console.

So load up the Package Manage Console (if it’s not already running) in Visual Studio.

The first thing we need to do is enable migrations on the project. This basically sets up the migration configuration files etc. ready for you to start migrations. So run

Enable-Migrations

and you’ll see a Migrations folder added to your project as well as a Congiruations.cs file and an _InitialCreate.cs file (and associated designer.cs and .rex files).

If you first take a look at the Configurations.cs file you’ll see a pretty empty file which can be used to seed data or handle other migration configuration code.

The _InitialCreate.cs file is where the code exists for actually recreating the current database tables.

Okay, so now we want to make some actual changes to our database. So continuing to use the sample code from the Code First I need to add dimensions to the Planogram, so my new code will look like this (new code highlighted)

public class Planogram
{
   public int Id { get; set; }
   public string Name { get; set; }

   public double Width { get; set; }
   public double Height { get; set; }
   public double Depth { get; set; }

   public virtual ICollection<Product> Products { get; set; }
   public virtual Store Store { get; set; }
}

If we now write some code to populate the new fields and run our application we’ll get an InvalidOperationException stating that the model backing the database context has changed. In other words we’ve changed the model but the DB is not in sync. The exception kindly notes that we should consider using Code First Migrations to update the database. So that’s what we’ll do next (for completeness the new coe to create the Planogram is listed below)

using (StorePlanDatabase storePlanDatabase = new StorePlanDatabase())
{
   Planogram pegboard = new Planogram
   {
      Name = "Batteries Pegboard",
      Depth = 50,
      Height = 1000,
      Width = 800
   };
   storePlanDatabase.Planograms.Add(pegboard);
}

So now back to our Package Manager Console and we need to create a migrations script for the changes to the model and thus the changes required to bring the database into sync with the model. At the Package Manager Console command line type

Add-Migration PlanogramDimensions

PlanogramDimensions is the descriptive name we’re giving to these changes to the model. If you don’t enter a name you will be prompted for one. A new file _PlanogramDimensions.cs (and it’s associated Designer.cs and .resx) are created. If you take a look into this file you’ll see code specific to adding (and dropping) the new columns on the Planograms table.

At this point the database has not been updated, this has just created the required “patch” if you like. So now from the Package Manager Console run

Update-Database

This will run the migrations script and patch the db it will also run the seed data method from the configurations. If you now check the db you’ll find the Planograms table has the new Width, Height and Depth fields.

In the past I’ve used Hibernate (in Java) and NHibernate (in C#/.NET) for ORM functionality but more recently started looking again at Entity Framework to see what this has to offer.

“Code First” within EF allows us to concentrate on writing the POCO (Plan Old CLR Objects) and let the EF database generation code create the DB for us.

Let’s look at an example.

Many years ago I wrote software for the retail industry. When a large retailer is looking to create a new store they will not only design the layout of shelving etc. but also stipulate the layout of products on a shelving unit (note: shelving is being used as a generic terms here for any type of shelf, freezer, pegboard etc.). So the basic relationship would be along the lines of a Store having many Planograms (the name given to the shelving unit plans) and each Planogram having many Products. In code we might model this in the following way

public class Product
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string UPC { get; set; }
}

public class Planogram
{
   public int Id { get; set; }

   public virtual ICollection Products { get; set; }
}

public class Store
{
   public int Id { get; set; }

   public virtual ICollection Planograms { get; set; }
}

Note: The above will NOT produce the tables that I/we might expect (discussed later). Also for brevity I’ve not included the connection string as either config or in the DbContext constructor.

The use of the virtual keyword indicates that these properties should be lazy loaded. Now we need something to represent the database itself. This is derived from the DbContext

public class StorePlanDatabase : DbContext
{
   public DbSet<Store> Stores { get; set; }
   public DbSet<Planogram> Planograms { get; set; }
   public DbSet<Product> Products { get; set; }
}

Now I’d like to see what the table structure of these classes looks like, so the simplest way is to create an empty database using the following

StorePlanDatabase storePlanDatabase = new StorePlanDatabase();
storePlanDatabase.Database.Create();

The database constructed by this call and with the current POCO classes isn’t quite what I expected. The first thing to note is, that my intention was to have Products used across multiple Planograms whereas the generated tables have a many-to-one relationship meaning I have many Products but only associated with one Planogram, which is no use when trying to reuse a Product across multiple Planograms.

The problem is we’ve not really given the EF database generator enough information. One way to describe the relationship (again solely using the POCO’s) is as follows

public class Product
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string UPC { get; set; }

   public virtual ICollection Planograms { get; set; }
}

public class Planogram
{
   public int Id { get; set; }
   public string Name { get; set; }

   public virtual ICollection Products { get; set; }
   public virtual Store Store { get; set; }
}

Prior to running this code I deleted the database as I don’t want to get into migrations at this time. So for completeness, in SQL Server Management Studio I typed

use master
go
drop database StorePlanDatabase
go

and deleted the database. Now re-running our database creation code we see that a new table ProductPlanograms has been created with two keys, Product_Id and Planogram_Id. So we now have the same product used across multiple Planograms in a many-to-many relationship. Ofcourse it’s quite feasible that the Planogram itself should have a many-to-many relationship with a Store also. But for now I’ll assume a Planogram is Store specific.

Time to add some data.

using (StorePlanDatabase storePlanDatabase = new StorePlanDatabase())
{
   Product tinnedTomatoes = new Product
   {
      Name = "Tinned Tomatoes",
      UPC = "12345678"
   };

   Product tinnedArtichokes = new Product
   {
      Name = "Tinned Artichokes",
      UPC = "23456789"
   };

   Planogram endcap = new Planogram
   {
      Products = new List
      {
         tinnedTomatoes,
         tinnedArtichokes
      }
   };

   Planogram gondola = new Planogram
   {
      Products = new List
      {
         tinnedTomatoes
      }
   };

   Store localStore = new Store
   {
      Planograms = new List
      {
         endcap,
         gondola
      }
   };

   storePlanDatabase.Stores.Add(localStore);
   storePlanDatabase.SaveChanges();
}

One thing we might wish to alter is the Id on the Product. Obviously it’s easy for me to duplicate the “Tinned Tomatoes” name. In the retail world we have things like UPC (Universal Product Code). This is mean’t to be a 12 digit unique code. So we could do away with the Id on the product and make the UPC the key. Just removing the Id will fail with an exception regarding missing key. We need to give the EF database creator a hint (or better still tell it precisely) which property is now the key. To do this we annotate the UPC with the KeyAttribute (see Julie Lerman’s blog for more info. on various code first annotations)

[Key]
public string UPC { get; set; }

and now it all works again. If we take a look at the UPC field in the Products table we see it’s a nvarchar(128) but the UPC is a 12 digit code, so time to limit the property to this number of characters.

[Key, MinLength(12), MaxLength(12)]
public string UPC { get; set; }

checking the UPC field in the Products table we now see it’s listed as an nvarchar(12).

Note: if you run this with the test data listed previously you’ll get a constraint exception as the UPC were not 12 characters in length. So feel free to change the data accordingly.

So, the next question might be how do I go about validating the 12 character string to ensure it’s all numeric. We now need to add a new override to the StorePlanDatabase

protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry,
                      IDictionary<object, object> items)
{
   DbEntityValidationResult result = base.ValidateEntity(entityEntry, items);

   if(entityEntry.State == EntityState.Added ||
              entityEntry.State == EntityState.Modified)
   {
      if(entityEntry.Entity is Product)
      {
         Product p = (Product) entityEntry.Entity;
         if (p.UPC.Any(c => !Char.IsDigit(c)))
         {
            result.ValidationErrors.Add(
                        new DbValidationError("Product", "Must be numeric"));
         }
      }
   }
   return result;
}

When we attempt to save changes to the StorePlanDatabase the validation code will kick in and any invalid UPC’s will cause a DbEntityValidationException to occur.

And finally for this post, it’s time to access the data we’ve stored

using (StorePlanDatabase storePlanDatabase = new StorePlanDatabase())
{
   foreach(Product p in storePlanDatabase.Products)
   {
      Console.WriteLine(String.Format("{0} : {1}", p.UPC, p.Name));
   }
}

Simple.

A couple of additional pieces of information, I’ve not mentioned the user of the connection string for the database. One could hard code the connection string into the DbContext constructor, for example

public class StorePlanDatabase : DbContext
{
   public StorePlanDatabase()
	: base("Server=MyServerName;Database=StorePlanDatabase;Integrated Security=True")
   {
   }

   public DbSet<Store> Stores { get; set; }
   public DbSet<Planogram> Planograms { get; set; }
   public DbSet<Product> Products { get; set; }
}

or more likely place in the app.config as per the following

<configuration>
   <connectionStrings>
      <add name="StorePlanDatabase"
             connectionString="Server=MyServerName;Database=StorePlanDatabase;Integrated Security=True" providerName="System.Data.SqlClient" />
   </connectionStrings>
</configuration>

There are further options on handling the configuration string, but these should get you started.