Developer.Blog();

In the first post of this series, I showed you the basics of using Entity Framework Code First with a single table. In this post, I’ll talk about dealing with one-to-many relationships. We’ll use the code from Part 1 as our starting point, but will create and populate the tables again.

The place to start is with the entities and the context.

namespace EFLab
{
    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string State { get; set; }
        public virtual List<Email> Emails { get; set; }
    }

    public class Email
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        public string EmailAddress { get; set; }
    }

    public class EFContext : DbContext
    {
        public DbSet<Person> People { get; set; }
        public DbSet<Email> Emails { get; set; }
    }
}

I’ve added a class called Email, that is used to store the email address for a person, or in this case, zero to n email addresses. Also, look at the new Emails property of the Person class. It’s a virtual property. By making it virtual, it will be lazy loaded, so the data won’t actually be read until it’s needed.

Now we need some code to populate the data. Here’s the first attempt:

static void Main(string[] args)
{
        EFContext context = new EFContext();
        Person person;
        Email email;

        person = new Person();
        person.FirstName = "Bullwinkle";
        person.LastName = "Moose";
        person.State = "MN";
        email = new Email { EmailAddress = bullwinkle@frostbitefalls.com};
        person.Emails.Add(email);
        context.People.Add(person);
        context.SaveChanges();
   }

If you guess this would not work, you’re correct. It throws an exception on the line person.Emails.Add(email).

This is because List<Email> has not been instantiated. It’s currently null. Let’s fix it and add some additional data.

static void Main(string[] args)
{
        EFContext context = new EFContext();
        Person person;
        Email email;

        person = new Person();
        person.FirstName = "Bullwinkle";
        person.LastName = "Moose";
        person.State = "MN";
        person.Emails = new List<Email>();
        email = new Email { EmailAddress = "bullwinkle@frostbitefalls.com" };
        person.Emails.Add(email);
        context.People.Add(person);
        context.SaveChanges();

        person = new Person();
        person.FirstName = "Rocky";
        person.LastName = "Squirrel";
        person.State = "MN";
        person.Emails = new List<Email>();
        email = new Email { EmailAddress = "rocky@frostbitefalls.com" };
        person.Emails.Add(email);
        email = new Email { EmailAddress = "rocky@squirrel.com" };
        person.Emails.Add(email);
        context.People.Add(person);
        context.SaveChanges();

        person = new Person();
        person.FirstName = "Peter";
        person.LastName = "Peachfuzz";
        person.State = "NY";
        context.People.Add(person);
        context.SaveChanges();
}

When we look at the data for the People table, we see the correct values were added

But,what about the Emails table?

Whoa! Magic! Entity Framework correctly populated the PersonId value for us. This is because EF inferred the parent ID and populated the column.

But what about reading data back? Well, it’s not as easy as you may think. Let’s try it and see what we get.

static void Main(string[] args)
{
    EFContext context = new EFContext();
    var people = context.People;

    foreach (var person in people)
    {
        Console.WriteLine(person.FirstName);
        Console.WriteLine(person.LastName);
        foreach (var email in person.Emails)
        {
            Console.WriteLine(email.EmailAddress);
        }
        Console.WriteLine("==========");
    }
    Console.ReadLine();
}

If you try to run this, you’ll get a SystemData.EntityCommandExecutionException. Specifically, the error is “There is already an open DataReader associated with this Command which must be closed first.”. What’s happening, is you can’t read from both tables using the same EntityCommandDefinition.

There are a couple of ways to fix this. The first, is to add MultipleActiveResultSets=true to the connection string in app.config. This will allow you run the above code and get the email addresses without throwing the error.

The second way is to convert the query into a List before looping through it. Change the line that reads to data to var people = context.People.ToList(). This causes the data to be read in one go instead of two.

When it comes to delete, you’ll have to handle each table yourself. If you delete just the row from the People table, the related Emails will be orphaned.

In the next post in this series, we’ll look at many-to-many relationships.