Many to Many Relationships with EF

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.

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]