Source Code

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 ICollection<LibraryCard> LibraryCards { get; set; }
public virtual ICollection<Magnifier> Magnifiers { get; set; }
public virtual ICollection<Pencil> Pencils { get; set; }
public virtual Bicycle Bicycle { get; set; }
[Required]
public virtual ReadingRoom ReadingRoom { get; set; }
public virtual ICollection<Book> Books { get; set; }
public virtual ICollection<PersonUmbrella> PersonUmbrellas { get; set; }
public virtual ICollection<Furniture> Furnitures { get; set; }

public Person()
{
    LibraryCards = new HashSet<LibraryCard>();
    Magnifiers = new HashSet<Magnifier>();
    Pencils = new HashSet<Pencil>();
    Books = new HashSet<Book>();
    PersonUmbrellas = new HashSet<PersonUmbrella>();
    Furnitures = new HashSet<Furniture>();
}
....

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 DbSet<Furniture> Furnitures { 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 DbSet Furnitures { 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

Source Code

Entity Framework 5 Code First Relationships

Enumerables

The Professor solves a spelling problem.

After lunch, (deviled eggs and radishes for the Professor, which made Edward gag and silently thank his mother for her peanut butter and kiwi sandwich), the Professor continued discussing umbrellas.

I’m afraid, [he said clearly, despite the radish in his mouth], I’ve deceived you. While it’s true that an umbrella changes color depending on who uses it, we do not store the color as a word. That is, not as a string. We tried that, but the umbrellas would misspell the color names. Another mystery that Mrs. Gamp assures me she’ll solve some day.

To keep my own sanity, if not anyone else’s, in the face of the color blue being spelled “blew,” we required the umbrellas to choose from a list of colors. An enumeration. This has only been possible recently, and is quite practical and intuitive despite the challenges to the Framework designers.

For our example, we’ll say an umbrella can be one of three colors.* Here is the updated class schema.

LearningEFSchema.cs

public class PersonUmbrella
{
    //Primary Key
    [Key, Column(Order = 0)]
    public int PersonId { get; set; }
    [Key, Column(Order = 1)]
    public int UmbrellaId { get; set; }

    [Required]
    public UmbrellaColors Color { get; set; }

    //Navigation
    public virtual Person Person { get; set; }
    public virtual Umbrella Umbrella { get; set; }
}

public enum UmbrellaColors
{
    Red,
    Green,
    Blue
}

Notice we have changed the Color property to use an Enum. Now, we change the code.

Program.cs

PersonUmbrella personUmbrella = new PersonUmbrella()
{
    Person = edward,
    Umbrella = umbrella,
    Color = "Red"
};
edward.PersonUmbrellas.Add(personUmbrella);
Console.WriteLine("Jane uses it, too.");
jane.PersonUmbrellas.Add(new PersonUmbrella()
{
    Umbrella = umbrella,
    Color = "Green"
});

And that is all! We run the program, which produces exactly the same results.

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
Finished

Our table has changed, though. We now store the integer value of the Enum.

T-SQL

create table PersonUmbrellas (
 PersonId int not null foreign key references Persons(PersonId),
 UmbrellaId int not null foreign key references Umbrella(UmbrellaId),
 Color int not null,
 constraint pkPersonUmbrella primary key (PersonId, UmbrellaId)
)

Wonderful!

*In reality, Mrs. Gamp’s umbrellas could be one of three hundred colors, and counting....

Enum Support (Code First) in Entity Framework 5
Enumeration Support in Entity Framework

Source Code

Entity Framework 5 Code First Relationships

Many to Many with Payload

Serendipity gets applied.

Rain poured for hours before Edward bicycled to the library, red umbrella overhead. He, with help from friends and the manufacturers, had modified his Herman to hold an umbrella. In hindsight, it didn’t do much to keep the rain off. But he thought it looked wicked, eclectic cool.

In the bicycle lot, he removed the umbrella and sprinted. Professor Relvar met him at the front doors, which surprised Edward.

“Excellent! You have an umbrella!”

“Yes, Professor. It’s, um, raining.”

The Professor frowned and tilted his head. “Yes, my boy, I see that. Why else do you think I’m here?”

Edward shrugged his shoulders. The Professor pointed pointedly at Edward’s umbrella.

“This?” asked Edward.

“Precisely. Synchronicity has dropped her handkerchief on the floor, and we must pick it up.”

“Sink or what?”

The Professor sighed. “Really, I must speak with the school board. Synchronicity. Coincidence in time. The rain forces you to bring an umbrella on just the day I planned to explain their configuration in the Framework. Come along.”

The Professor turned to the nearby bin and pulled out a clear umbrella, which abruptly turned sky blue. He opened it and cheerfully led Edward back out into the rain.

I love the rain, [he said, twirling the umbrella’s handle]. I often do my best thinking while walking in it. Admittedly, I also sometimes catch cold, but then I do even more thinking while convalescing. Eh, what’s that? Look it up.

Libraries keep a healthy supply of umbrellas on hand. A person, in her lifetime, could use any number of umbrellas, and an umbrella could be used by hundreds of people. On the face of it, this is another simple many-to-many relationship.

But our umbrellas have a rather lovely feature, being the result of that artisan and scientist Mrs. Freda Gamp. There is a story connected with one of her ancestors; I believe our Mrs. Gamp is making up for it. Regardless, as you know, our umbrellas are clear when in their library bin, but upon being taken out instantly transform into the holder’s favorite color at that moment! How she achieves this is a great mystery.

Now we have a complication. The color doesn’t belong to the umbrella, nor does it belong to the person (because it changes, you see). The color belongs to the combination. Our tables should end up like this.

T-SQL

create table Umbrellas (
 UmbrellaId int not null identity primary key,
 IsAutomatic bit not null
)

create table PersonUmbrellas (
 PersonId int not null foreign key references Persons(PersonId),
 UmbrellaId int not null foreign key references Umbrella(UmbrellaId),
 Color varchar(max) not null,
 constraint pkPersonUmbrella primary key (PersonId, UmbrellaId)
)

This relationship is called Many to Many with Payload. The payload is the Color property. For our Umbrella class, we indicate whether it opens automatically or manually. I prefer manual. You, I see, are more modern.

Unlike the Simple version, with Payload we must create a class for the join table. There are two ways to do this, of which I will instruct you in one.*

In our schema class changes:

LearningEFSchema.cs

//Navigation
public virtual ICollection<LibraryCard> LibraryCards { get; set; }
public virtual ICollection<Magnifier> Magnifiers { get; set; }
public virtual ICollection<Pencil> Pencils { get; set; }
public virtual Bicycle Bicycle { get; set; }
[Required]
public virtual ReadingRoom ReadingRoom { get; set; }
public virtual ICollection<Book> Books { get; set; }
public virtual ICollection<PersonUmbrella> PersonUmbrellas { get; set; }

public Person()
{
    LibraryCards = new HashSet<LibraryCard>();
    Magnifiers = new HashSet<Magnifier>();
    Pencils = new HashSet<Pencil>();
    Books = new HashSet<Book>();
    PersonUmbrellas = new HashSet<PersonUmbrella>();
}
....

public class Umbrella
{
    //Primary Key
    public int UmbrellaId { get; set; }
    public bool IsAutomatic { get; set; }

    //Navigation
    public virtual ICollection<PersonUmbrella> PersonUmbrellas { get; set; }

    public Umbrella()
    {
        IsAutomatic = false;
        PersonUmbrellas = new HashSetPersonUmbrella>();
    }
}

public class PersonUmbrella
{
    //Primary Key
    [Key, Column(Order = 0)]
    public int PersonId { get; set; }
    [Key, Column(Order = 1)]
    public int UmbrellaId { get; set; }

    [Required]
    public string Color { get; set; }

    //Navigation
    public virtual Person Person { get; set; }
    public virtual Umbrella Umbrella { get; set; }
}

Our Context class: note both PersonUmbrellas and Umbrellas are declared.

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Bicycle> Bicycles { get; set; }
    public DbSet<Book> Books { get; set; }
    public DbSet<LibraryCard> LibraryCards { get; set; }
    public DbSet<Magnifier> Magnifiers { get; set; }
    public DbSet<Pencil> Pencils { get; set; }
    public DbSet<Person> Persons { get; set; }
    public DbSet<PersonUmbrella> PersonUmbrellas { get; set; }

public DbSet<ReadingRoom> ReadingRooms { get; set; } public DbSet<Umbrella> Umbrellas { get; set; }

And our program. Again, we will make use of our wonderful librarian, Jane Eager, for the example.

Program.cs

Console.WriteLine("Create an umbrella for Edward");
Umbrella umbrella = new Umbrella() { IsAutomatic = true };
PersonUmbrella personUmbrella = new PersonUmbrella()
{
    Person = edward,
    Umbrella = umbrella,
    Color = "Red"
};
edward.PersonUmbrellas.Add(personUmbrella);
Console.WriteLine("Jane uses it, too.");
jane.PersonUmbrellas.Add(new PersonUmbrella()
{
    Umbrella = umbrella,
    Color = "Green"
});
SaveChanges();
Console.WriteLine("PersonUmbrellas: "
    + _db.PersonUmbrellas.Count());
Console.WriteLine("Edward's Umbrella: "
    + edward.PersonUmbrellas.First().Color);
Console.WriteLine("Jane's Umbrella: "
    + jane.PersonUmbrellas.First().Color);

We run our now somewhat long program:

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
Finished

I leave it to you, Edward, to pay particular attention to the program code. We must now work with the join table explicitly, and be careful in doing so.

Let us walk back to the library. Ah! I see the sun has come out. Oh well, I have thought enough for this morning, and lunch beckons with her frail arms!

*The Professor chooses the easier and more common method, where the PersonUmbrellas table’s primary key is a composite of the Person and Umbrella primary keys. This works well if the join table, itself, does not have a relationship to another table. But what if an umbrella had zero or more umbrella cases? While we could carry the PersonId+UmbrellaId key forward as a foreign key in the UmbrellaCase table, many prefer to add a new primary key to the PersonUmbrellas table, and make PersonId+UmbrellaId a unique key (aka candidate key). I show how this is done in the Epilogue.

Synchronicity
Convalesce
Sarah Gamp
Create code first, many to many, with additional fields in association table