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

Source Code

Entity Framework 5 Code First Relationships

A Simple Table

Professor Relvar lays the framework for learning the Framework.

First, Edward [said the Professor], we’re going to write some code that will make it easier to learn about the Framework. And we’ll always strive to be a simple as possible--but not simpler.* You’re familiar with Visual Studio, I assume? Good. We’ll create a Console Application and name it something whimsical. How about “GnomeMobile”? No? Well, then, we’ll name it “LearningEF.” Next, we’ll add the Entity Framework 5 NuGet package. We’ll use SQL Express, so that we can easily run queries using Enterprise Manager. Are you following? Good. Normally, I like a file per class, but today I feel abnormal, so we’ll put the entire schema in one file. Here is the class for our first table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
    }
}

And now, in another file, code to create the database itself. Notice the using statements.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace LearningEF
{
    public class LearningEFDb: DbContext
    {
        public DbSet Persons { get; set; }
    }
}

[“But Professor,” protested Edward, “Shouldn’t that be ‘People’?”]

I am illustrating a point, young man. Youth and impatience go together like peanut butter and bananas, I suppose. Before answering your question, in our Program class, we write code to instantiate and return items from our database. Using ToList() forces eager loading of the data, which in turn forces the Framework to create the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearningEF
{
    class Program
    {
        static LearningEFDb _db = new LearningEFDb();

        static void Main(string[] args)
        {
            var list = _db.Persons.ToList();
        }
    }
}

Let’s run our program and see what’s created.

Ah. So. We get a database with the rather excessive name LearningEF.LearningEFDb, with a table named People. Apparently the Framework thinks it knows more about grammar than I**. This is a byproduct of convention over configuration. We, um, borrowed this idea from the DHH Library, and heaven knows where they got it from. Regardless, the Framework doesn’t care how I named my DbSet. I could have named it Kumquats and the database table would be named People. But I don’t like People. So, I’ll inform the Framework of my preference. I’ll also enforce my own database name, which means a trip to the app.config file. Enough talk. Here, look:

app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="LearningEFDb" 
         connectionString="Server=.\SQLEXPRESS;Database=EFDb;Trusted_Connection=True"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Person> Persons { get; set; }

    public LearningEFDb(): base("LearningEFDb")
    {

    }

LearningEFSchema.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace LearningEF
{
    [Table("Persons")]
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
    }
}

Now, we drop the database and rerun the program.

That’s better, isn’t it? The database has a rational name, and the table has a name whose rationality cannot be denied. We’ll do one more thing. Well, no, we’ll do many more things, of course. What I mean to say is, I don’t want to manually drop the database each time, and I already know I’ll need help saving and displaying information, so, in the interests of time....

[The Professor’s hands fluttered over the keyboard, and in a minute the following code appeared.]

LearningEFDb.cs

public class LearningEFDb: DbContext
{
    public DbSet<Person> Persons { get; set; }

    public LearningEFDb(): base("LearningEFDb")
    {
        KillConnections();
        Database.SetInitializer(new DatabaseInitializer());
        //run with force:true so recreated immediately instead of upon first query.
        Database.Initialize(force: true); 
    }

