Many to Many Relationships with EF

Wednesday, June 13, 2012

A many to many relationship is easy to setup with code first EF. For example, an Author can write many books.

public class Author
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<Book> Books { get; set; }
}

And a book can have many authors.

public class Book
{
    public virtual int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual ICollection<Author> Authors { get; set; }
}

To start a library, all you need is a book set or author set (or both) in a DbContext derived class.

public class LibraryDb : DbContext
{        
    public DbSet<Book> Books { get; set; }
}

Then the framework will create a database schema with the join table you might expect.

Books and Authors Schema

If you want to have a sequence of objects representing every book and author combination, you can use the SelectMany operator to “flatten” a sequence of book authors.

var authorsBooks = db.Books
                     .SelectMany(
                        book => book.Authors,
                        (book, author) => new {
                            AuthorName = author.Name,
                            BookTitle = book.Title
                     });            

And the above query generates the following SQL:

SELECT 
  [Extent1].[Id] AS [Id], 
  [Join1].[Name] AS [Name], 
  [Extent1].[Title] AS [Title]
FROM  [Books] AS [Extent1]
  INNER JOIN 
  (
      SELECT 
        [Extent2].[Author_Id] AS [Author_Id], 
        [Extent2].[Book_Id] AS [Book_Id], 
        [Extent3].[Id] AS [Id], 
        [Extent3].[Name] AS [Name]
      FROM  [AuthorBooks] AS [Extent2]
        INNER JOIN [Authors] AS [Extent3] ON 
                   [Extent3].[Id] = [Extent2].[Author_Id]
   ) AS [Join1] ON [Extent1].[Id] = [Join1].[Book_Id]

Comments
gravatar James Culbertson Wednesday, June 13, 2012
Thanks for the post. I found it gets a little harrier if you need to add an additional property on the AuthorBooks like "Contribution", e.g. ("Principal", "Co-Author", etc). You have to create the AuthorBooks as an entity but it complicates the collections.
gravatar Carl Anderson Thursday, June 14, 2012
Hey, great post :)

I'm personally not a big fan of Code First with its dynamic queries and anonymous types which I find hard to debug or optimize in the long run.
To illustrate my comment I actually posted a sample (Many to Many relationships with CodeFluent Entities) of how to get the exact same result but using a model-first approach ;)

Cheers,
Carl
gravatar Carl Anderson Thursday, June 14, 2012
Comments are closed.

My Pluralsight Courses

K.Scott Allen OdeToCode by K. Scott Allen
What JavaScript Developers Should Know About ECMAScript 2015
The Podcast!