Mapping many tables to one object

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.

Getting ready

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.

How to do it...

Let us get connected to the database using the following steps:

  1. We start by adding a new unit test named 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);
    }
  }
}
  2. Add a new C# class named 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();
    }
  }
}
  3. Add a new C# class named 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; }
  }
}
  4. Now that we have our domain objects, we want to add a 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");
    }
  }
}
  5. Modify 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();
    }
  }
}
  6. Run our test, and see how it works.

How it works...

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.

There's more...

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.

When to combine tables

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.

Security concerns

Security concerns may arise while bridging the tables and schemas. Here, you must be aware that if the user has access to both schemas and tables that are bridged, this can cause runtime errors that cripple your application, if not handled properly.

See also

In this chapter:

  • Mapping one table to many objects
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset