Source Code

Entity Framework 5 Code First Relationships

Many to Many, Simple

Edward reminds the Professor of something important. The Professor expounds on the meaning of a library.

“Professor,” said Edward the next day, “something has been bothering me.”

“Oh, what is that my boy?”

“Well, you’ve explained about library cards and magnifiers and pencils and bicycles and reading rooms. But, you haven’t said anything about books. Isn’t that what a library is for?”

The Professor became serious. “Edward, books are a great treasure. They have survived as a means for storing folly and wisdom for centuries. If you could sum up all living people’s knowledge, it would not equal the knowledge found in a library. The word library, indeed, means collection of books. But, no, our modern library is not simply for books. It is for encountering knowledge outside of ourselves. When we wander these aisles and open a book’s covers, we travel through time and space into another human’s imagination. This is why we get ‘lost’ in a book. But we are not losing ourselves. We are finding the cosmos and our place in it.”

After a few seconds, the Professor left the room, beckoning Edward to follow.

Look around you, [he said]. Books! Music! Paintings! How many books can a person read in a lifetime? How many readers has a book had?

This is a simple relationship. Many to Many. A person reads many books, a book has many readers. The Framework makes this relationship even easier. In the database, such a relationship requires a joining table. Here are the tables we’re about to create.

T-SQL

create table Books (
 BookId int not null primary key,
 Title nvarchar(max) not null
)

create table PersonBooks (
 PersonId int not null foreign key references Persons(PersonId),
 BookId int not null foreign key references Books(BookId),
 constraint pkPersonBook primary key (PersonId, BookId)
)

In this case, a diagram may help.

A many-to-many relationship is two one-to-many relationships that meet in the middle, so to speak.

While we could (and tomorrow will) explicitly create a joining class, for this simple case we don’t need to. The Framework manages the join behind the scenes.

LearningEFSchema.cs

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

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

public class Book
{
    //Primary Key
    public int BookId { get; set; }
    [Required]
    public string Title { get; set; }

    //Navigation
    public virtual ICollection<Person> Persons { get; set; }

    public Book()
    {
        Persons = new HashSet<Person>();
    }
}

In our Context, we have some extra work to do. As with our previous examples, the Framework will happily create foreign key names according to its whims.* Moreover, it will create the joining table with a name that I personally find unattractive. And so, dear Edward, we must venture into your father’s favorite realm, the Fluent API.

First, we add the DbSet as usual.

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Bicycle> Bicycles { get; set; }
    public DbSet<Book> Books { get; set; }

Now, we override the OnModelCreating procedure and explicitly tell the Framework how we want things named. I have tried many times to enforce my desired naming via attributes, to no avail. Someday I must ask the fine people who manage the Framework why they seemingly overlooked such useful attributes for such a common relationship. But here we are:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .HasMany(e => e.Books)
        .WithMany(e => e.Persons)
        .Map(t => t
            .MapLeftKey("PersonId")
            .MapRightKey("BookId")
            .ToTable("PersonBooks")
            );
}

Back to our program. To show the many-to-many relationship, we will enlist the help of our nearby Librarian.

[They had reached an information desk, and an elderly lady smiled at the Professor.]

Mrs. Jane Eager, I am illustrating for young Edward Farley, here, the many-to-many relationship. Would you work your magic on that temperamental machine and find books you and Edward have both read? [She typed and showed him the screen.] Really? Edward, I must say, you have been well raised. These titles come from a writer who is a master of us all. Now, attend, Edward. There is quite a bit of code.

Program.cs

Console.WriteLine("Add two books and save");
Book book1 = new Book() { Title = "The House of Arden" };
Book book2 = new Book() { Title = "Harding's Luck" };
_db.Books.Add(book1);
_db.Books.Add(book2);
SaveChanges();

Console.WriteLine("Add another person and save");
Person person2 = new Person() { Name = "Jane Eager" };
person2.LibraryCards.Add(person.LibraryCards.First());
person2.ReadingRoom = new ReadingRoom() { Location = "NorthEast Wing" };
_db.Persons.Add(person2);
SaveChanges();

Console.WriteLine("Each person has read both books");
person.Books.Add(book1);
person.Books.Add(book2);
person2.Books.Add(book1);
person2.Books.Add(book2);
SaveChanges();
Console.WriteLine(person.Name + "'s books: " 
    + String.Join(", ", person.Books.Select(a => a.Title)));
Console.WriteLine(person2.Name + "'s books: " 
    + String.Join(", ", person.Books.Select(a => a.Title)));

Let’s run the 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
Finished

“And there you have the many-to-many relationship! Thank you, Jane,” said the Professor. When they reached Edward’s reading room, the Professor paused and said quietly, “Edward, you may not know this, but Jane’s husband is a fine author himself. If you enjoyed the the masterful E. Nesbit’s writing, you would surely like his as well. Come, let me show you....”

*This isn’t quite fair on the Professor’s part. Entity Framework is consistent in its naming.

Library
Pinakes
E. Nesbit, author of The House of Arden and Harding’s Luck, and others
Edward Eager, author of Half Magic and others

Source Code

Entity Framework 5 Code First Relationships

One to One

Edward’s reading room is scrutinized. The Professor solves a paradox.

Edward’s parents sent him off with some of the Sunderland Library ice cream that he, Edward, had thoughtfully brought home for them as a gift.

“Yes, thank you, dear,” said his mother, “we quite loved the adventure of trying Cricket and Cream Sherbet, and feel you have earned the rest.”

Once again, he met the Professor at Edward’s reading room. This time he (Edward) was five minutes early. Inside the room, the Professor walked around, commenting on particular books (“Excellent imagery.”), magazines (“Dreadful, but you’re too young to know.”), paintings (“I’d have preferred an orange cloud, but then I am not an artistic genius.”) and Edward’s homework (“Do they not teach grammar anymore? Never pluralize with an apostrophe, dear boy. That rule’s as certain as the rising sun.”)

“Well, Edward, the reason we are here, is that here is the reason for our reason.”

The Professor waited, expectantly. Edward knew his part by now. “What, exactly, does that mean, Professor?”

It means, [the Professor said, reclining on the couch], that to be in a library is to be studious, to be studious one must have an environment conducive to study, and whatever one’s study, that study must contain an element of reason.

When a person is born, he or she is assigned a library card. More library cards can be obtained (especially when they involve ice cream). Each person is also assigned a Reading Room in that first library.

This is our oddest relationship yet. It is One to One. To our human, intuitive mind, it is natural. For example, a person has one left hand, a left hand belongs to one person. But the database underlying our Framework cannot accomplish this. Look here. [He projected code onto the wall using his personal laptop.] This is T-SQL that will create a one-to-one relationship.

create table One (
 OneId int not null identity primary key,
 Name varchar(10) null
)

create table Two (
 OneId int not null primary key references One(OneId),
 Name varchar(10)
)

alter table One add constraint fk1 foreign key (OneId) references Two(OneId)

Yet, if you run the following statements, they will fail catastrophically.

insert One(Name) values('asdf')
insert Two(Name) values('qwer')

Why, Edward?

[“Because...” Edward said slowly, “because you’re telling table One it has a record before table Two has one, but table Two has to have one because they’re one-to-one. Right?”]

Precisely. I could hardly have said it more clearly myself. It is a paradox. We must insert both records at the same time, but the database won’t allow us to do that. There may be databases that can, but--well, we do not speak of those here. [And the Professor smiled mysteriously.]

By the way, here is the script to remove those two tables, since they now have foreign key relationships to each other!

alter table One drop constraint fk1
drop table Two
drop table One

What are we to do? More exactly, what did we do? Fortunately, we can employ a--I hesitate to use the word, but it fits--hack. Hmm. Makes me think of sick cats.

We’ll tell the Framework to create a One to Zero or One table relationship, but enforce the one-to-one requirement in the classes. Our classes won’t complain, because we are able to assign the relationships simultaneously.

LearningEFSchema.cs

public class Person: IValidatableObject
{
    //Primary Key
    public int PersonId { get; set; }
    [Required]
    public string Name { get; set; }

