Code-First Entity Framework Multiple Collections with Many to Many

Question

I have another Entity Framework question here. I have a complicated object called Book and that object has a number of collections of type Contributor such as Writer, Letterer, Colorist, etc. Contributors are not necessarily scoped to a particular role though. So the same contributor (with the same ContributorId) could be both a Writer and a Colorist, for example.

public Book {
        public ICollection<Contributor> Writers { get; set; }
        public ICollection<Contributor> Artists { get; set; }
        public ICollection<Contributor> Pencilers { get; set; }
        public ICollection<Contributor> Inkers { get; set; }
        public ICollection<Contributor> Colorists { get; set; }
        public ICollection<Contributor> Letterers { get; set; }
        public ICollection<Contributor> CoverArtists { get; set; }
        public ICollection<Contributor> OtherContributors { get; set; }
}

public Contributor {
     public int ContributorId { get; set; }
     public string Name { get; set; }
}

I am having trouble, viewing the examples I have found here and on other sites, determining how I would signify the appropriate model. I would expect a Db Model something like this. What I want to avoid is a model wherein I have a separate table for every Contributor Role, or a separate row in the Contributor table for every instance in which a contributor is associated with a book in any role.

+ Books 
     --BookId
+ Contributors
      --ContributorId
+ BookContributors
      --BookId
      --ContributorId
      --Discriminator

I am such as ADO.NET guy that I am not really finding this too enjoyable, but I am determined to become at least borderline proficient in this important framework.

A Quick Note: Since opening this question, I got pulled away at work and haven't had the time to thoroughly review the answers and play around with the results. But I didn't want to leave the bounty hanging as I appreciate the answers everyone has provided. So I selected the answer that appeared of the most interest to me starting out. I want to thank everyone though for this.

Answer 1

I have worked on a solution that implements the model you proposed although it works a bit different than what you would expect. Hope this answers your question.

Models

[Table("Book")]
public class Book
{
    [Column("BookId")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int BookId { get; set; }

    [NotMapped]
    public ICollection<Contributor> Writers { get; set; }

    [NotMapped]
    public ICollection<Contributor> Artists { get; set; }

    [NotMapped]
    public ICollection<Contributor> Pencilers { get; set; }

    [NotMapped]
    public ICollection<Contributor> Inkers { get; set; }

    [NotMapped]
    public ICollection<Contributor> Colorists { get; set; }

    [NotMapped]
    public ICollection<Contributor> Letterers { get; set; }

    [NotMapped]
    public ICollection<Contributor> CoverArtists { get; set; }

    [NotMapped]
    public ICollection<Contributor> OtherContributors { get; set; }
}

[Table("Contributor")]
public class Contributor
{
    [Column("ContributorId")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ContributorId { get; set; }
}

// Contributor Type is one of the following options: Writer, Artist, Penciler, etc.
[Table("ContributorType")]
public class ContributorType
{
    [Column("ContributorTypeId")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ContributorTypeId { get; set; }

    [Column("Name")]
    public string Name { get; set; }
}

[Table("BookContributor")]
public class BookContributor
{
    [Column("BookContributorId")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int BookContributorId { get; set; }

    [Column("BookId")]
    public int BookId { get; set; }

    [Column("ContributorId")]
    public int ContributorId { get; set; }

    [Column("RoleId")]
    public int RoleId { get; set; }

    [ForeignKey("BookId")]
    public virtual Book Book { get; set; }

    [ForeignKey("ContributorId")]
    public virtual Contributor Contributor { get; set; }

    [ForeignKey("RoleId")]
    public virtual ContributorType Role { get; set; }
}

Database Context

AppDbContext.cs:

public class AppDbContext : DbContext
{
    public AppDbContext()
    {
        Database.SetInitializer<AppDbContext>(new AppDbInitializer());
    }

    public AppDbContext(string connectionString)
        : base(connectionString)
    {
        Database.SetInitializer<AppDbContext>(new AppDbInitializer());
    }

    public DbSet<Book> Books { get; set; }

    public DbSet<Contributor> Contributors { get; set; }

    public DbSet<ContributorType> ContributorTypes { get; set; }

    public DbSet<BookContributor> BookContributors { get; set; }
}

AppDbInitializer.cs:

public class AppDbInitializer : DropCreateDatabaseAlways<AppDbContext>
{
    protected override void Seed(AppDbContext context)
    {
        // default contributor types
        var contributorTypes = new List<ContributorType>();
        contributorTypes.Add(new ContributorType() { Name = "Writer" });
        contributorTypes.Add(new ContributorType() { Name = "Artist" });
        contributorTypes.Add(new ContributorType() { Name = "Penciler" });
        contributorTypes.Add(new ContributorType() { Name = "Inker" });
        contributorTypes.Add(new ContributorType() { Name = "Colorist" });
        contributorTypes.Add(new ContributorType() { Name = "Letterer" });
        contributorTypes.Add(new ContributorType() { Name = "CoverArtist" });
        contributorTypes.Add(new ContributorType() { Name = "OtherContributor" });

        // adding it to the context
        foreach (var type in contributorTypes)
            context.ContributorTypes.Add(type);

        base.Seed(context);
    }
}

Wrapping everything together

Program.cs:

class Program
{
    static void Main(string[] args)
    {
        // enter name of the connection string in App.Config file
        var connectionSettings = ConfigurationManager.ConnectionStrings["..."];
        using (var dbContext = new AppDbContext(connectionSettings.ConnectionString)) 
        {
            // Creating a book
            var book = new Book();
            dbContext.Books.Add(book);
            dbContext.SaveChanges();

            // Creating contributor
            var contributor = new Contributor();
            dbContext.Contributors.Add(contributor);
            dbContext.SaveChanges();

            // Adding contributor to the book
            var bookContributor = new BookContributor()
            {
                BookId = book.BookId,
                ContributorId = contributor.ContributorId,
                RoleId = dbContext.ContributorTypes.First(t => t.Name == "Writer").ContributorTypeId
            };
            dbContext.BookContributors.Add(bookContributor);
            dbContext.SaveChanges();

            // retrieving a book
            var book = dbContext.Books.Where(b => b.BookId == 2).FirstOrDefault();
            if (book != null) 
            {
                book.Writers = 
                    from contributor in dbContext.Contributors
                    join bookContributor in dbContext.BookContributors on contributor.BookId equals bookContributor.BookId
                    join contributorType in dbContext.ContributorTypes on contributorType.ContributorTypeId equals bookContributor.ContributorTypeId
                    where 
                        bookContributor.BookId == 2 and
                        contributorType.Name == "Writer"
                    select contributor;

                // do the same for other types of contributors
            }
        }
    }
}