Error when instantiating object from model class

Question

I've hit a snag. On my Summary page I want to pull from a table, YearSummariesStatic, as well as a view, YearSummariesDynamic. I have everything working fine with just the Static table. But I'm getting an error when I try to add Dynamic and I haven't been able to resolve it.

Index.cshtml.cs
using Alpha.Data;
using Alpha.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;

namespace Alpha.Pages {
    public class IndexModel : PageModel {
        private AlphaContext context;

        public IEnumerable<YearSummary> YearSummariesStatic { get; set; }
            =Enumerable.Empty<YearSummary>();

        public IndexModel(AlphaContext ctx) {
            context = ctx;
        }

        public void OnGet() {
            YearSummariesStatic = context.YearSummariesStatic.OrderByDescending(s => s.Year);
        }
    }
}

Index.cshtml
@page
@model Alpha.Pages.IndexModel
@using Alpha.Models;

<table class="table table-bordered table-striped table-sm text-center">
    <tbody>
        @foreach (YearSummary s in Model.YearSummariesStatic) {
            <tr>
                <td><a href="/Years/@s.Year">@s.Year</a></td>
                <td>@s.League</td>
                <td>@if (@s.League is not null)
                    @s.LeagueRecord</td>
                <td>@s.RegularSeasonRecord</td>
                <td>@if (@s.PostSeasonRecord != "0-0")
                        @s.PostSeasonRecord</td>
                <td>@s.FullSeasonRecord</td>
                <td>@s.HeadCoach</td>
                <td>@s.Notes</td>
            </tr>
        }
    </tbody>
</table>

AlphaContext.cs
using Alpha.Data.Configuration;
using Alpha.Models;
using Microsoft.EntityFrameworkCore;

namespace Alpha.Data {
    public class AlphaContext : DbContext {
        #nullable disable
        public AlphaContext(DbContextOptions options) : base(options) {
        }
        #nullable restore

        public DbSet<YearSummary> YearSummariesStatic { get; set; }
//      public DbSet<YearSummary> YearSummariesDynamic { get; set; }
        public DbSet<GameSummary> GameSummaries { get; set; }
        public DbSet<OpponentSummary> OpponentSummaries { get; set; }

        protected override void OnModelCreating(ModelBuilder builder) {
            builder.ApplyConfiguration(new YearSummaryConfiguration());
            builder.ApplyConfiguration(new GameSummaryConfiguration());
            builder.ApplyConfiguration(new OpponentSummaryConfiguration());
        }
    }
}

Everything works fine with this code. However, when I uncomment the YearSummariesDynamic line in AlphaContext, I get a run time error:

SqlException: Invalid object name 'YearSummary'

on this line in Index.cshtml:

@foreach (YearSummary s in Model.YearSummariesStatic) {

Any help greatly appreciated!

Answer 1

During a database migration, EF Core will attempt to create a migration for each DbSet that maps to a database table. Because two DbSets reference the same entity type, EF Core may encounter mapping conflicts and not be able to determine how to create a corresponding table for both DbSets. To avoid these issues, you can use different entity types for two DbSets in the AlphaContext, even if the two entity types have the same properties. You can do this by setting YearSummaryStatic and YearSummaryDynamic to be two derived classes of YearSummary, you can create these two classes and use inheritance to do so, here's an example you can use as a reference:

public class YearSummary
{
      
    public int Id { get; set; }
    public string Year { get; set; }
    public string League { get; set; }
    public string LeagueRecord { get; set; }
    public string RegularSeasonRecord { get; set; }
    public string PostSeasonRecord { get; set; }
    public string FullSeasonRecord { get; set; }
    public string HeadCoach { get; set; }
    public string Notes { get; set; }

        
}

public class YearSummaryStatic : YearSummary
{
        
}

public class YearSummaryDynamic : YearSummary
{

}

AlphaContext：

public class AlphaContext : DbContext
{
    public AlphaContext(DbContextOptions<AlphaContext> options) : base(options)
    {
    }

    public DbSet<YearSummaryStatic> YearSummariesStatic { get; set; }
    public DbSet<YearSummaryDynamic> YearSummariesDynamic { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new YearSummaryStaticConfiguration());
        modelBuilder.ApplyConfiguration(new YearSummaryDynamicConfiguration());
        base.OnModelCreating(modelBuilder);
    }

}

Configuration :

public class YearSummaryStaticConfiguration : IEntityTypeConfiguration<YearSummaryStatic>
{
    public void Configure(EntityTypeBuilder<YearSummaryStatic> builder)
    {
        builder.ToTable("YearSummariesStatic");
        
    }
}



public class YearSummaryDynamicConfiguration : IEntityTypeConfiguration<YearSummaryDynamic>
{
    public void Configure(EntityTypeBuilder<YearSummaryDynamic> builder)
    {
        builder.ToTable("YearSummariesDynamic");
        
    }
}

After the configuration is complete, I will generate two tables with the same structure: