Code-First Entity Framework Multiple Collections with Many for Many

Question

I have one more question with Entity Framework. I have a complex object called "Book", and this object has several collections like Contributor, such as Writer, Letterer, Colorist, etc. However, participants do not necessarily have a role. Thus, the same contributor (with the same ContributorId) can be both a writer and a colorist.

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 problems looking through examples that I have found here and on other sites, determining how I will designate a suitable model. I would expect the Db model to look something like this. What I want to avoid is a model in which I have a separate table for each Contributor role or a separate row in the Contributor table for each instance in which the contributor is associated with the book in any role.

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

I am such an ADO.NET guy that I really don't find it too enjoyable, but I am determined to become, at least, a borderline professional in this important structure.

Quick note: Since the discovery of this question, I was suspended at work, and I did not have time to carefully analyze the answers and play with the results. But I did not want to leave the bounty hanging, because I appreciate the answers that all provided. Therefore, I chose the answer that interests me the most. I want to thank everyone for this.

Answer 1

I worked on a solution that implements the model you proposed, although it is slightly different from what you 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 } } } } 

Answer 2

I am working on books at test.polarcomputer.com
If you have a book object, and this object has a writer, publisher, designer .. all you need is 3 objects in total:

Object 1.book
2.contributor object.
Integration Object -

book object has - bookid
- bookname

object investor is - investor
- name
- typeofcontributor // 0-writer 1-colorist 2-CoverArtists 3-whoever

integration object has - bookid
- contributor
- typeofcontributor // 0-writer 1-colorist 2-CoverArtists 3-whoever

Check it out, if I understand it, I can give you a complete solution.

Answer 3

Create similar collections in a Contributor object with an M: N mapping and use the InverseProperty attribute to declare which collection in the Contributor class corresponds to that collection in the Book class.

 public class Book { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Contributor> Writers { get; set; } public virtual ICollection<Contributor> Artists { get; set; } public virtual ICollection<Contributor> Pencilers { get; set; } public virtual ICollection<Contributor> Inkers { get; set; } public virtual ICollection<Contributor> Colorists { get; set; } public virtual ICollection<Contributor> Letterers { get; set; } public virtual ICollection<Contributor> CoverArtists { get; set; } public virtual ICollection<Contributor> OtherContributors { get; set; } public Book() { Writers = new List<Contributor>(); Artists = new List<Contributor>(); Pencilers = new List<Contributor>(); Inkers = new List<Contributor>(); Colorists = new List<Contributor>(); Letterers = new List<Contributor>(); CoverArtists = new List<Contributor>(); OtherContributors = new List<Contributor>(); } } public class Contributor { public int ContributorId { get; set; } public string Name { get; set; } [InverseProperty("Writers")] public virtual ICollection<Book> WriterOfBooks { get; set; } [InverseProperty("Artists")] public virtual ICollection<Book> ArtistOfBooks { get; set; } [InverseProperty("Pencilers")] public virtual ICollection<Book> PencilerOfBooks { get; set; } [InverseProperty("Inkers")] public virtual ICollection<Book> InkerOfBooks { get; set; } [InverseProperty("Colorists")] public virtual ICollection<Book> ColoristOfBooks { get; set; } [InverseProperty("Letterers")] public virtual ICollection<Book> LettererOfBooks { get; set; } [InverseProperty("CoverArtists")] public virtual ICollection<Book> CoverArtistOfBooks { get; set; } [InverseProperty("OtherContributors")] public virtual ICollection<Book> OtherContributorOfBooks { get; set; } public Contributor() { WriterOfBooks = new List<Book>(); ArtistOfBooks = new List<Book>(); PencilerOfBooks = new List<Book>(); InkerOfBooks = new List<Book>(); ColoristOfBooks = new List<Book>(); LettererOfBooks = new List<Book>(); CoverArtistOfBooks = new List<Book>(); OtherContributorOfBooks = new List<Book>(); } } 

Usage is quite simple:

 using (var dc = new MyDbContext()) { // create sample data var book1 = new Book() { Name = "Book 1" }; dc.Books.Add(book1); var contrib1 = new Contributor() { Name = "Contributor 1" }; var contrib2 = new Contributor() { Name = "Contributor 2" }; var contrib3 = new Contributor() { Name = "Contributor 3" }; dc.Contributors.Add(contrib1); dc.Contributors.Add(contrib2); dc.Contributors.Add(contrib3); dc.SaveChanges(); // add relationships book1.Writers.Add(contrib1); book1.Artists.Add(contrib1); book1.Artists.Add(contrib2); book1.OtherContributors.Add(contrib3); dc.SaveChanges(); } // verify that the contributor 1 has both Artist and Writer relations using (var dc = new MyDbContext()) { var contrib1 = dc.Contributors.Single(c => c.Name == "Contributor 1"); var hasWriter = contrib1.WriterOfBooks.Count == 1; var hasArtist = contrib1.ArtistOfBooks.Count == 1; if (!hasWriter || !hasArtist) { throw new Exception("Houston, we have a problem."); } } 

Answer 4

The data model you are showing is fine, but one thing is clear. You cannot match this as a pure many-to-many association. This is only possible if the BookContributors connection BookContributors contains only BookId and ContributorId .

Therefore, you always need an explicit BookContributor class, and getting a collection of one of the types of contributors will always have this basic form:

 book.BookContributors .Where(bc => bc.Type == type) .Select(bc => bc.Contributor) 

Clumsy compared to what you mean. I'm afraid I can't get around this. There are a few options left in the implementation details.

Option 1: Get all participants, filter later.

First let me get the base model correctly:

 public class Book { public int BookId { get; set; } public string Title { get; set; } public virtual ICollection<BookContributor> BookContributors { get; set; } } public class Contributor { public int ContributorId { get; set; } public string Name { get; set; } public virtual ICollection<BookContributor> BookContributors { get; set; } } public class BookContributor { public int BookId { get; set; } public virtual Book Book { get; set; } public int ContributorId { get; set; } public virtual Contributor Contributor { get; set; } public string Type { get; set; } } 

And display:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<Book>().HasMany(b => b.BookContributors) .WithRequired(bc => bc.Book) .HasForeignKey(bc => bc.BookId); modelBuilder.Entity<Contributor>().HasMany(c => c.BookContributors) .WithRequired(bc => bc.Contributor) .HasForeignKey(bc => bc.ContributorId); modelBuilder.Entity<BookContributor>() .HasKey(bc => new {bc.BookId, bc.ContributorId, bc.Type}); } 

(by the way, here I avoid the term “Discriminator” because it involves the inheritance of TPH, which is not yet applicable).

Now you can add properties to the Book as follows:

 [NotMapped] public IEnumerable<Contributor> Writers { get { return BookContributors.Where(bc => bc.Type == "writer") .Select(bc => bc.Contributor); } } 

The disadvantage of this approach is that you should always ensure that books are downloaded from their BookContributors and their Contributor , or this lazy download. And you cannot use these properties directly in a LINQ query. In addition, it is somewhat difficult to get books and only their unique contributors (i.e., Different).

Option 2: Inheritance is essentially the same

You can make BookContributor abstract base class with a number of descendants:

 public abstract class BookContributor { public int Id { get; set; } public int BookId { get; set; } public virtual Book Book { get; set; } public int ContributorId { get; set; } public virtual Contributor Contributor { get; set; } } public class Artist : BookContributor { } public class Writer : BookContributor { } 

BookContributor now needs the surrogate key Id , because EF will now use the Discriminator field, which is hidden, so it cannot be configured as part of the primary key.

Book can now have properties such as ...

  [NotMapped] public ICollection<Artist> Artists { get { return BookContributors.OfType<Artist>().ToList(); } } 

... but they will still have the same disadvantages as those mentioned above. The only possible advantage is that now you can use types (with compile-time checking) instead of strings (or enumeration values) to move on to different types of BookContributor .

option 3: another model

Perhaps the most promising approach is a slightly different model: books and contributors, where each association between them can have a collection of contributor types. BookContributor now looks like this:

 public class BookContributor { public int BookId { get; set; } public virtual Book Book { get; set; } public int ContributorId { get; set; } public virtual Contributor Contributor { get; set; } public virtual ICollection<BookContributorType> BookContributorTypes { get; set; } } 

And a new type:

 public class BookContributorType { public int ID { get; set; } public int BookId { get; set; } public int ContributorId { get; set; } public string Type { get; set; } } 

Modified Display:

 modelBuilder.Entity<BookContributor>().HasKey(bc => new { bc.BookId, bc.ContributorId }); 

Additional display:

 modelBuilder.Entity<BookContributor>().HasMany(bc => bc.BookContributorTypes).WithRequired(); modelBuilder.Entity<BookContributorType>().HasKey(bct => bct.ID); 

With this model, you can simply get books and their individual contributors if you are not interested in the types of participants ...

 context.Books.Include(b => b.BookContributors .Select(bc => bc.Contributor)) 

... or with types ...

 context.Books.Include(b => b.BookContributors .Select(bc => bc.Contributor)) .Include(b => b.BookContributors .Select(bc => bc.BookContributorTypes)); 

... or books only with writers ...

 context.Books.Select(b => new { Book = b, Writers = b.BookContributors .Where(bc => bc.BookContributorTypes .Any(bct => bct.Type == "artist")) }) 

Again, the last request can be wrapped in a property ...

  [NotMapped] public ICollection<Artist> Artists { get { return BookContributors .Where(bc => bc.BookContributorTypes .Any(bct => bct.Type == "artist")) .Select(bc => bc.Contributor).ToList(); } } 

... however, with all the above warnings.

Answer 5

Your model should be like this:

  [Table("tblBooks")] public class BookTbl { [Key] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int BookID { get; set; } public string BookName { get; set; } } [Table("tblContributor")] public class ContributorTbl { [Key] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int ContID { get; set; } public string Contributor { get; set; } } [Table("tblIntegration")] public class IntegrationTbl { [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int IntID { get; set; } public int BookID { get; set; } [ForeignKey("BookID")] public BookTbl Book { get; set; } public int ContID { get; set; } [ForeignKey("ContID")] public IntegrationTbl Integration { get; set; } }