StackOverflow adventures: Entity Framework Code First advanced mapping scenarios
A couple of days back I was lucky enough to answer this question on stackoverflow:
So basically the question was about how to split an entity into two tables. And this let me to writing this blog post on advanced mapping scenarios.
When you have an entity in EF CF, by default it maps to one table. But what happens if you want something different?
(You can use these samples in Visual Studio, but if you want a more convenient way, be sure to check out my post for an Entity Framework Model Builder Workbook).
Mapping an entity to multiple tables
So what happens if you have an entity like this:
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
public string ZipCode { get; set; }
public string City { get; set; }
public string AddressLine { get; set; }
}
And you want to map it into two tables: one with the PersonId and the Name, where PersonId is the primary key, and another with the address details and PersonId as the foreign key? Well, you can do this with entity splitting:
public class MyDemoContext : DbContext
{
public DbSet<Person> Products { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>().Map(m =>
{
m.Properties(t => new { t.PersonId, t.Name });
m.ToTable("People");
})
.Map(m =>
{
m.Properties(t => new { t.PersonId, t.AddressLine, t.City, t.ZipCode });
m.ToTable("PersonDetails");
});
}
}
This creates the two tables, one with the PersonId and the Name, the other with PersonId, AddressLine, City and ZipCode. In the second one PersonId is both a primary key and the foreign key, referencing the PersonId in the other table. If you query an entity from the database, EF automatically joins these two tables together along this PersonId column.
What if you want to change the names of the columns? No problem, you can use single property mapping and specify a name:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>().Map(m =>
{
m.Properties(t => new { t.PersonId });
m.Property(t => t.Name).HasColumnName("PersonName");
m.ToTable("People");
})
.Map(m =>
{
m.Property(t => t.PersonId).HasColumnName("ProprieterId");
m.Properties(t => new { t.AddressLine, t.City, t.ZipCode });
m.ToTable("PersonDetails");
});
}
Mapping a single table to multiple entities
You can also do mapping the other way around: you can map one table into two entities (thus splitting the table). Be aware that if you do this, entities must share the same primary key (otherwise you could specify different primary keys to each of the two "halves" of the table).
So you might have a model like this:
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
public Address Address { get; set; }
}
public class Address
{
public string ZipCode { get; set; }
public string City { get; set; }
public string AddressLine { get; set; }
public int PersonId { get; set; }
public Person Person { get; set; }
}
And then configure the context like this:
public class MyDemoContext : DbContext
{
public DbSet<Person> Products { get; set; }
public DbSet<Address> Addresses { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Address>().HasKey(t => t.PersonId);
modelBuilder.Entity<Person>().HasRequired(t => t.Address)
.WithRequiredPrincipal(t => t.Person);
modelBuilder.Entity<Person>().Map(m => m.ToTable("People"));
modelBuilder.Entity<Address>().Map(m => m.ToTable("People"));
}
}
This just configures the keys (because remember primary key must have the same name, and this makes in this case foreign keys also off, so that must be fixed). We make Address the dependent, because that is "part of the Person", and not vice versa. Aside from this key fix-up, you can simply Map() to map both the entities into the same table. And this indeed creates one table with all the properties as columns, with PersonId as the primary key.
Complex types
Complex types are also a way of mapping multiple entities into one table — sort of. There is a catch (when isn't there?): complex types are not entity types. They cannot have their own DbSet, they cannot have primary keys, cannot contain foreign keys and they have some other restrictions.
So the idea is the same: you have an Address and a Person. Address will be the complex type, because that is "part of the Person", again.
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
public Address Address { get; set; }
}
public class Address
{
public string ZipCode { get; set; }
public string City { get; set; }
public string AddressLine { get; set; }
}
Remember: no primary key or foreign key into Address (even though that's the dependent).
And now for the model:
public class MyDemoContext : DbContext
{
public DbSet<Person> Products { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.ComplexType<Address>();
}
}
And that's it! It also creates one single table, like before, only the column names are different (but you can always change that with a simple property mapping statement). The main difference compared to the previous solution is that Address here is a complex type, so as I've written before, they have certain limitations. As they do not have primary keys, they cannot exist on their own — this means no DbSet for them, and the cannot be part of associations (i.e. no foreign keys in them or pointing into them). Properties of these types cannot be null in the "parent" class, and you cannot use polymorphism (this means no inheritance between them). In turn they are more lightweight and easier to configure. Usually you would use this solution (unless these limitations are a real problem, of course).
Inheritance
Technically mapping inheritance between objects is also a means of mapping multiple entities into one table, but the purpose in that case is entirely different, so I leave this up for another post :)
Conclusion
So there you go: now you can map one entity into multiple tables, or multiple entities into one table (or use complex types for a more lightweight solution).