    public void KillConnections()
    {
        string kill = @"declare @kill varchar(8000) = '';
        select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
        from master..sysprocesses 
        where dbid=db_id('" + Database.Connection.Database + @"');
        exec (@kill);";
        string connectionString = Database.Connection.ConnectionString;
        using (var cn = Database
            .DefaultConnectionFactory
            .CreateConnection(connectionString))
        {
            cn.Open();
            cn.ChangeDatabase("master");
            var cmd = cn.CreateCommand();
            cmd.CommandText = kill;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }

    public class DatabaseInitializer : DropCreateDatabaseAlways
    {

    }

LearningEFSchema.cs

[Table("Persons")]
public class Person
{
    //Primary Key
    public int PersonId { get; set; }
    [Required]
    public string Name { get; set; }
}

Program.cs

static LearningEFDb _db = new LearningEFDb();

static void Main(string[] args)
{
    Console.WriteLine("Create Person without a name.");
    Person edward = new Person();
    _db.Persons.Add(edward);
    SaveChanges();
    Console.WriteLine("Add the required name and save.");
    edward.Name = "Edward Farley";
    SaveChanges();
    Console.WriteLine(“Get the record we just saved, and display.”);
    edward = _db.Persons.First();
    Console.WriteLine("Person: " + edward.Name);
    
    Console.WriteLine("Finished");
    Console.ReadLine();
}

static void SaveChanges()
{
    Console.WriteLine("Saving changes.");
    try { _db.SaveChanges(); Console.WriteLine("Successful save."); }
    catch (Exception ex)
    {
        string exceptionError = ex.GetBaseException().Message;
        if (exceptionError.ToLower().Contains("Validation failed for one or more entities.".ToLower()))
        {
            exceptionError = "";
        }
        WriteValidationErrors(exceptionError);
    }
}

static void WriteValidationErrors(string exceptionError)
{
    if (!String.IsNullOrWhiteSpace(exceptionError))
    {
        Console.WriteLine("  ERROR: " + exceptionError);
    }
    var errors = _db.GetValidationErrors();
    foreach (var entry in errors)
    {
        foreach (var error in entry.ValidationErrors)
        {
            Console.WriteLine("  ERROR: " + error.ErrorMessage);
        }
    }
}

Young man, you are allowed to say “Wow” on occasion, though there’s nothing extraordinary here. I’ve simply provided a way to drop and recreate the database each time we run the program. In addition, I have a method to save Framework changes and display validation errors. For good measure, I made it required that a person has a name--everyone does here, you know. I’ve updated the program to create a person and show off our work. When we run the program, the Persons table is slightly different. Names cannot be null. Well, I suppose someone’s name could, technically, be “Null,” but I’ve never heard of it. I think I’ll dispense with the pictures, and provide equivalent SQL scripts.

T-SQL

create table Persons (
  PersonId int not null identity primary key,
  Name nvarchar(max) not null
)

Here is the program output.

Create Person without a name.
Saving changes.
  ERROR: The Name field is required.
Add the required name and save.
Saving changes.
Successful save.
Get the record we just saved, and display.
Person: Edward Farley
Finished

Well, I think that’s quite enough for one day, don’t you? Come back tomorrow. Oh, and one last thing! Ask your dear mother if she’d make me one of her famous peanut butter and banana sandwiches. Off you go!

*Quote attributed to Albert Einstein: ”Everything should be made as simple as possible, but not simpler.”
**The Professor seems to be joking, or he likes old usages of words. Or, it’s possible--but unlikely--he expects to know the number of people in the Persons table. Regardless, EF’s pluralization to “People” is standard. People vs. Persons
Convention over configuration
Kumquat
The Gnome-Mobile
DHH

Source Code

Entity Framework 5 Code First Relationships

Introduction

Where Edward Farley begins his quest.

When Edward Farley turned twelve years old, he announced to his family, “I’m going to the library and find out how everything works.”

You might imagine that his parents would be surprised, or puzzled. But Edward had always been like this, wondering the how and why of things. In fact, his mother and father expected a day like this.

“Well, Edward, if you really want to know, then there’s only one person to ask,” said his father.

“Who?”

“Professor Relvar.”

Edward nodded. “Which reading room is he in?”

Edward’s mother smiled and glanced at her husband. “He’s not in any reading room.”

Edward’s eyes got big. “Then where will I find him?”

“In Archives,” she said. “I know, Edward. Children your age aren’t allowed in Archives. But we’re old friends of the professor, and he’ll see you.”

“Really!? Wow! When can I go?”

“Whenever you’re ready,” said his father. “Right now, if you like.”

Edward nodded his head so fast it looked like it might fly off. His mother got his coat and said, while helping him on with it, “One thing, Edward. The professor is...well, a little eccentric.”

“That’s one word for it,” mumbled his father.

“So,” continued his mother, “it’s best if you listen more than talk.”

“OK.”

“Well then, off you go.”

Edward ran out the door, leaped onto his bicycle, and pedaled like a dervish to the library. He arrived, walked quickly to the information desk, and said, “My name’s Edward Farley. I’m supposed to see Professor Relvar. In Archives.” He emphasized the last part.

The man behind the desk glanced at a computer screen, then at Edward, then back at the screen.

“The green elevator, bottom floor, go straight, left, left, straight to number 301, knock twice--only twice--and wait.”

“OK.” Edward hurried off, repeating the instructions to himself. When he found the door, he knocked twice and waited. And waited. And waited some more. After ten minutes, he raised his hand to knock again when the door flew open and a short, pudgy man with white hair and huge reading glasses glared at him.

“You weren’t about to knock again, were you?”

Edward froze, swallowing.

“Never mind, never mind. They always do. Come in, come in, come in. Find yourself a seat. No! Not that one. Find one over here. Yes, that’s right. That one.”

The chair indicated was the only one in the room, which otherwise was a pandemonium of books, drawings, magazines and artwork. Edward wasn’t sure what other chair the professor thought he was headed for. Nor did he know where the professor would sit. The professor went behind his desk, and suddenly the floor opened and a chair shot up, stopping just as the professor sat.

“Well?” Professor Relvar asked, peering through his glasses.

Edward was dumbstruck. The Professor kept staring.

“I...want to know how it all, um, works?”

The Professor sat back. “Ah. All of it?”

“Well...yeah. I mean, no! Not all...you know, just...whatever you want to tell me.”

“Your parents,” said Professor Relvar, “were above average students. Not my best. But they remembered the little things.” He gazed at the ceiling for a few seconds, then shook himself. “Well, knowing how it all works is out of the question. No one knows that. At least, I don’t, and I’m the most knowledgable no one here. But, what I can tell you is how things relate, how we store data about things.”

“The Database?” asked Edward.

“Just so. The Database keeps it straight, and we talk to the database using the Framework. It’s all rather simple, but most people are too busy reading their books to learn much about it.”

The Professor reached out and a computer keyboard slid into position from somewhere. He tapped the monitor, which lifted up into the ceiling. The room darkened, and the opposite wall lit up. Where there’d been massive bookcases was now the biggest computer screen Edward had ever seen.

“Before you say ‘Wow,’ you should know, Dr. Nelson has a bigger one at the Pick Library.” The Professor leaned toward Edward, conspiratorially, and whispered, “We’re rivals, but I secretly admire his work.

“So, ready?”

Edward nodded, and Professor Relvar began....

Relvar
Pick Database Operating System