    //Navigation
    public virtual ICollection<LibraryCard> LibraryCards { get; set; }
    public virtual ICollection<Pencil> Pencils { get; set; }
    public virtual ICollectionMagnifier> Magnifiers { get; set; }
    public virtual Bicycle Bicycle { get; set; }
    [Required]
    public virtual ReadingRoom ReadingRoom { get; set; }

public class ReadingRoom
{
    //Primary Key same as Person
    [Key, ForeignKey("Person")]
    public int PersonId { get; set; }
    [Required]
    public string Location { get; set; }

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

As with the Bicycle, the ReadingRoom requires a person. But now the Person’s ReadingRoom property is required as well. We update the Context.

LearningEFDb.cs

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

Now, we must update our code. Unlike before, we can’t simply append a reading room. It is now intrinsically linked to a person. We must modify our very first instantiation of a person.

Program.cs

Console.WriteLine("Create Person without a name.");
Person edward = new Person();
_db.Persons.Add(edward);
SaveChanges();
Console.WriteLine("Add the required name and Library Card.");
edward.Name = "Edward Farley";
edward.LibraryCards.Add(new LibraryCard() { Number = 123 });
Console.WriteLine("Add the Reading Room and save.");
edward.ReadingRoom = new ReadingRoom() { Location = "Lewis Wing Floor -3" };
//Must Save Changes after library card AND reading room have been added.
SaveChanges();
Console.WriteLine("Get the record we just saved, and display.");
edward = _db.Persons.First();
Console.WriteLine("Person: " + edward.Name 
    + " has card: " + edward.LibraryCards.First().Number
    + " and Reading Room: " + edward.ReadingRoom.Location);

Our program runs as expected, with just the first part changing.

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
....
It should be clear, but just in case it is not: the database table is shown below, and does not enforce a one-to-one relationship. Though there might be some clever way to make it do so!
T-SQL
create table ReadingRooms (
 PersonId int not null primary key foreign key references Persons(PersonId),
 Location nvarchar(max) not null
)

That’s quite enough for today. Tomorrow we leave the realm of “one to something” cardinality, and venture into new relationships. Ah, Edward, do I spy Cricket and Cream Sherbet? Outstanding!

T-SQL create relationships
Library of Congress Main Reading Room

Source Code

Entity Framework 5 Code First Relationships

One to Zero or One

In which the Professor demonstrates that travel is educational.

Edward had sprinted from the bicycle lot, walked quickly (but did not run) in the library’s main floor, then took the stairs two at a time and jogged to his reading room where Professor Relvar was waiting. Edward was exactly thirty-seven seconds early.

“My dear boy, you look exhausted.” The Professor smiled slightly and adjusted his reading glasses. “Isn’t your bicycle in good repair?”

“Sure it is, Professor,” said Edward, huffing between words. “My dad helps me with it. Mom, too.”

“To be sure. Your mother was always mechanically inclined. Well, I’m glad to hear your conveyance is functionally sound. We sojourn now to the bicycle lot, and from there to Sunderland Library.”

Edward’s eyes widened, imagining even more bicycling, but he didn’t say anything. In the lot, they retrieved their bicycles and started off. The Professor rode a classic Kermit. Edward pedaled a bright red Herman. As they rode, the Professor lectured....

Not everyone owns a Bicycle, [he said]. Many people enjoy walking to the library. I myself walked for years, until I spied a picture of this lovely two-wheeler on display at a Henson shop garage sale. (I did not buy the garage, however.) Bicycles are manufactured to order, so each person’s is unique. Choosing a bicycle is, as you know, a delicate thing. For various historical, practical and aesthetic reasons, a person doesn’t have to have a bicycle, but once built it’s his or hers for life. Bicycle makers are proud of their ability to maintain and modify a bicycle for decades.

A person doesn’t have to a have a bicycle, but a bicycle must have an owner. We have a One to Zero or One relationship, and it is a bit peculiar.

[Edward had been frowning, and raised his hand. He could ride pretty well one-handed. “Yes?” asked the Professor. Edward said, “Why wouldn’t you just add properties to the Person class--you know, table columns--like HasBicycle and BicycleModel?”]

Ah, excellent! I was beginning to think your parents had misinformed me about your aptitude.

The answer is, you could. But what if, later, you wanted to know more about this bicycle? You might end up with twenty properties. Would it make sense to have those all in the Persons table when a person might not even own a bicycle? No.*

I will describe the way I solve this problem, and you will have to imagine the code.** First, the schema.

LearningEFSchema.cs

public class Person: IValidatableObject
{
    //Primary Key
    public int PersonId { get; set; }
    [Required]
    public string Name { get; set; }

    //Navigation
    public virtual ICollection<LibraryCard> LibraryCards { get; set; }
    public virtual ICollection Pencils<Pencil> { get; set; }
    public virtual ICollection Magnifiers<Magnifier> { get; set; }
    public virtual Bicycle Bicycle { get; set; }
    ....

public class Bicycle
{
    //Primary Key same as Person
    [Key, ForeignKey("Person")]
    public int PersonId { get; set; }
    [Required]
    public string Model { get; set; }

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

The important items are that the Person class references just one Bicycle that is not required, and Bicycle references just one Person where the PersonId property is both the primary key and the foreign key. In essence, we are extending the Person table.

The Context class gets the expected DbSet.

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Bicycle> Bicycles { get; set; }

We add to our program.

Program.cs

Console.WriteLine("Add Bicycle without a Person");
Bicycle bicycle = new Bicycle() { Model = "Herman" };
_db.Bicycles.Add(bicycle);
SaveChanges();
Console.WriteLine("Set the Person, save, retrieve and show");
bicycle.Person = edward;
SaveChanges();
bicycle = _db.Bicycles.First();
Console.WriteLine("Model: " + bicycle.Model + ", Person: " + bicycle.Person.Name);

And run it.

Create Person without a name.
Saving changes.
  ERROR: The Name field is required.
Add the required name and Library Card and save.
Saving changes.
Successful save.
Get the record we just saved, and display.
Person: Edward Farley has card: 123
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
Add 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
Finished

It is not a lovely error, but suits our educational purpose. The resulting table would be scripted so:

T-SQL

create table Bicycles (
 PersonId not null primary key foreign key references Persons(PersonId),
 Model nvarchar(max) not null
)

With that, Edward and Professor Relvar had arrived at Sunderland Library which, as anyone knows, maintains the finest collection of ice cream recipe books found anywhere. Plus, chefs who prepare scoops for anyone who cares to taste.

“And if it weren’t for the bicycles,” the Professor stated, licking happily at his Chocolate Peanut Butter and Pretzel Sundae, “all Sunderland regulars would be fat. Another scoop, Edward?”

*Though some would say, “Yes.” The question is when to use table-per-type, table-per-hierarchy, or table-per-concrete-type, which will come up again in Inheritance. Also, the Professor might be thinking ahead too much. Some Agile development methods would say to do the simplest thing first (TPH), then change to TPT if needed. The fact is, the author doesn’t like TPH and discriminator columns.

**Which, fortunately, we don’t have to wait for.

The Muppet Movie
Pee-wee’s Big Adventure
EF Inheritance Models
The Perfect Scoop