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.