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.

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.

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

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,

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.

About these ads