Entity Framework, MVC 3, One-to-many relationship

Question

Ok - first off apologies - I'm a front end developer (HTML, CSS and JS) trying to do stuff with data - never pretty!

I have a 'Page', that can can have one or many 'Series'. These 'Series' can hold one or many 'Collections' and these 'Collections' can be related to more than one 'Series'. The 'Collection's can hold one or more 'Titles'. This is how I've structured my db:

CREATE TABLE [dbo].[Pages] (
    PageId  INT       NOT NULL PRIMARY KEY, 
    [Title] NCHAR(50) NOT NULL
)

CREATE TABLE [dbo].[Series] (
    [SeriesId] INT        NOT NULL,
    [Title]    NCHAR (50) NOT NULL,
    [PageId]   INT        NOT NULL, 
    PRIMARY KEY CLUSTERED ([SeriesId] ASC), 
    CONSTRAINT [FK_Series_Pages] FOREIGN KEY ([PageId]) REFERENCES [Pages]([PageId])
);

CREATE TABLE [dbo].[Collections] (
    [CollectionId] INT        NOT NULL,
    [Title]        NCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([CollectionId] ASC)
);

CREATE TABLE [dbo].[SeriesCollections] (
    [SeriesCollectionId]   INT    NOT NULL,
    [SeriesId]             INT    NOT NULL, 
    [CollectionId]         INT    NOT NULL, 
    PRIMARY KEY CLUSTERED ([SeriesCollectionId] ASC), 
    CONSTRAINT [FK_SeriesCollections_Series] FOREIGN KEY ([SeriesId]) REFERENCES [Series]([SeriesId]),
    CONSTRAINT [FK_SeriesCollections_Collections] FOREIGN KEY ([CollectionId]) REFERENCES [Collections]([CollectionId])
);

CREATE TABLE [dbo].[Titles] (
    [TitleId]            INT         NOT NULL,
    [Title]              NCHAR (100) NOT NULL,
    [SeriesCollectionId] INT         NOT NULL, 
    PRIMARY KEY CLUSTERED ([TitleId] ASC), 
    CONSTRAINT [FK_Titles_SeriesCollections] FOREIGN KEY ([SeriesCollectionId]) REFERENCES [SeriesCollections]([SeriesCollectionId])

Using Entity Framework I have the following:

public DbSet<Page> Pages { get; set; }
public DbSet<Series> Series { get; set; }
public DbSet<Collection> Collections { get; set; }
public DbSet<SeriesCollection> SeriesCollections { get; set; }
public DbSet<Title> Titles { get; set; }

In the view I want to get the following.

For a given 'Page' (id), I want all the 'Series' and within each of those 'Series', be able to list each of the 'Titles' and its associated 'Collection'.

First off - is my db set up correctly? Secondly, I'm struggling with the db call and viewmodels that would return this.

If anyone can help that'd be great

Thanks in advance

Answer 1

The 'Collection's can hold one or more 'Titles'.

Because of this I would modify your DB table schema:

• In table Titles replace [SeriesCollectionId] by [CollectionId], directly refering to the Collections table.

• In table SeriesCollections remove your PK [SeriesCollectionId] and make instead the remaining two fields [SeriesId] and [CollectionId] to a composite primary key.

• Now, you can model a many-to-many relationship between Series and Collections with EF. Then the join table SeriesCollections isn't part of your model anymore. It's just a hidden table in the DB which is managed by EF. Therefore you can remove public DbSet<SeriesCollection> SeriesCollections { get; set; }.

The model classes could then look like this:

public class Page
{
    public int PageId { get; set; }
    [Required]
    [MaxLength(50)]
    pubic string Title { get; set; }
    public ICollection<Series> Series { get; set; }
}

public class Series
{
    public int SeriesId { get; set; }
    [Required]
    [MaxLength(50)]
    pubic string Title { get; set; }
    public int SeriesId { get; set; }
    public int PageId { get; set; } // FK property, helpful but not required
    public Page Page { get; set; }
    public ICollection<Collection> Collections { get; set; }
}

public class Collection
{
    public int CollectionId { get; set; }
    [Required]
    [MaxLength(50)]
    pubic string Title { get; set; }
    public ICollection<Series> Series { get; set; }
    public ICollection<Title> Titles { get; set; }
}

public class Title
{
    public int TitleId { get; set; }
    [Required]
    [MaxLength(100)]
    pubic string TTitle { get; set; } // must be other name then class
    public int CollectionId { get; set; } // FK property
    public Collection Collection { get; set; }
}

For many-to-many mapping you need Fluent API:

public class MyContext : DbContext
{
    public DbSet<Page> Pages { get; set; }
    public DbSet<Series> Series { get; set; }
    public DbSet<Collection> Collections { get; set; }
    public DbSet<Title> Titles { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Series>()
            .HasMany(s => s.Collections)
            .WithMany(c => c.Series)
            .Map(a =>
            {
                a.MapLeftKey("SeriesId");
                a.MapRightKey("CollectionId");
                a.ToTable("SeriesCollections");
            });
    }
}

EF will figure out all other relationships by convention, I believe.

For a given 'Page' (id), I want all the 'Series' and within each of those 'Series', be able to list each of the 'Titles' and its associated 'Collection'.

With the model above you could then try:

var page = context.Pages.Where(p => p.PageId == id)
    .Include(p => p.Series.Select(s => s.Collections.Select(c => c.Titles)))
    .SingleOrDefault();

It would select the page which contains a list of series with a list of collections with a list of titles.

Not sure if this is exactly what you want, just an untested starting point.

(BTW: You can write your classes first (Code-First) and let EF create your database tables. It's easier during design phase when you want to try some mappings, imo.)

Edit

One thing I forgot: If you really want non-variable fixed length string fields (NCHAR(50)) you must define this explicitely in Fluent API. By default EF would assume NVARCHAR(50) fields with the mapping above. Setting to fixed length columns would look like this:

modelBuilder.Entity<Page>().Property(p => p.Title).IsFixedLength();