Home > dotnet 4, Entity Framework > EF Code First: Managing relationships

EF Code First: Managing relationships

Continuing with my previous post on how to use complex types and how things are so simple, clean and yet powerful. In this post I will show how easy it is to manage relationships between entities using the EF Code first approach. I think this is one of the reasons I really like EF Code first as it makes things so simple and easy.

1) One to One relationship: Suppose we have an Employee table which has Id, first name and last name and we want to map it to Employeephoto table which contains the Employee Id as a reference and a photo path of that employee.

Employee.cs

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

 

EmployeePhoto.cs

public class EmployeePhoto
{
    public int Id { get; set; }
    public string PhotoPath { get; set; }
    public virtual Employee Employee { get; set; }
}

and to define these two entity as our database table we have to define it with the DbSet like this.

POCOContext.cs

public DbSet<Employee> Employee { get; set; }
public DbSet<EmployeePhoto> EmployeePhoto { get; set; }

 

2) One to Many relationship: Suppose we have a person table and a person can have many aliases.So this how we would map that in Code first.

Person.cs

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
    public virtual ICollection<PersonAlias> Aliases { get; set; }
}

PersonAlias.cs

public class PersonAlias
{
    public int Id { get; set; }
    public int PersonId { get; set; }
    public string Aka { get; set; }
}

So as you can see by the virtue of the fact that I declare a virtual ICollection<PersonAlias>
it mapped the PersonAlias as one to many.

 

3)Many to Many relationship: In this I am going to take the classic example of “Books and Authors” i.e. a book can be written by many authors, and an author can write many books. Lets see how we would map that in EF Code first.

Books.cs

public class Book
{
    public Book()
    {
        Authors = new HashSet<Author>();
    }
    public int BookId { get; set; }
    public string Title { get; set; }
    public virtual ICollection<Author> Authors { get; set; }
}

Author.cs

public class Author
{
    public Author()
    {
        Books = new HashSet<Book>();
    }
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}

And when you call Database.Initialize you will see the two tables getting created and another table with name BookAuthor will get created as represented in the database diagram.

Database diagram

This is very clean and much better, as many to many is always a tricky part of any database design so lets enter a few dummy data and play with it and this is how the data looks like.

Output data

As you can see that Joe Blog and John Doe have co authored Book1 and Book2, however Joe has Book3 only to his name and John has Book4. Lets see how EF handles the data specially we are saying each entity have collection of the other one.

So lets write a unit test and see what’s beneath the hood 😉

[TestMethod()]
public void Should_be_able_to_get_book_by_author_id()
{
    int id = 1;
    var query = context.Author.Include("Books")
                              .Where(x => x.AuthorId == id);
    var authorDto = query.ToList();
}

and lets put this author DTO under the watch

author dto under watch

hmm.. interesting … EF is creating some sort of recursive objects of author and books and even though we ran the query only for Joe Blogg but John Doe is also returned as John Doe has co authored with Joe. And the object hierarchy is infinite as it just a pointer to another object and no matter how many levels you keep on drilling you will always find the books and authors objects.

Lets write our query so that it makes more sense and will flatten the structure only returning the books written by Joe Blogg. And to do that we will use some LINQ magic and this is how the new test would look like.

[TestMethod()]
public void Should_be_able_to_get_book_by_author_id()
{
    int id = 1;
    var query = context.Author
                        .Where(x => x.AuthorId == id)
                        .SelectMany(a => a.Books,
                                   (au, bk) => new
                                   {
                                       au.Name,
                                       bk.BookId,
                                       bk.Title
                                   });
    var authorDto = query.ToList();
}

and now when we re run the test and put the object in the watch window we will see a flatten structure of our data and the desired result,

Desired output

I hope my previous post and this post must have convinced you that how good the EF Code First is and if you are convinced then go ahead and spread the word get your team members and your project manager involved and push for this sweet ORM. 🙂

Advertisements
  1. Shane
    March 28, 2011 at 7:53 pm

    I am trying to figure this stuff out and your sample is the closest I’ve gotton to making this work.
    My “BookAuthor” table has all nulls when trying to add data to it.

    How do those values populate?
    Can you give an example of adding the data?
    -Shane

    • Shane
      March 28, 2011 at 9:44 pm

      I figured it out.
      Was simple, of course.
      Class1 myclass = NewClass {value = value};
      context.Class2.Add(myclass)

      Thanks

      • March 29, 2011 at 7:04 am

        Shane :

        I figured it out.
        Was simple, of course.
        Class1 myclass = NewClass {value = value};
        context.Class2.Add(myclass)

        Thanks

        Yes that’s correct this is how I did it ..

        public void AddData()

        {

        var book = new Book() { Title = “Book1”};

        context.Book.Add(book);

        var author = new Author() { Name = “Joe Blogg”};

        context.Author.Add(author);

        context.SaveChanges();

        }

        public void AssociateAuthorAndBook()

        {

        var book = context.Book.Where(x => x.BookId == 1).FirstOrDefault();

        var author = context.Author.Where(x => x.AuthorId == 1).FirstOrDefault();

        author.Books.Add(book);

        context.SaveChanges();

        }

  2. Satish
    April 8, 2011 at 8:54 am

    A good read, thanks .

  3. marouprod
    April 12, 2012 at 9:56 pm

    thank’s
    but i have this database :
    commande(cmd_Id, Client, Data) cmd_id is pimary key
    produit(pro_Id,libelle) pro_id is primary key
    ligneCmd(Cmd_Id,pro_id) pro_id and cmd id are primary keys and foreign keys
    help me pleaze thank’s

    • marouprod
      April 12, 2012 at 10:20 pm

      marouprod :
      thank’s
      but i have this database :
      commande(cmd_Id, Client, Date) cmd_id is pimary key
      produit(pro_Id,libelle) pro_id is primary key
      ligneCmd(Cmd_Id,pro_id) pro_id and cmd id are primary keys and foreign keys
      help me pleaze thank’s

  1. April 6, 2011 at 8:16 pm
  2. April 11, 2011 at 9:06 pm
  3. April 20, 2011 at 9:24 pm
  4. May 1, 2011 at 6:47 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: