Pages

Sunday, May 19, 2013

Edward Farley and the Fantastic Library Part 12

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

Edward Farley and the Fantastic Library Part 11

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

Edward Farley and the Fantastic Library Part 10

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

Edward Farley and the Fantastic Library Part 9

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

Edward Farley and the Fantastic Library Part 8

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

Edward Farley and the Fantastic Library Part 7

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

Edward Farley and the Fantastic Library Part 6

Source Code

Entity Framework 5 Code First Relationships

One to Zero or Many

Our hero see his pencils in a new light.

“Take me to your reading room,” commanded the Professor. “We shall eat en route.”

Edward’s room was modest. He was too young to have acquired many items, but he hoped someday to study something so interesting that he’d have to build an addition. For now, he just had a desk, some chairs and a couch. Plus, his framed mosaic of his favorite authors.

“Yes, yes. Quite nice, though it lacks some fire. We’ll see if we can’t kindle you, eh?”

The Professor laughed to himself. Apparently this was a joke only he understood. He suddenly stepped to the desk.

“See? Here?”

Edward timidly shook his head.

“Your pencils, my boy. How many?”

“Three. Sir.”

Professor Relvar picked them up, scrutinizing. “Good quality wood. Decent whittling. Proud of your work?”

“Yes, I guess so. I’ve seen nicer. My dad’s are fantastic!”

“Just so! Just so! We can appreciate our work while being modest. Have you considered this remarkable object? We value them highly, teaching everyone how to whittle and assemble their own pencils. Works of art, some people’s. Ms. Turner turns out terrific ones--or so she tongue-twistingly intones. Would you buy a pre-made pencil? Of course not! You dream of making your own. We’re all the same in this way. The pencil is today’s subject.”

Everyone, [he continued, sitting behind the desk], can have a Pencil. Not everyone does, and, while I personally don’t understand them, many people have done great work without a pencil. So, a person can whittle and own zero or more pencils. But a pencil always belongs to just one person. This is called a One to Zero or Many relationship, and it is very common. In fact, it may be the most common relationship.

Here is how we’ll model it:

LearingEFSchema.cs
public class Person...
...
//Navigation
public virtual ICollection<LibraryCard> LibraryCards { get; set; }
public virtual ICollection<Magnifier> Magnifiers { get; set; }
public virtual ICollection<Pencil> Pencils { get; set; }

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

public class Pencil
{
    //Primary Key
    public int PencilId { get; set; }
    [Required(AllowEmptyStrings = true)]
    public string Nickname { get; set; }
    //Foreign Key column
    public int PersonId { get; set; }

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

    public Pencil()
    {
        Nickname = "";
    }
}

