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

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