In this recipe, we map a set of related objects to a single table, and map ourselves to the values in the database, to determine the type of object to load.
We will be using NuGet Package Manager to install the Entity Framework 4.1 assemblies.
The package installer can be found at http://nuget.org/.
We will also be using a database to connect to the data and update it.
Open the Improving Inheritance Based On Database Values solution in the included source code examples.
Let us get connected to the database using the following steps:
MappingTest
to the Test
project. We will make a test that connects to the database and retrieves an object. This will test the configuration, and ensure that the model matches the database schema, using the following code:using System; using System.Collections.Generic; using System.Linq; using System.Text; using BusinessLogic; using DataAccess; using DataAccess.Database; using Microsoft.VisualStudio.TestTools.UnitTesting; using Test.Properties; using System.Data.Entity; namespace Test { [TestClass] public class MappingTest { [TestMethod] public void ShouldReturnABlogWithTypeSafety() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); //Act var pictureBlog = context.Set<PictureBlog>().FirstOrDefault(); var videoBlog = context.Set<VideoBlog>().FirstOrDefault(); //Assert Assert.IsNotNull(pictureBlog); Assert.IsNotNull(videoBlog); } } }
Initializer
, to the DataAccess
project in the Database
folder, with the following code:using System; using System.Collections.Generic; using System.Data.Entity; using BusinessLogic; namespace DataAccess.Database { public class Initializer : DropCreateDatabaseAlways<BlogContext> { public Initializer() { } protected override void Seed(BlogContext context) { context.Set<PictureBlog>().Add(new PictureBlog() { Creationdate = DateTime.Now, ShortDescription = "Testing", Title = "Test Blog", Description = "Long Test", AboutTheAuthor = "Me me me" }); context.Set<VideoBlog>().Add(new VideoBlog() { Creationdate = DateTime.Now, ShortDescription = "Testing", Title = "Test Blog", Description = "Long Test", AboutTheAuthor = "Me me me" }); context.SaveChanges(); } } }
Blog
to the BusinessLogic
project, with the following code:using System; using System.Collections.Generic; using DataAccess; namespace BusinessLogic { public class Blog { public int Id { get; set; } public DateTime Creationdate { get; set; } public string ShortDescription { get; set; } public string Title { get; set; } public string Description { get; set; } public string AboutTheAuthor { get; set; } } public class PictureBlog : Blog { } public class VideoBlog : Blog { } }
Mapping
folder to the DataAccess
project and then add a BlogMapping
class to the folder, with the following code:using System.ComponentModel.DataAnnotations; using System.Data.Entity.ModelConfiguration; using BusinessLogic; namespace DataAccess.Mappings { public class BlogMapping : EntityTypeConfiguration<Blog> { public BlogMapping() { this.ToTable("Blogs"); this.HasKey(x => x.Id); this.Property(x => x.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) .HasColumnName("BlogId"); this.Property(x => x.Title).HasMaxLength(175); } } public class VideoBlogMapping : EntityTypeConfiguration<VideoBlog> { public VideoBlogMapping() { this.Map(x => { x.MapInheritedProperties(); x.Requires("BlogType").HasValue("Video"); }); } } public class PictureBlogMapping : EntityTypeConfiguration<PictureBlog> { public PictureBlogMapping() { this.Map(x => { x.MapInheritedProperties(); x.Requires("BlogType").HasValue("Picture"); }); } } }
BlogContext
to include Blog DbSet<>
and the new configurations, with the following code:using System; using System.Data.Entity; using System.Linq; using BusinessLogic; using DataAccess.Mappings; namespace DataAccess { public class BlogContext : DbContext, IUnitOfWork { public BlogContext(string connectionString) : base(connectionString) { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new BlogMapping()); modelBuilder.Configurations.Add(new PictureBlogMapping()); modelBuilder.Configurations.Add(new VideoBlogMapping()); base.OnModelCreating(modelBuilder); } public DbSet<Blog> Blogs { get; set; } public IQueryable<T> Find<T>() where T : class { return this.Set<T>(); } public void Refresh() { this.ChangeTracker.Entries().ToList().ForEach(x=>x.Reload()); } public void Commit() { this.SaveChanges(); } } }
We start with a test that conveys our intent to store several types of objects in a single table. This allows us to implement the table per hierarchy pattern and set a discriminator column that tells Entity Framework which object is to be created, when it loads the data from the database.
Note that we do not map the property for the discriminator column. If we want to use a nullable column for the discriminator, we would map it and then use it with the HasValue()
method, without parameters. We are looking for a specific value, so we use the string version of Requires(string).HasValue(string)
. This allows us to map more than one type to a single discriminator column.
When we map this kind of table structure, there are several major patterns that come into play.
This defines a pattern by which each class structure (also called the base class), and all the derived classes, get a single table. The framework uses a discriminator column, in the table, that allows it to distinguish between the objects and the load. This will keep the data structure consistent, but makes direct dealing with the data, without the framework, slightly more difficult. If you do not specify the custom configuration of the table structure, this table per hierarchy is the strategy that you will get by default.
Database administrators would likely point out that this model violates the third normal form, and they are right. This model places dependencies on columns that are not keys, but it is much more efficient in handling polymorphic queries than either of the other two options. There are times while attaining efficiency in querying, it is important to break the third normal form, and if you make robust use of polymorphic queries, then this should be one of them.
This defines a pattern by which each class gets a table in the database. This is the most straightforward approach, and it will normally get you through most situations. This is where objects are translated directly by convention. If you attempt to map inheritance this way, all the derived relationships will be mapped to one-to-one relationships.
This defines a pattern by which each non-abstract class gets a table; the properties that are defined on the base abstract class are mapped and replicated to each table so that there is no sharing between the objects. This kind of denormalization is normally discouraged by our database administrators, as it duplicates columns and the structure that holds the same data.