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