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 now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!