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