LearningEFDb.cs
public class LearningEFDb: DbContext
 {
     public DbSet<LibraryCard> LibraryCards { get; set; }
     public DbSet<Magnifier> Magnifiers { get; set; }
     public DbSet<Pencil> Pencils { get; set; }
     public DbSet<Person> Persons { get; set; }

We introduce one new wrinkle, today. We’re requiring a Nickname, and yet we’re not! We allow an empty string. Why, you may ask?

[The Professor waited patiently until Edward sheepishly asked, “Why, Professor?”]

I’m so glad you asked. Many fields should not allow nulls. Names are a good example. Let’s say we stored first, middle and last names in nullable table columns. To concatenate them and display the full name, we would have to convert nulls to empty strings, otherwise the full name would become null. I’m sure there’s something philosophical to say about how objects added to a void becoming the void, but that isn’t my field.

Let’s add to our program and try to set a pencil’s nickname to null.

Program.cs
Console.WriteLine("Add/save Pencil setting Nickname to null.");
Pencil pencil = new Pencil() { Nickname = null, Person = edward };
_db.Pencils.Add(pencil);
SaveChanges();
Console.WriteLine("Set Nickname to empty string and save");
pencil.Nickname = "";
SaveChanges();

Console.WriteLine("Retrieve pencil, change Nickname, save");
pencil = _db.Pencils.First();
Console.WriteLine("Pencil: " + pencil.Nickname + ", Person: " + pencil.Person.Name);
pencil.Nickname = "Blue";
SaveChanges();
pencil = _db.Pencils.First();
Console.WriteLine("Pencil: " + pencil.Nickname + ", Person: " + pencil.Person.Name);

Running the program now produces a Pencils table like so:

T-SQL
create table Pencils (
 PencilId int not null identity primary key,
 Nickname nvarchar(max) not null,
 PersonId int not null foreign key references Persons(PersonId) 
)

And produces the following output:

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
Finished

Tomorrow, Edward, we’ll meet here. Do not keep me waiting! That’s simply bad manners. Now, what are you grinning at?


Sunyata (Void-ness)
Vocabulary of Interlinked Datasets
Blackwing Pencil

Edward Farley and the Fantastic Library Part 5

Source Code

Entity Framework 5 Code First Relationships

Zero or One to Zero or Many

The Professor produces his favorite Magnifier.

“Hi, Professor,” said Edward Farley. “Mom added raisins to your sandwich.”

The Professor accepted the sandwich with a delighted smile, then scowled and said, “Today, young man, I will lavish great salivary care on this culinary delicacy before accepting interrogatives.”

“Huh?” said Edward.

“I’m not answering any questions until I’ve eaten.”

“Oh.”

They ate in silence, the older man clearly enjoying himself and the torture he was inflicting on his impatient student. Finally, the last crumb tasted, they set to work and the Professor began to lecture.

Observe, Edward, [he began]. I hold in my hand a prized possession. My first magnifying glass. It’s an unfortunate truth of age that our eyes fail. Also, there are several rather fine books with print so small that one needs a good magnifier to read them. (The print was small to conserve paper, before we learned to make artificial paper. I applaud the reduced resources, but I do miss the aroma of those old tomes.)

Magnifiers are hand-crafted in Birmuthe. Lovely work. A person blessed with excellent vision doesn’t have to own one. A magnifier might not ever be used, but if it is it belongs to that person for life.

This is a Zero or One to Zero or Many relationship. A magnifier doesn’t require a person, and a person can have zero or more magnifiers. Here is how the relationship is modeled.

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<Magnifier> Magnifiers { get; set; }

    public Person()
    {
        LibraryCards = new HashSet<LibraryCard>();
        Magnifiers = new HashSet<Magnifier>();
    }

public class Magnifier 
{
    //Primary Key
    public int MagnifierId { get; set; }
    [Required]
    public string SerialNbr { get; set; }
    //Foreign Key column
    public int? PersonId { get; set; }

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

LearningEFDb.cs
public class LearningEFDb: DbContext
{
    public DbSet<LibraryCard> LibraryCards { get; set; }
    public DbSet<Magnifier> Magnifiers { get; set; }
    public DbSet<Person> Persons { get; set; }

You have already observed that this is almost identical to our Library Card. The differences are:

A person doesn’t have to have a magnifier, so there is no validation for that.

A magnifier doesn’t require a person, so the PersonId foreign key property is nullable.

If we run our program, the following table gets created.

T-SQL
create table Magnifiers (
 MagnifierId int not null identity primary key,
 SerialNbr nvarchar(max) not null unique nonclustered,
 PersonId int null foreign key references Persons(PersonId)
)

And, we’re finished, right? Wrong! We have a problem. In fact, we have a problem that exists in Library Cards, too, and that I was waiting for you to cry havoc about and let slip the dogs of code. Pardon me? Really, my boy, you should read more Shakespeare.

The problem in Library Cards is with their Number, and in Magnifiers with their SerialNbr. Do you suppose two cards or magnifiers can have the same number? I should say not. Mr. Barrows at Birmuthe is very careful on that point. Each magnifier is given a unique serial number.

We already have a primary key (MagnifierId) on our Magnifier table, which uniquely identifies a row. The SerialNbr column is called by some a candidate key. It could be the primary key, but isn’t. Why don’t I just use it as my primary key? Because I don’t like to. It’s really that simple. Oh, I could go on and on about how consistency is glorious, and how Mr. Barrows might decide to recall all his magnifiers and change their serial numbers (or a library could change its card numbering scheme), and those are, indeed, good reasons. But they can be worked around.

The fact is, I like having a consistent primary key in both name and type*.

So, our serial number must be unique. How do we tell the Framework about that? Sadly, we don’t. At least, not directly. Remember, a class doesn’t know about the Framework, so it can’t check itself for uniqueness. There are no doubt other (possibly more clever) solutions than the one I’m about to show. Perhaps you, Edward, will improve on my work, eh?

We turn to our IValidatable interface, and add code that, in effect, tells the class “If you receive any errors from the context, return them as validation errors.” We are coupling the class to a context, but notice that a ValidationContext does not have to be a Framework context. In our case, of course, it will be the DBContext.

LearningEFSchema.cs
public class Magnifier : IValidatableObject
{
    //Primary Key
    public int MagnifierId { get; set; }
    [Required]
    public string SerialNbr { get; set; }
    //Foreign Key column
    public int? PersonId { get; set; }

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

    IEnumerable IValidatableObject.Validate(ValidationContext validationContext)
    {
        //Check for any Items in context. If they're ValidationResults, yield them.
        if (validationContext.Items != null)
        {
            foreach (var item in validationContext.Items)
            {
                if (item.Value is ValidationResult)
                {
                    yield return (ValidationResult)item.Value;
                }
            }
        }
    }
}

In our Context, we add a using statement:

LearningEFDb.cs
using System.ComponentModel.DataAnnotations;

We add a method to check for duplicate SerialNbrs.

public bool IsUniqueMagnifierSerialNbr(Magnifier magnifier)
{
    var entity = this.Magnifiers.SingleOrDefault(a => a.MagnifierId != magnifier.MagnifierId & a.SerialNbr == magnifier.SerialNbr);
    return (entity == null);
}

Finally, we override the ValidateEntity method, and add the following code.

protected override System.Data.Entity.Validation.DbEntityValidationResult ValidateEntity(System.Data.Entity.Infrastructure.DbEntityEntry entityEntry, IDictionary<object, object> items)
{
    if (items == null) { items = new Dictionary<object, object>(); }
    //Magnifier Serial Number     
    if (entityEntry.Entity is Magnifier) { var entity = (Magnifier)entityEntry.Entity; 
        if (!IsUniqueMagnifierSerialNbr(entity)) 
        { 
            items.Add(new KeyValuePair(Guid.NewGuid(), new ValidationResult("Magnifier with this serial nbr already exists.", new List<string>() { "SerialNbr" }))); 
        } 
    } 
    return base.ValidateEntity(entityEntry, items);
}

Do you see? The Framework validates for uniqueness when the entity, itself, is being validated, and tells the entity if there were errors. Unfortunately, the Framework is unable (some would say unwilling) to create the table with unique columns**. Let’s add to our program a rather lengthy test.

Program.cs
Console.WriteLine("Create and save Magnifier with required serial number");
Magnifier magnifier = new Magnifier() { SerialNbr = "Bar123" };
_db.Magnifiers.Add(magnifier);
SaveChanges();
Console.WriteLine("Retrieve Magnifier, show it doesn't belong to someone.");
magnifier = _db.Magnifiers.First();
Console.WriteLine("Magnifier: " + magnifier.SerialNbr + ", Person: "
    + (magnifier.Person != null ? magnifier.Person.Name : "available"));
Console.WriteLine("Edward gets the Magnifier. Save and retrieve.");
magnifier.Person = edward;
SaveChanges();
magnifier = _db.Magnifiers.First();
Console.WriteLine("Magnifier: " + magnifier.SerialNbr + ", Person: "
    + (magnifier.Person != null ? magnifier.Person.Name : "available"));
Console.WriteLine("Try to add duplicate magnifier");
magnifier = new Magnifier() { SerialNbr = "Bar123" };
_db.Magnifiers.Add(magnifier);
SaveChanges();
//Clean up. Remove the magnifier;
_db.Magnifiers.Remove(magnifier);

Run the program. Ah, it works as expected! (If only this were more often the case, my boy.)

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.
Finished

That should give you enough to think about, young man. If, in fact, you do any thinking at all.


*The Professor later wrote to Edward, saying there are practical exceptions to this. For instance, a Logins table might use GUIDs as primary keys to reduce security risks. A States table should use the state abbreviation as the key, and likewise a zip code table. These relationships are already established by the US Postal Service. **We could, however, add the unique constraint when the database is created by EF. Here’s the code that might run in a DbContext DatabaseInitializer.

************* Unique Constraints ***************
db.Database.ExecuteSqlCommand("ALTER TABLE AppMachines ADD CONSTRAINT u_AppIdMachineId UNIQUE(AppId,MachineId)");
************************************************

“Cry ‘Havoc!’ and let slip the dogs of war.”

Edward Farley and the Fantastic Library Part 4

Source Code

Entity Framework 5 Code First Relationships

One to One or Many


The day that Edward understands the Library Card.

Edward knocked on Professor Relvar’s door and waited fairly patiently for him to answer. Once inside, they sat and Edward immediately started talking.

“Professor, here’s the sandwich you asked for. My mom says she sprinkled cinnamon on it, and she’s betting you like it. And my dad wants to know if you’re going to tell me about--” Edward screwed up his eyes in thought, “being fluent and happy.”

The Professor smiled and said, “I suspect you mean the Fluent API. Yes, yes, your father always loved using that, and he’s not alone. Since you clearly want to get started right away, I’ll have to nibble on this delectable sandwich while explaining.”

We can describe, [he continued], our relationships to the library two ways. One is declaratively, using attributes on the class and its procedures. The other is overriding the OnModelCreating method in our data context and using the Fluent API. You’ll hear a lot of noise over this, but I assure you one way is not better than the other. They are different, that’s all. I myself have used both, but I confess to preferring attributes.

Many like the fluent style because they can keep their classes pristine. POCO is the humorous acronym people use--plain old CLR objects. With this approach, the classes aren’t explicitly database oriented.
I, however, like decorating my classes as much as practical. I already know they’ll be used to interact with the database, and even if they aren’t it’s valuable to understand things such as which fields are required. Even decorating with attributes doesn’t tie you to the Framework. In short, a class doesn’t have to know it’s part of the Framework, regardless of using the Fluent API or attributes.

Now, let me see your library card. Hmm. Yes. Nice, but I don’t care for the new typeface. Now, did you know that each person is assigned a library card at birth? It’s one of the secrets to our little success here.  Get people started right, Socrates always said. Or was that Mr. Schultz?

Well, you can have as many library cards as you want. You might travel to Salmon Island and visit their lovely undersea facility. But you must have at least one card. This is a One to One or Many relationship. A person must have at least one card, and a card can belong to just one person.

We start modeling that relationship like so:

LearningEFSchema.cs
public class Person
{
    //Primary Key
    public int PersonId { get; set; }
    [Required]
    public string Name { get; set; }

    //Navigation
    public virtual ICollection<LibraryCard> LibraryCards { get; set; }

    public Person()
    {
        LibraryCards = new HashSet<LibraryCard>();
    }
}

public class LibraryCard
{
    //Primary Key
    public int LibraryCardId { get; set; }
    [Required]
    public int Number { get; set; }
    //Foreign Key column
    public int PersonId { get; set; }

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

LearningEFDb.cs
public class LearningEFDb: DbContext
{
    public DbSet<LibraryCard> LibraryCards { get; set; }
    public DbSet<Person> Persons { get; set; }

Notice in Person we’ve added a navigation property to the person’s cards. We use ICollection because it’s the simplest collection that allows insertion via the Add method. IEnumerable, as you should recall from your schooling, does not. We’re using interfaces to help those who enjoy writing unit tests.

In the Person constructor, I initialize the collection using HashSet. Why don’t I initialize using a Collection? Simple convenience. The Collection class is in the System.Collections.ObjectModel namespace, which we aren’t referencing, while HashSet is already available in System.Collections.Generic. A HashSet is the simplest collection that, again, allows adding. You could certainly use a List, and many people do, but there is some extra memory overhead.

We declare our navigation properties as virtual so that the Framework can implement lazy loading. I’m not lazy by nature, perhaps you are. Yet I think we can all agree there are some times it’s good to be lazy.
In the LibraryCard class, I include a navigation property back to the Person.

Now, there are some who say I’m too particular about my database tables, and that may be. But I’m old and allowed to be set in my ways. The Framework, left to its own, would name the Person foreign key Person_PersonId, which I find distasteful and difficult to type. “But, Professor, why do you care?” they ask. “You won’t be using the database directly, anyway.” I assure you, each person who says this comes to me a year later saying, “We’re constantly querying our database directly. If only we had done as you do, we would save typing.”

By explicitly defining a PersonId property, the Framework will use it as the foreign key column name. It does this because of our naming convention. If we had named the virtual person property Learners and wanted a foreign key named StudentId, then we would use an attribute to tell the Framework about the relationship, like this:

//Foreign Key column
public int StudentId { get; set; }

//Navigation
[ForeignKey("StudentId")]
public virtual Person Learners { get; set; }

If your mother were here, she would--rather stridently, I’m afraid, in her youth--say, “Professor, this is not a One to One or Many. This is a One to Zero or Many.” And she would be right. We are not, at this point, requiring a person to have a library card.

What’s that you say? How are we doing the reverse and requiring a card to have a person? My dear boy, it’s because we’ve declared PersonId as a non-nullable int. May I continue? To require a person to have a card, we need to introduce some validation.

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 Person()
        {
            LibraryCards = new HashSet<LibraryCard>();
        }

        public IEnumerable Validate(ValidationContext validationContext)
        {
            if (LibraryCards.Count() < 1)
            {
                yield return new ValidationResult("A Person must have at least one Library Card");
            }
        }

Do you see? When the Framework attempts to SaveChanges, it calls Validate if a class inherits from IValidatableObject. We can put all sorts of interesting validations in our class. You should note something however. The geniuses--you might detect some sarcasm--who managed this part of the Framework decided that if a class failed its property attribute validations, such as [Required], it would not bother running the Validate method. Only if the properties are valid will it run Validate. I don’t know why. Wouldn’t you rather know all of your mistakes up front? Still, it remains very useful. If we run the program with no changes, we get an error.

Program.cs
Create Person without a name.
Saving changes.
  ERROR: The Name field is required.
Add the required name and save.
Saving changes.
  ERROR: A Person must have at least one Library Card
Let’s add to our program.
edward.Name = "Edward Farley";
edward.LibraryCards.Add(new LibraryCard() { Number = 123 });
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 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
Finished

The resulting LibraryCards table script is like this.

T-SQL
create table LibraryCards (
  LibraryCardId int not null identity primary key,
  Number int not null,
  PersonId int not null foreign key references Persons(PersonId)
)

And that is quite enough. Look, I’ve barely taken a bite. But please, tell your mother it was delicious, which I’m sure it will be!




ICollection vs List
POCO
Charles M Schultz (To my knowledge neither Mr. Schultz nor Socrates is quoted saying“get people started right.”)


Edward Farley and the Fantastic Library Part 3

Source Code

Entity Framework 5 Code First Relationships

A Simple Table

Professor Relvar lays the framework for learning the Framework.

First, Edward [said the Professor], we’re going to write some code that will make it easier to learn about the Framework. And we’ll always strive to be a simple as possible--but not simpler.* You’re familiar with Visual Studio, I assume? Good. We’ll create a Console Application and name it something whimsical. How about “GnomeMobile”? No? Well, then, we’ll name it “LearningEF.” Next, we’ll add the Entity Framework 5 NuGet package. We’ll use SQL Express, so that we can easily run queries using Enterprise Manager. Are you following? Good. Normally, I like a file per class, but today I feel abnormal, so we’ll put the entire schema in one file. Here is the class for our first table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
}

And now, in another file, code to create the database itself. Notice the using statements.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace LearningEF
{
public class LearningEFDb: DbContext
{
public DbSet Persons { get; set; }
}
}

[“But Professor,” protested Edward, “Shouldn’t that be ‘People’?”]

I am illustrating a point, young man. Youth and impatience go together like peanut butter and bananas, I suppose. Before answering your question, in our Program class, we write code to instantiate and return items from our database. Using ToList() forces eager loading of the data, which in turn forces the Framework to create the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
class Program
{
static LearningEFDb _db = new LearningEFDb();

static void Main(string[] args)
{
var list = _db.Persons.ToList();
}
}
}

Let’s run our program and see what’s created.















Ah. So. We get a database with the rather excessive name LearningEF.LearningEFDb, with a table named People. Apparently the Framework thinks it knows more about grammar than I**. This is a byproduct of convention over configuration. We, um, borrowed this idea from the DHH Library, and heaven knows where they got it from. Regardless, the Framework doesn’t care how I named my DbSet. I could have named it Kumquats and the database table would be named People. But I don’t like People. So, I’ll inform the Framework of my preference. I’ll also enforce my own database name, which means a trip to the app.config file. Enough talk. Here, look:

app.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="LearningEFDb"
connectionString="Server=.\SQLEXPRESS;Database=EFDb;Trusted_Connection=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>


LearningEFDb.cs
public class LearningEFDb: DbContext
{
public DbSet<Person> Persons { get; set; }

public LearningEFDb(): base("LearningEFDb")
{

}

LearningEFSchema.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace LearningEF
{
[Table("Persons")]
public class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
}

Now, we drop the database and rerun the program.















That’s better, isn’t it? The database has a rational name, and the table has a name whose rationality cannot be denied. We’ll do one more thing. Well, no, we’ll do many more things, of course. What I mean to say is, I don’t want to manually drop the database each time, and I already know I’ll need help saving and displaying information, so, in the interests of time....

[The Professor’s hands fluttered over the keyboard, and in a minute the following code appeared.]

LearningEFDb.cs
public class LearningEFDb: DbContext
{
public DbSet<Person> Persons { get; set; }

public LearningEFDb(): base("LearningEFDb")
{
KillConnections();
Database.SetInitializer(new DatabaseInitializer());
//run with force:true so recreated immediately instead of upon first query.
Database.Initialize(force: true);
}

public void KillConnections()
{
string kill = @"declare @kill varchar(8000) = '';
select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
from master..sysprocesses
where dbid=db_id('" + Database.Connection.Database + @"');
exec (@kill);";
string connectionString = Database.Connection.ConnectionString;
using (var cn = Database
.DefaultConnectionFactory
.CreateConnection(connectionString))
{
cn.Open();
cn.ChangeDatabase("master");
var cmd = cn.CreateCommand();
cmd.CommandText = kill;
cmd.ExecuteNonQuery();
cn.Close();
}
}

public class DatabaseInitializer : DropCreateDatabaseAlways
{

}

LearningEFSchema.cs
[Table("Persons")]
public class Person
{
//Primary Key
public int PersonId { get; set; }
[Required]
public string Name { get; set; }
}

Program.cs
static LearningEFDb _db = new LearningEFDb();

static void Main(string[] args)
{
Console.WriteLine("Create Person without a name.");
Person edward = new Person();
_db.Persons.Add(edward);
SaveChanges();
Console.WriteLine("Add the required name and save.");
edward.Name = "Edward Farley";
SaveChanges();
Console.WriteLine(“Get the record we just saved, and display.”);
edward = _db.Persons.First();
Console.WriteLine("Person: " + edward.Name);

Console.WriteLine("Finished");
Console.ReadLine();
}

static void SaveChanges()
{
Console.WriteLine("Saving changes.");
try { _db.SaveChanges(); Console.WriteLine("Successful save."); }
catch (Exception ex)
{
string exceptionError = ex.GetBaseException().Message;
if (exceptionError.ToLower().Contains("Validation failed for one or more entities.".ToLower()))
{
exceptionError = "";
}
WriteValidationErrors(exceptionError);
}
}

static void WriteValidationErrors(string exceptionError)
{
if (!String.IsNullOrWhiteSpace(exceptionError))
{
Console.WriteLine(" ERROR: " + exceptionError);
}
var errors = _db.GetValidationErrors();
foreach (var entry in errors)
{
foreach (var error in entry.ValidationErrors)
{
Console.WriteLine(" ERROR: " + error.ErrorMessage);
}
}
}

Young man, you are allowed to say “Wow” on occasion, though there’s nothing extraordinary here. I’ve simply provided a way to drop and recreate the database each time we run the program. In addition, I have a method to save Framework changes and display validation errors. For good measure, I made it required that a person has a name--everyone does here, you know. I’ve updated the program to create a person and show off our work. When we run the program, the Persons table is slightly different. Names cannot be null. Well, I suppose someone’s name could, technically, be “Null,” but I’ve never heard of it. I think I’ll dispense with the pictures, and provide equivalent SQL scripts.

T-SQL
create table Persons (
PersonId int not null identity primary key,
Name nvarchar(max) not null
)

Here is the program output.

Create Person without a name.
Saving changes.
ERROR: The Name field is required.
Add the required name and save.
Saving changes.
Successful save.
Get the record we just saved, and display.
Person: Edward Farley
Finished

Well, I think that’s quite enough for one day, don’t you? Come back tomorrow. Oh, and one last thing! Ask your dear mother if she’d make me one of her famous peanut butter and banana sandwiches. Off you go!




*Quote attributed to Albert Einstein: ”Everything should be made as simple as possible, but not simpler.”
**The Professor seems to be joking, or he likes old usages of words. Or, it’s possible--but unlikely--he expects to know the number of people in the Persons table. Regardless, EF’s pluralization to “People” is standard. People vs. Persons
Convention over configuration
Kumquat
The Gnome-Mobile
DHH