Source Code

Entity Framework 5 Code First Relationships

Many to Many With Payload and One to Many

Edward learns what his mother knows.

Edward Farley put his new hover tablet down in disgust. "Mom, I can't get it to work!"

His mother came in from her office/studio. "Which 'it' would that be, dear?"

"Umbrella covers!"

"Ah," said his mother. "This is the puzzle Professor Relvar sent you, after he received your thank you card for the tablet?"

"Yes," mumbled Edward, miserably. "I thought I could figure it out. I guess I'll have to ask him."

"Edward, I realize I'm not at brilliant as Professor Relvar, but I do know a thing or two about databases and the Framework. Would you like my help?"

"I guess so."

She smiled and sat next to him. "I think the Professor would say something like, 'Now, young man, what seems to be your trouble? I assure you it isn't my trouble, though I may trouble myself to help you.'"

Edward stared at his mother. "That sounded just like him."

"Well, then," she continued, "explain the problem."

"I have umbrellas. An umbrella could be used by many people, and a person could use many umbrellas."

"A Many to Many relationship," said his mother.

"Right. Except an umbrella for a person becomes a color. So, it's a Many to Many with Payload. Now I want to show that an umbrella-person combination can have many umbrella covers. The Professor said I could use the join table's UmbrellaId and PersonId columns as the key, but he said there was another way and I don't remember."

"May I use your tablet?" his mother asked. Edward handed it to her. She pressed several buttons and the tablet projected its screen on a far wall.

What you are trying to do, Edward, [his mother continued], is keep your table primary keys simple. It's nice to not use composite keys unless you have to, because it's easier to relate tables using a single column. Instead of starting at the beginning, let's start at the end. Here's how you want the tables to look.

It's easy to add a new primary key to the class. And if we remove the attributes that tell PersonId and UmbrellaId that they're part of the key, the Framework will still see them as foreign keys because they're named according to convention. Otherwise, you'd have to use attributes on the navigation properties to indicate the foreign key property.

Let's also add the related PersonUmbrellaCovers class. Now, you understand, dear, that this isn't a practical thing to do? Having an umbrella cover for each unique person-umbrella? But, the Professor does love his little puzzles. Here is how our classes will look.

LearningEFSchema.cs

public class PersonUmbrella
{
    //Primary Key
    public int PersonUmbrellaId { get; set; }
    //Unique Key (aka candidate key, could have been primary key)
    //must be handled in DbContext.ValidateEntity.
    //Foreign Keys
    public int PersonId { get; set; }
    public int UmbrellaId { get; set; }

    [Required]
    public UmbrellaColors Color { get; set; }

    //Navigation
    public virtual Person Person { get; set; }
    public virtual Umbrella Umbrella { get; set; }
    public virtual ICollection<PersonUmbrellaCover> PersonUmbrellaCovers { get; set; }

    public PersonUmbrella()
    {
        PersonUmbrellaCovers = new HashSet<PersonUmbrellaCover>();
    }
}

public class PersonUmbrellaCover
{
    //Primary Key
    public int PersonUmbrellaCoverId { get; set; }
    //Foreign Key
    public int PersonUmbrellaId { get; set; }
    
    [Required]
    public string Description { get; set; }

    //Navigation
    public virtual PersonUmbrella PersonUmbrella { get; set; }

}

Let's not update the program's output yet, and just run it so we see how the database turns out. [She did so, and they viewed the tables] See? We have the tables we wanted. Now, dear, are we finished?

[Edward thought hard, then shook his head. "Couldn't we have duplicate PersonUmbrellas?"]

Exactly! I'll have to tell you father you really were paying attention to the Professor, and not just eating ice cream. Our problem is that by no longer setting the PersonId and UmbrellaId columns as part of a key, they aren't guaranteed to be unique. We need to do something similar to your Magnifier serial number problem. In your DbContext class, we'll add a uniqueness test, and a validation.

LearningEFDb.cs

public bool IsUniquePersonUmbrella(PersonUmbrella personUmbrella)
{
    var entity = this.PersonUmbrellas.SingleOrDefault(a => 
        a.PersonUmbrellaId != personUmbrella.PersonUmbrellaId 
        & a.PersonId == personUmbrella.PersonId 
        & a.UmbrellaId == personUmbrella.UmbrellaId);
    return (entity == null);
}


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<object, object>(Guid.NewGuid(),
                new ValidationResult(
                    "Magnifier with this serial nbr already exists.",
                    new List<string>() { "SerialNbr" }
                    )
                )
            );
        }
    }
    if (entityEntry.Entity is PersonUmbrella)
    {
        var entity = (PersonUmbrella)entityEntry.Entity;
        if (!IsUniquePersonUmbrella(entity))
        {
            items.Add(new KeyValuePair<object, object>(Guid.NewGuid(),
                new ValidationResult(
                    "Person-Umbrella combination already exists.",
                    new List<string>() { "PersonId", "UmbrellaId" }
                    )
                )
            );
        }
    }
    return base.ValidateEntity(entityEntry, items);
}

We also need to make PersonUmbrella validatable.
**
** LearningEFSchema.cs

public class PersonUmbrella: IValidatableObject
    {
        //Primary Key
        public int PersonUmbrellaId { get; set; }
        //Unique Key (aka candidate key, could have been primary key)
        //must be handled in DbContext.ValidateEntity.
        //Foreign Keys
        public int PersonId { get; set; }
        public int UmbrellaId { get; set; }

        [Required]
        public UmbrellaColors Color { get; set; }

        //Navigation
        public virtual Person Person { get; set; }
        public virtual Umbrella Umbrella { get; set; }
        public virtual ICollection<PersonUmbrellaCover> PersonUmbrellaCovers { get; set; }

        public PersonUmbrella()
        {
            PersonUmbrellaCovers = new HashSet<PersonUmbrellaCover>();
        }

        IEnumerable<ValidationResult> 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;
                    }
                }
            }
        }
    }

Hmm. If I were writing this, I would create an extension method for ValidationContext that returns the enumeration. That way I could reduce the amount of code. But, we'll leave that for after dinner. Now, Edward, update the program while I fix us each a Kiwi-ade. [Edward did so, clarifying why two errors would be thrown. His mother returned.] Ah! Good, dear, you put the new code near the other Umbrella code, instead of at the end. That shows you're thinking about context.

Program.cs

Console.WriteLine("Prove that there can't be two identical PersonUmbrella combinations");
jane.PersonUmbrellas.Add(new PersonUmbrella() { Umbrella = umbrella, Color = UmbrellaColors.Blue });
Console.WriteLine("Jane's umbrellas: " + jane.PersonUmbrellas.Count());
Console.WriteLine("Two error lines show, because we store the error for two property names");
SaveChanges();

Console.WriteLine("Remove the bad combination and continue.");
_db.PersonUmbrellas.Remove(jane.PersonUmbrellas.Skip(1).Take(1).Single());
SaveChanges();

Console.WriteLine("Add covers to Jane's Umbrellas");
jane.PersonUmbrellas.First().PersonUmbrellaCovers.Add(new PersonUmbrellaCover() { Description = "Plaid" });
jane.PersonUmbrellas.First().PersonUmbrellaCovers.Add(new PersonUmbrellaCover() { Description = "Solid" });
Console.WriteLine("Save them, reget Jane from the database, and display.");
SaveChanges();
jane = _db.Persons.Single(a => a.Name == "Jane Eager");
foreach (var cover in jane.PersonUmbrellas.First().PersonUmbrellaCovers)
{
    Console.WriteLine("Jane's umbrella has a cover that is " + cover.Description + ".");
}

...

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.PropertyName + " - " + error.ErrorMessage);
        }
    }
}

[With his tongue between his teeth, Edward ran the program.]

Create Person without a name.
Saving changes.
  ERROR: Name - The Name field is required.
  ERROR: ReadingRoom - 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
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: SerialNbr - Magnifier with this serial nbr already exists.
Add/save Pencil setting Nickname to null.
Saving changes.
  ERROR: Nickname - 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
Attempt adding 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
Add two books and save
Saving changes.
Successful save.
Add another person and save
Saving changes.
Successful save.
Each person has read both books
Saving changes.
Successful save.
Edward Farley's books: The House of Arden, Harding's Luck
Jane Eager's books: The House of Arden, Harding's Luck
Create an umbrella for Edward
Jane uses it, too.
Saving changes.
Successful save.
PersonUmbrellas: 2
Edward's Umbrella: Red
Jane's Umbrella: Green
Prove that there can't be two identical PersonUmbrella combinations
Jane's umbrellas: 2
Two error lines show, because we store the error for two property names
Saving changes.
  ERROR: PersonId - Person-Umbrella combination already exists.
  ERROR: UmbrellaId - Person-Umbrella combination already exists.
Remove the bad combination and continue.
Saving changes.
Successful save.
Add covers to Jane's Umbrellas
Save them, reget Jane from the database, and display.
Saving changes.
Successful save.
Jane's umbrella has a cover that is Plaid.
Jane's umbrella has a cover that is Solid.
Build a chair and a sofa
Edward puts them in his reading room.
Saving changes.
Successful save.
Edward Farley has a chair made by Vincent's Chairs. It is not a recliner.
Edward Farley has a sofa made by Gaugin's Chairs. It has 3 cushions.
Finished

And the program says we're finished! I think so, too. Time for Kiwi-ade.

Kiwi-ade (When I wrote this, I had no idea there were real kiwi-ade recipes.)