Category Archives: Entity Framework

Returning to Entity Framework database first

After working with a database project in Visual Studio, I thought it was probably time to create a simple console application to interact with the database using the current version of Entity Framework (v6.0).

So as we’ve already created the cddb database in a previous post, we’ll simply create a new console project and work with that DB.

  • Create your application, as stated mine is a console application
  • Add new item and select Data | ADO.NET Entity Data Model, mine’s named CddbContext (as this will include the source for the data context created for EF)
  • Select Code First from database
  • Create a new connection and supply the relevant details for your database connection
  • Press next then select the tables (and views) you want to generate code for – then click Finish

Here’s the code generated

CddbContext.cs

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

   public virtual DbSet<album> albums { get; set; }
   public virtual DbSet<artist> artists { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      modelBuilder.Entity<artist>()
         .HasMany(e => e.albums)
         .WithRequired(e => e.artist)
         .WillCascadeOnDelete(false);
   }
}

artist.cs

[Table("artist")]
public partial class artist
{
   [System.Diagnostics.CodeAnalysis.SuppressMessage(
    "Microsoft.Usage", 
    "CA2214:DoNotCallOverridableMethodsInConstructors")]
   public artist()
   {
      albums = new HashSet<album>();
   }

   public int Id { get; set; }

   [Required]
   [StringLength(50)]
   public string Name { get; set; }

   [System.Diagnostics.CodeAnalysis.SuppressMessage(
    "Microsoft.Usage", 
    "CA2227:CollectionPropertiesShouldBeReadOnly")]
   public virtual ICollection<album> albums { get; set; }
}

album.cs

[Table("album")]
public partial class album
{
   public int Id { get; set; }

   [Required]
   [StringLength(50)]
   public string Title { get; set; }

   public int ArtistId { get; set; }

   public virtual artist artist { get; set; }
}

finally let’s create a simple but of code to get the artists from the database, so in Main we have

using (var db = new CddbContext())
{
   var artists = db.artists;
   foreach (var a in artists)
   {
      Console.WriteLine(a.Name);
   }
}

If your database schema changes you will need to re-run the steps to generate your data context etc. or code by hand. There isn’t (currently) a way to update existing classes – so don’t make changes to the generated code and expect it to still exist after regeneration.

Entity Framework & AutoMapper with navigational properties

I’ve got a webservice which uses EF to query SQL Server for data. The POCO’s for the three tables we’re interested in are listed below:

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

   public PlantType PlantType { get; set; }
   public LifeCycle LifeCycle { get; set; }

  // other properties
}

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

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

The issue is that when a new plant is added (or updated for that matter) using the AddPlant (or UpdatePlant) method we need to ensure EF references the LifeCycle and PlantType within its context. i.e. if we try to simply call something like

context.Plants.Add(newPlant);

then (and even though the LifeCycle and PlantType have an existing Id in the database) EF appears to create new PlantTypes and LifeCycles. Thus giving us multiple instances of the same LifeCycle or PlantType name. For the update method I’ve been using AutoMapper to map all the properties, which works well except for the navigational properties. The problem with EF occurs.

I tried several ways to solve this but kept hitting snags. For example we need to get the instance of the PlantType and LifeCycle from the EF context and assign these to the navigational properties to solve the issue of EF adding new PlantTypes etc. I wanted to achieve this in a nice way with AutoMapper. By default the way to create mappings in AutoMapper is with the static Mapper class which suggests the mappings should not change based upon the current data, so what we really need is to create mappings for a specific webservice method call.

To create an instance of the mapper and use it we can do the following (error checking etc. remove)

using (PlantsContext context = new PlantsContext())
{
   var configuration = new ConfigurationStore(
                     new TypeMapFactory(), MapperRegistry.AllMappers());
   var mapper = new MappingEngine(configuration);
   configuration.CreateMap<Plant, Plant>()
         .ForMember(p => p.Type, 
            c => c.MapFrom(pl => context.PlantTypes.FirstOrDefault(pt => pt.Id == pl.Type.Id)))
	 .ForMember(p => p.LifeCycle, 
            c => c.MapFrom(pl => context.LifeCycles.FirstOrDefault(lc => lc.Id == pl.LifeCycle.Id)));

   //... use the mapper.Map to map our data and then context.SaveChanges() 
}

So it can be seen that we can now interact with the instance of the context to find the PlantType and LifeCycle to map and we do not end up trying to create mappings on the static class.

Entity Framework – lazy & eager loading

By default Entity Framework will lazy load any related entities. If you’ve not come across Lazy Loading before it’s basically coding something in such a way that either the item is not retrieved and/or not created until you actually want to use it. For example, the code below shows the AlternateNames list is not instantiated until you call the property.

public class Plant
{
   private IList<AlternateName> alternateNames;

   public virtual IList<AlternateName> AlternateNames
   {
      get
      {
         return alternateNames ?? new List<AlternateName>();
      }
   }
}

So as you can see from the example above we only create an instance of IList when the AlternateNames property is called.

As stated at the start of this post, by default Entity Framework defaults to lazy loading which is perfect in most scenarios, but let’s take one where it’s not…

If you are returning an instance of an object (like Plant above), AlternateNames is not loaded until it’s referenced, however if you were to pass the Plant object over the wire using something like WCF, AlternateNames will not get instantiated. The caller/client will try to access the AlternateNames property and of course it cannot now be loaded. What we need to do is ensure the object is fully loaded before passing it over the wire. To do this we need to Eager Load the data.

Eager Loading is the process of ensuring a lazy loaded object is fully loaded. In Entity Framework we achieve this using the Include method, thus

return context.Plants.Include("AlternateNames");

Entity Framework – Tips and Tricks

How do I stop my Code First implementation creating a new database

It might seem a strange request, why use Code First then. But let’s assume you’ve created a Code First EF model and then switched to creating the db using SQL scripts and want ensure the EF application doesn’t try to create the db.

Database.SetInitializer(null);

By default the initializer would be set to CreateDatabaseIfNotExists.

See also Database.SetInitializer.

There is already an open DataReader associated with this Command which must be closed first

An EntityCommandExecutionException with the message “There is already an open DataReader associated with this Command which must be closed first” occurred whilst implementing a bit of code to list all the ProductTypes in a database.

The solution appears to be add

MultipleActiveResultSets=True

to your connection string

I’ll add more to this post as or when I remember

Storing binary data using Entity Framework

This is a real quick post, not my usual verbose ramblings :)

If you want to store binary data within a database column using Code First, then store the data as a byte array (see below).

public class SomeData
{
   // properties etc.

   // binary data, will be converted to a varbinary(max) in SQL Server
   public byte[] Data { get; set; }
}

Entity Framework – Dynamic Proxies and WCF

A quick post regarding a simple problem you might find using Entity Framework with WCF. I have a simply little application which uses Entity Framework to access SQL Server and currently this all happens in a little client application.

I decided I wanted to move the DB access into a web service so I could look at writing an iPad or the likes front end to access it. All went well until I got the following exception


An error occurred while receiving the HTTP response to http://localhost:9095/MyService. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details.

My web service code looked like the following

public List<Plant> GetPlants()
{
   using (PlantsContext context = new PlantsContext())
   {
       return context.Plants.ToList();
   }
}

The problem is that the return is returning dynamic proxies, of the Plant type, not strictly real Plant types. What we need to do is change the code to add a line to turn off proxy creation

using (PlantsContext context = new PlantsContext())
{
   context.Configuration.ProxyCreationEnabled = false;
   return context.Plants.ToList();
}

And all works.

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.

Entity Framework – Migrations

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.

Entity Framework – Code First

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.