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

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

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