Parts
1) Preface2) Introduction
3) A Simple Table
4) One to One or Many
5) Zero or One to Zero or Many
6) One to Zero or Many
7) One to Zero or One
8) One to One
9) Many to Many, Simple
10) Many to Many with Payload
11) Enumerables
12) Inheritance
Entity Framework 5 Code First Relationships
Inheritance
Our hero’s adventure ends where it began, but with a new beginning.The next day, Edward was told at the front desk to go to Professor Relvar’s room in Archives. When he arrived, Edward was astonished to find that the room was immaculately clean. His open mouth caused the Professor to chuckle.
“Didn’t think I had it in me, did you?”
Edward stared. “No, sir.” Then he reddened. “I mean...um...that is....”
The Professor waved him toward a sofa. “Quite all right, my boy, quite all right. I was your age once, when the Earth was cooling. One of my instructors habitually, when finished with his coffee, placed the cup upside down on the saucer. This led to a rather unsightly ring building up all year long. We all commented on it amongst ourselves. Finally, one day, in the middle of class, one young lady named Peluca O’Grarian raised her hand and asked, ‘Sir, respectfully, that saucer’s gross. Do you ever clean it?’ Whereupon he picked it up, washed it in the nearby sink (it was a Chemistry classroom), set it down and announced, ‘Now why did it take so long for someone to mention that?’ I assure you, the lesson was not lost on me.
“But, that is not today’s subject. Are you sitting comfortably?”
“Yes, sir.”
“Then I shall begin.”
Every reader, [he said], loves being comfortable while reading. We all have furniture in our rooms. All furniture is manufactured by someone. But each piece of furniture has its own qualities. A chair could be a recliner, a sofa can have two or three cushions, etc.
How tedious it would be to duplicate the same column in multiple tables. In programming, we solve this through inheritance. Thankfully, we can apply the same concept to the database, using the same technique as we used for our Bicycle.
I will show you two ways in which we can represent chairs and sofas. One uses Table Per Type, which I prefer. The other--equally valid--uses Table Per Hierarchy. I will start with the latter.
Table Per Hierarchy
In this approach, we end up with...
LearningEFSchema.cs
[Person class] //Navigation public virtual ICollectionLibraryCards { get; set; } public virtual ICollection Magnifiers { get; set; } public virtual ICollection Pencils { get; set; } public virtual Bicycle Bicycle { get; set; } [Required] public virtual ReadingRoom ReadingRoom { get; set; } public virtual ICollection Books { get; set; } public virtual ICollection PersonUmbrellas { get; set; } public virtual ICollection Furnitures { get; set; } public Person() { LibraryCards = new HashSet (); Magnifiers = new HashSet (); Pencils = new HashSet (); Books = new HashSet (); PersonUmbrellas = new HashSet (); Furnitures = new HashSet (); } .... public abstract class Furniture { //Primary Key public int FurnitureId { get; set; } public string Manufacturer { get; set; } //Foreign Key public int PersonId { get; set; } //Navigation public virtual Person Person { get; set; } } public class Chair : Furniture { public bool IsRecliner { get; set; } public Chair() { IsRecliner = true; } } public class Sofa : Furniture { public int Cushions { get; set; } }
LearningEFDb.cs
public DbSetFurnitures { get; set; } create table Furnitures ( FurnitureId int not null identity primary key, Manufacturer nvarchar(max) null, IsRecliner bit null, Cushions int null, Discriminator nvarchar(128) not null )
Notice that we do not have navigation properties for types Chair and Sofa, nor--especially--are there DbSets for them. It is unintuitive, but you must add types of Furniture. Adding DbSets for Chair and Sofa results in undesired columns. Try it yourself, Edward, and you’ll see.
Here is the program update.
Program.cs
Console.WriteLine("Build a chair and a sofa");
Chair chair = new Chair() { Manufacturer = "Vincent's Chairs", IsRecliner = false };
Sofa sofa = new Sofa() { Manufacturer="Gaugin's Chairs", Cushions = 3 };
Console.WriteLine("Edward puts them in his reading room.");
edward.Furnitures.Add(chair);
edward.Furnitures.Add(sofa);
SaveChanges();
edward = _db.Persons.Single(a => a.Name.StartsWith("Edward"));
foreach (var piece in edward.Furnitures)
{
if (piece is Chair)
{
var thisChair = (Chair)piece;
Console.WriteLine(edward.Name + " has a chair made by " + thisChair.Manufacturer + ". " + "It " + (thisChair.IsRecliner ? "is" : "is not") + " a recliner.");
}
if (piece is Sofa)
{
var thisSofa = (Sofa)piece;
Console.WriteLine(edward.Name + " has a sofa made by " + thisSofa.Manufacturer + ". It has " + thisSofa.Cushions + " cushions.");
}
}
Run the program and the following table is created. (I will show the complete program output at the end, since it doesn’t change.)
T-SQL
create table Furnitures ( FurnitureId int not null identity primary key, Manufacturer nvarchar(max) null, IsRecliner bit null, Cushions int null, Discriminator nvarchar(128) not null, PersonId int null foreign key references Persons(PersonId) )
You’ll see that a single table is used for both types of furniture. In each table row, the Discriminator column contains either “Chair” or “Sofa,” which is how you know which type of furniture it is. The difficulty I have with this approach is that a person querying data from the database (which, I assure you, always happens) doesn’t know which columns correspond to which type. However, as I said, this is a valid approach and one you may find useful.
Now, on to my preference!
Table Per Type
The schema is the same as above, except in derived classes we explicitly set the table names. This tells the Framework to use a table per type.LearningEFSchema.cs
[Table("Chairs")]
public class Chair : Furniture
{
public bool IsRecliner { get; set; }
public Chair()
{
IsRecliner = true;
}
}
[Table("Sofas")]
public class Sofa : Furniture
{
public int Cushions { get; set; }
}
And also we add the DbSets for the tables.
LearningEFDb.cs
public DbSetFurnitures { get; set; } public DbSet Chairs { get; set; } public DbSet Sofas { get; set; }
When we run the program, we now get three (much more manageable) tables.
T-SQL
create table Furnitures ( FurnitureId int not null identity primary key, Manufacturer nvarchar(max) null, PersonId int null foreign key references Persons(PersonId) ) create table Chairs ( FurnitureId int not null primary key foreign key references Furnitures(FurnitureId), IsRecliner bit not null ) create table Sofas ( FurnitureId int not null primary key foreign key references Furnitures(FurnitureId), Cushions int not null )
Finally, the program’s complete output. Recall, the program hasn’t changed for the two approaches.
Create Person without a name. Saving changes. ERROR: The Name field is required. ERROR: The ReadingRoom field is required. Add the required name and Library Card. Add the Reading Room and save. Saving changes. Successful save. Get the record we just saved, and display. Person: Edward Farley has card: 123 and Reading Room: Lewis Wing Floor -3 Create and save Magnifier with required serial number Saving changes. Successful save. Retrieve Magnifier, show it doesn't belong to someone. Magnifier: Bar123, Person: available Edward gets the Magnifier. Save and retrieve. Saving changes. Successful save. Magnifier: Bar123, Person: Edward Farley Try to add duplicate magnifier Saving changes. ERROR: Magnifier with this serial nbr already exists. Add/save Pencil setting Nickname to null. Saving changes. ERROR: The Nickname field is required. Set Nickname to empty string and save Saving changes. Successful save. Retrieve pencil, change Nickname, save Pencil: , Person: Edward Farley Saving changes. Successful save. Pencil: Blackwing, Person: Edward Farley Attempt adding Bicycle without a Person Saving changes. ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Bicycles_dbo.Persons_PersonId". The conflict occurred in database "EFDb", table "dbo.Persons", column 'PersonId'. The statement has been terminated. Set the Person, save, retrieve and show Saving changes. Successful save. Model: Herman, Person: Edward Farley Add two books and save Saving changes. Successful save. Add another person and save Saving changes. Successful save. Each person has read both books Saving changes. Successful save. Edward Farley's books: The House of Arden, Harding's Luck Jane Eager's books: The House of Arden, Harding's Luck Create an umbrella for Edward Jane uses it, too. Saving changes. Successful save. PersonUmbrellas: 2 Edward's Umbrella: Red Jane's Umbrella: Green Build a chair and a sofa Edward puts them in his reading room. Saving changes. Successful save. Edward Farley has a chair made by Vincent's Chairs. It is not a recliner. Edward Farley has a sofa made by Gaugin's Chairs. It has 3 cushions. Finished
The Professor closed his computer lid and tossed it to the floor. Edward heard a whoosh of air and saw the computer glide across the tile and gently bump into a corner.
“Cool!”
The Professor smiled. “Hover Tablet. I am immodest enough to admit it is my invention. Still some kinks to work out, though. Last week it shot up to the ceiling. The maintenance department was distraught at the paint damage.
“And now, Edward, we have come to the end. This is not all I can teach you, but all I will for now. Please give your parents my kindest regards. They were excellent pupils, and, despite some wandering, you have been a delight.”
“Thanks, Professor,” said Edward, still eyeing the tablet.
“Then, let me walk you to the door.”
As Edward left, the Professor called to him.
“Young man, you have a special event coming up soon, I believe?”
Edward shook his head, then nodded grinning. “Yeah, my birthday. I’ll be a teenager. Mom says she’s going on vacation for six years.”
“I’m sure she’s exaggerating. Well, then, that was simply a fact I wanted to confirm. Goodbye, my boy, goodbye.”
The Professor re-entered his room, closed his door, then walked to his desk, sat and picked up a very old phone.
“Mrs. Eager? Would you be so kind as to send up a box and wrapping? Hmm? Oh, about the size of a tablet computer should do.”
“Are you sitting comfortably?” --Prokofiev’s Peter and the Wolf
Chairs by Van Gogh and Gaugin
