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

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