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