Mapping relations between two tables that have two linking tables in .NET MVC

Question

I'm trying to figure out if it's possible to set up entity relationships between two tables which are linked by two linking tables. I'm not sure it's even possible.

Imagine a bookstore database. There are Books and there are Sections tables. These tables are connected via a linking table called SectionBooks (many-to-many, since a book may be in more than one section). But each section in the store will have a best-sellers list, which is a non-exhaustive ranking of the top books for that section. This will be in BestSellers.

The data might look like this:

[Books]
BookId  Title           Author
1000    Allegiant       Roth
1001    The Book Thief  Zusak
1002    Mocking Jay     Collins
1003    Fan Girl        Rowell
1004    I am Malala     Yousafzai

[Sections]
SectionId Name
1  Fiction
2  Young Adult

[SectionBooks]
SectionId  BookId
2          1000
2          1001
2          1002
2          1003
2          1004

[BestSellers]
SectionId  BookId  BestSellerIndex
2          1000    1
2          1001    2
2          1002    3

How would you code this so you can get both the data from SectionBooks and BestSellers? I'm doing this with existing tables, so this would be database-first, but any other implementation information would probably help.

Extra information might help. Here's a sample query that would return all of the books in a section ranked by their BestSellerIndex (I'm converting the nulls to 999 so that ranking is more obvious.)

SELECT 
    ISNULL(BestSellerIndex, 999) AS BestSellerIndex
    SectionBooks.SectionId,
    Books.Id, Books.Title, Books.Author
FROM Books
INNER JOIN SectionBooks ON Books.BookId = SectionBooks.BookId
LEFT OUTER JOIN BestSellers BestSellers ON Book.BookId = BestSellers.BookId
WHERE SectionBooks.SectionId = 2 AND (BestSellers.SectionId = 2 OR BestSellers.SectionId IS NULL)  
ORDER BY BestSellerIndex, Title DESC

This would result in the following dataset:

BestSellerIndex  SectionId  BookId  Title           Author
0                2          1000    Allegiant       Roth
1                2          1001    The Book Thief  Zusak
2                2          1002    Mocking Jay     Collins
999              2          1003    Fan Girl        Rowell
999              2          1004    I am Malala     Yousafzai

Answer 1

Reading through your example, I can see where you are trying to go with this. In my opinion, this is a case where you would probably be best served by a hybrid approach to Entity Framework. The Different Modeling Strategies ("Database-First", "Model-First", "Code-First") aren't very clearly named. You can use Code-First, for example, even with an existing Database; all Code-First means is that the "Model Classes" are defined through Code, rather than a designer. While Code-First can create a database, it works just fine with an existing database, hence the "hybrid approach".

This domain model is much more simply boiled down using Code-First, with a sprinkling of Fluent API.

Using Code-First modeling, your models are very simple:

public class Book()
{
    public int BookId {get;set;}
    public string Title {get;set;}
    public string Author {get;set;}

    public virtual ICollection<Section> Sections {get;set;}
} 

public class Section()
{
    public int SectionId {get;set;}
    public string Name {get;set;}

    public virtual ICollection<Book> Books {get;set;}
}

public class BestSeller()
{
    public int BestSellerIndex {get;set;}

    public Section Section {get;set;}
    public Section Book {get;set;}
}

then you use some Fluent API to make sure the table gets mapped the right way, for example...

// Define the BestSeller to have a composite key from the 2 joined tables
modelBuilder.Entity<BestSeller>().HasKey(t => 
                                  new {t.Section.SectionId, t.Book.BookId});

// Define the Many to Many mapping
modelBuilder.Entity<Book>()
                           .HasMany(b => b.Section)
                           .WithMany(s => s.Book)
                           .Map(
                            m => {
                                  m.MapLeftKey("BookId");
                                  m.MapRightKey("SectionId");
                                  m.ToTable("SectionBooks");
                            });

you may need other Fluent API to make sure that the table names are pluralized, and that the table schema matches, more info on that is on MSDN

from here, you can simply enumerate through the collections... i.e.

bestSellers = context.BestSellers();
foreach (var bestSeller in bestSellers)
{
      var title = bestSeller.Book.Title;
      var section = bestSeller.Section.Name;

      ...
}

This could probably be optimized or tweaked even further, and you may be able to achieve the same results by using the Database-First model designer, but I'm not familiar with all the options there, and this method gives you much more precise control over your end result (as the Database-First Designer creates classes it manages, that you can't change easily).