Using Conventions and Configurations for Database Mappings
So far we have discussed the Code First convention and configurations that affect property attributes and those that pertain to relationships between classes. In both of these categories, Code First affected not only the model but the database as well. In this chapter, we will focus on how to map classes to the database without impacting the conceptual model.
Mapping Class Name to Database Table and Schema Name
Entity Framework use its pluralization service to infer database table names based on the class names in the model. Destination, for example, becomes Destinations, Person becomes to People, etc. The class name of model, however, might not be the same as the table naming conventions.
We can use the Table Data Annotation to ensure that Code First maps your class to the correct table name. Additional we also could assign the schema to the table (default is dbo).
Configuring Table and Schema Name with Data Annotations
The Table Data Annotation allows us to change the name of the table that our model class maps to.
[Table("PersonPhotos")]
public class PersonPhoto
[Table("Locations", Schema="baga")]
public class Destination
Figure: Table configuration results in baga.Locations
Configuring Table and Schema name with the Fluent API
modelBuilder.Entity<Destination>().ToTable("Locations", "baga");
Mapping Property names to Database Columns
Not only we can remap the table name, but we can also alter the presumed database column name. By convention, Code First will use the name of the property as the name of the column that it maps to, but this may not always the case.
Modifying the Default Column Name with Data Annotations
[Column("LocationID")]
public int DestinationId { get; set; }
[Required, Column("LocationName")]
public string Name { get; set; }
Modifying the Default Column Name with the Fluent API
HasColumnName is the Fluent method used to specify a column name for a property.
public class DestinationConfiguration :
EntityTypeConfiguration<Destination>
{
public DestinationConfiguration()
{
Property(d => d.Nam
.IsRequired().HasColumnName("LocationName");
Property(d => d.DestinationId).HasColumnName("LocationID");
}
}
Affecting Column Names for Complex Types
In the Chapter 1, we discussed the ComplexType from the Address class. Code First will map this class into database by using the pattern Address_StreetAddress or Address_State, etc.
We can rename these column names like this:
//Data Annotation
[ComplexType]
public class Address
{
public int AddressId { get; set; }
[MaxLength(150)]
[Column("StreetAddress")]
public string StreetAddress { get; set; }
[Column("City")]
public string City { get; set; }
[Column("State")]
public string State { get; set; }
[Column("ZipCode")]
public string ZipCode { get; set; }
}
//Fluent API
public class AddressConfiguration : ComplexTypeConfiguration<Address>
{
public AddressConfiguration()
{
Property(a => a.StreetAddress).HasColumnName("StreetAddress");
}
}
Allowing Multiple Entities to Map to a Single Table: aka Table Splitting
Often a database table has so many columns in it that some scenarios only require you to use a subset of them, while others require access to additional column data. Table splitting helps solve this problem by allowing us to break up the columns of a single table across multiple entities.
You may be more likely to want table splitting when mapping to an existing database where you find yourself with the scenario described above, though you might find that you want it even when letting Code First create the database for you.
Example: The PersonPhoto calss with Data Annotation
[Table("PersonPhotos")]
public class PersonPhoto
{
[Key , ForeignKey("PhotoOf")]
public int PersonId { get; set; }
[Column(TypeName="image")]
public byte[] Photo { get; set; }
public string Caption { get; set; }
public Person PhotoOf { get; set; }
}
In order to map entities into a common table, the entities must comply with the following rules:
- The entities must have a one-to-one relationship.
- The entities must share a common key.
The Person and PersonTable classes meet these requirements.
Mapping to a Common Table using Data Annotations
We’ll apply the table name to both classes:
[Table("People")]
public class Person
[Table("People")]
public class PersonPhoto
What’s more interesting about his mapping is that we can query one of the entities without wasting resources pulling back the whole table columns (including the columns are in the other entity).
If we execute a query against the Person class, like context.People.ToList(), Entity Framework only projects those clolumns that map to the Person class.
Splitting a Table Using the Fluent API
We can easily add them in the relevant EntityTypeConfiguration class:
modelBuilder.Entity<Person>().ToTable("People");
modelBuilder.Entity<PersonPhoto>().ToTable("People");
About Lazy Loading Split Table Data
Lazy loading makes this feature shine. Although the DbContext we are using has lazy loading enabled by default. Any navigation property with the virtual keyword applied to it will automatically be retrieved from the database when it is first accessed. For instance:
[Required]
public virtual PersonPhoto Photo { get; set; }
Lazy loading Photo
var people = context.People.ToList();
var firstPerson = people[0];
SomeCustomMethodToDisplay(firstPerson.Photo.Caption);
Once we enable the lazy loading, the Entity Framework will run a behind-the-scenes query when we try to retrieve the null data from database. If we disable it, the last line of code will throw an exception because the Photo property will be null.
Mapping a Single Entity Across Multiple Tables
<strong><em>Example:DestinationConfiguration with Entity Splitting mapping at the end</em></strong>
public class DestinationConfiguration :
EntityTypeConfiguration<Destination>
{
public DestinationConfiguration()
{
Property(d => d.Name)
.IsRequired().HasColumnName("LocationName");
Property(d => d.DestinationId).HasColumnName("LocationID");
Property(d => d.Description).HasMaxLength(500);
Property(d => d.Photo).HasColumnType("image");
// ToTable("Locations", "baga");
Map(m =>
{
m.Properties(d => new {d.Name, d.Country, d.Description});
m.ToTable("Locations");
});
Map(m =>
{
m.Properties(d => new { d.Photo });
m.ToTable("LocationPhotos");
});
}
}
Example: Insert a single object that maps to two database tables
private static void InsertDestination()
{
var destination = new Destination
{
Country = "Indonesia",
Description = "EcoTourism at its best in exquisite Bali",
Name = "Bali"
};
using (var context = new BreakAwayContext())
{
context.Destinations.Add(destination);
context.SaveChanges();
}
}
Preventing Types from Being Included in the Model
Using Data Annotation to ignore types
[NotMapped]
public class MyInMemoryOnlyClass
Using Fluent Configuration to ignore types
modelBuilder.Ignore<MyInMemoryOnlyClass>();
Understanding Property Mapping and Accessibility
The list of rules will explain when defining properties in your classes, what to expect from convention, and how to change the default mapping with configuration.
Scalar Property Mapping
Scalar properties are only mapped if they can be converted to a type that is supported by EDM.
The valid EDM types are Binary, Boolean, Byte, DateTime, DateTimeOffset, Decimal, Double, Guid, Int16, Int32, Int64, SByte, Single, String, Time.
Scalar properties that can’t be mapped to an EDM type are ignored (e.g., enums and unsigned integers).
Accessibility of Properties, Getters, and Setters
- A public property will be automatically mapped by Code First.
- The setter can be marked with a more restrictive accessor, but the getter must remain public for the property to be mapped automatically.
- A nonpublic property must be configured using the Fluent API in order to be mapped by Code First.
Mapping Inheritance Hierarchies
Entity Framework supports a variety of inheritance hierarchies in the model.
Working with Code First’s Default Inheritance: Table Per Hierarchy (TPH)
Table Per Hierarchy (TPH) describes mapping inherited types to a single database table that uses a discriminator column to differentiate one subtype from another.
Example: Lodging class and new Resort class that derives from Loding
public class Lodging
{
public int LodgingId { get; set; }
[Required]
[MaxLength(200)]
[MinLength(10)]
public string Name { get; set; }
public string Owner { get; set; }
// public bool IsResort { get; set; }
public decimal MilesFromNearestAirport { get; set; }
[InverseProperty("PrimaryContactFor")]
public Person PrimaryContact { get; set; }
[InverseProperty("SecondaryContactFor")]
public Person SecondaryContact { get; set; }
public int DestinationId { get; set; }
public Destination Destination { get; set; }
public List<InternetSpecial> InternetSpecials { get; set; }
}
public class Resort : Lodging
{
public string Entertainment { get; set; }
public string Activities { get; set; }
}
The Resort information is stored in the Lodgings tables, and Code First established a column called Discriminator. This property is non-nullable and its type is nvarchar(128). By default, Code First will use the type name of each type in the hierarchy as the value stored in the discriminator column.
If we run the code below, the Entity Framework will insert the string “Resort” into the Discriminator column in the database.
private static void InsertResort()
{
var resort = new Resort {
Name = "Top Notch Resort and Spa",
MilesFromNearestAirport=30,
Activities="Spa, Hiking, Skiing, Ballooning",
Destination=new Destination{
Name="Stowe, Vermont",
Country="USA"}
};
using (var context = new BreakAwayContext())
{
context.Lodgings.Add(resort);
context.SaveChanges();
}
}
Customizing the TPH Discriminator Field with the Fluent API
Configuring the discriminator column name and possible values.
Map(m =>
{
m.ToTable("Lodgings");
m.Requires("LodgingType").HasValue("Standard");
})
.Map<Resort>(m =>
{
m.Requires("LodgingType").HasValue("Resort");
});
Configuring Table Per Type (TPT) Hierarchy
While TPH contains all of the types for a hierarchy in a single table, Table Per Type(TPT) only stores properties from the base class in a single table. Additional properties defined on a derived type are stored in a separate table with a foreign key back to the core table.
For instance:
Mapping ToTable for a TPT inheritance from base entity
public class Resort : Lodging
{
public string Entertainment { get; set; }
public string Activities { get; set; }
}
modelBuilder.Entity<Lodging>().Map(m =>
{
m.ToTable("Lodgings");
}).Map<Resort>(m =>
{
m.ToTable("Resorts");
});
Configuring for Table Per Concrete Type (TPC) Inheritance
Table Per Concrete Type (TPC) is similar to TPT, except that all the properties for each type are stored in separate tables. There is no core table that contains data common to all types in the hierarchy. This allows you to map an inheritance hierarchy to tables with overlapping (common) fields. This can be useful when you set ancient data aside in a spare table.
TPC mapping is configured using the MapInheritedProperties method, which is only
accessible from within the Map method. And since we also need a separate table for the derived class (that will be duplicating the inherited properties), we can combine the Table configuration and the MapInheritedProperties configuration.
modelBuilder.Entity<Lodging>()
.Map(m =>
{
m.ToTable("Lodgings");
})
.Map<Resort>(m =>
{
m.ToTable("Resorts");
m.MapInheritedProperties();
});
Tip:How to choose an Inheritance Strategy
Working with Abstract Base Classes
if we modify the Lodging class to be an abstract base class. That means we’ll never use this class directly. It cannot be instantiated. Instead, we ill only derive from it. Thus, we add a second derived class: Hostel
abstract public class Lodging
{
public int LodgingId { get; set; }
public string Name { get; set; }
public string Owner { get; set; }
public decimal MilesFromNearestAirport { get; set; }
public List<InternetSpecial> InternetSpecials { get; set; }
public Nullable<int> PrimaryContactId { get; set; }
public Person PrimaryContact { get; set; }
public Nullable<int> SecondaryContactId { get; set; }
public Person SecondaryContact { get; set; }
public int DestinationId { get; set; }
public Destination Destination { get; set; }
}
public class Resort : Lodging
{
public string Entertainment { get; set; }
public string Activities { get; set; }
}
public class Hostel: Lodging
{
public int MaxPersonsPerRoom { get; set; }
public bool PrivateRoomsAvailable { get; set; }
}
Code First convention, which means that the inheritance will revert to TPH. All of the fields from the derived classes are contained in the Lodgings table.
CRUD which stands for an operation of creating, read, update, and delete documents.
GRAYSONS 