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