This allows us to map many normalized tables to one object to bring together a cohesive object model without propagating the data structure into our code.
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 for connecting the data and updating it.
Open the Improving Many Tables To One Object Maps solution in the included source code examples.
Let us get connected to the database using the following steps:
MappingTest
to the Test
project. We 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 ShouldReturnABlogWithAuthorDetails() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); //Act var post = context.Blogs.FirstOrDefault(); //Assert Assert.IsNotNull(post); Assert.IsNotNull(post.AboutTheAuthor); } } }
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<Blog>().Add(new Blog() { 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; } } }
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.Map(m => { m.Properties(t => new {t.Id, t.Title,t.ShortDescription}); m.ToTable("Blog"); }) .Map(m => { m.Properties(t => new {t.Description, t.Creationdate, t.AboutTheAuthor}); m.ToTable("BlogDetails"); }); this.HasKey(x => x.Id); this.Property(x => x.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) .HasColumnName("BlogId"); } } }
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()); 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(); } } }
Our solution starts with a test that defines our intent. This process is intended to map two tables into a single object, for the sake of ease of use and readability. This process can be helpful to solidify a normalized database schema into a cohesive object graph.
The
Map()
method allows us to define mapping fragments that we then use to set up two distinct sets of configuration. This can be almost any number of fragments that we would need to be combined into one object. This technique is normally reserved for mapping to legacy databases that have had large amounts of normalization, due to which they would not translate well into objects.
The Properties()
method allows us to define new anonymous objects that contain the properties that belong to each table. Each of these fragments will be combined into overall class mapping that will span tables and even schemas.
The ToTable()
method functions as it always has, mapping to a table on the server. This will allow us to tie the properties together from multiple tables.
Mapping more than one table to a single object is a fairly rare scenario, but when we need to handle this kind of database schema, there are several things to keep in mind.
It is ideal to combine tables when the table structure does not translate properly to the objects, and also when querying performance is not a major concern. The queries that are generated by this are not the fastest out there and will get slower as you combine more tables.