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