EF Core - Missing related entity in class despite foreign key after running Scaffold-DbContext

Question

In a SQL Server database, I have two almost identical tables TBL_1 and TBL_2. Here are the create scripts for both tables:

TBL_1:

CREATE TABLE [dbo].[TBL_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [value_type_ID] [int] NOT NULL,
    [description] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_TBL_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_1] ADD  CONSTRAINT [DF__TBL_1__param__40F9A68C]  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_1] ADD  CONSTRAINT [DF_TBL_1_description]  DEFAULT ('-') FOR [description]
GO

ALTER TABLE [dbo].[TBL_1]  WITH CHECK ADD  CONSTRAINT [TBL_1_TBL_value_types] FOREIGN KEY([value_type_ID])
REFERENCES [dbo].[TBL_Value_Type_Definition] ([value_type_ID])
GO

ALTER TABLE [dbo].[TBL_1] CHECK CONSTRAINT [TBL_1_TBL_value_types]
GO

TBL_2:

CREATE TABLE [dbo].[TBL_2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [value_type_ID] [int] NOT NULL,
 CONSTRAINT [PK_TBL_2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_2] ADD  CONSTRAINT [DF_TBL_2_name]  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_2]  WITH CHECK ADD  CONSTRAINT [TBL_2_TBL_value_types] FOREIGN KEY([value_type_ID])
REFERENCES [dbo].[TBL_Value_Type_Definition] ([value_type_ID])
GO

ALTER TABLE [dbo].[TBL_2] CHECK CONSTRAINT [TBL_2_TBL_value_types]
GO

After executing Scaffold-DbContext, the following classes were generated for these two tables:

public partial class Tbl2
{
    public Tbl2()
    {
        Tbl2Translations = new HashSet<Tbl2Translation>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int ValueTypeId { get; set; }

    public virtual ICollection<Tbl2Translation> Tbl2Translations { get; set; }
}

public partial class Tbl1
{
    public Tbl1()
    {
        Tbl1Translations = new HashSet<Tbl1Translation>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int ValueTypeId { get; set; }
    public string Description { get; set; }

    public virtual TblValueTypeDefinition ValueType { get; set; }
    public virtual ICollection<Tbl1Translation> Tbl1Translations { get; set; }
}

For some reason, a related ValueType is generated for Tbl1, but not for Tbl2. I've already compared the tables in the database but I cannot find any obvious reason for why the related ValueType is missing in Tbl2. This is very frustrating because I have no starting point for debugging this issue so it basically feels like searching for a needle in a haystack. Here is the generated TblValueTypeDefinition class:

public partial class TblValueTypeDefinition
{
    public TblValueTypeDefinition()
    {
        Tbl2s = new HashSet<Tbl2>();
    }

    public int ValueTypeId { get; set; }
    public string Name { get; set; }
    public string Unit { get; set; }
    public string Type { get; set; }

    public virtual ICollection<Tbl2> Tbl2s { get; set; }
}

And here is the create script for this table:

CREATE TABLE [dbo].[TBL_Value_Type_Definition](
    [value_type_ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [unit] [nvarchar](10) NOT NULL,
    [type] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_TBL_Value_Type_Definition] PRIMARY KEY CLUSTERED 
(
    [value_type_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [unit]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [type]
GO

What could be the reason why the related entity is missing in one of the tables?

Answer 1

Table 1 has an error in table name REFERENCES [dbo].[TBL_value_types] instead of REFERENCES [dbo].[TBL_Value_Type_Definition]

Table2 has the same error (Table name). REFERENCES [dbo].[TBL_value_types] instead of REFERENCES [dbo].[TBL_Value_Type_Definition]

The tables are created but the references are broken. No foreign keys are created.

Fixed code - Sql script

CREATE TABLE [dbo].[TBL_Value_Type_Definition](
    [value_type_ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [unit] [nvarchar](10) NOT NULL,
    [type] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_TBL_Value_Type_Definition] PRIMARY KEY CLUSTERED 
(
    [value_type_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [unit]
GO

ALTER TABLE [dbo].[TBL_Value_Type_Definition] ADD  DEFAULT ('-') FOR [type]
GO


CREATE TABLE [dbo].[TBL_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [value_type_ID] [int] NOT NULL,
    [description] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_TBL_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_1] ADD  CONSTRAINT [DF__TBL_1__param__40F9A68C]  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_1] ADD  CONSTRAINT [DF_TBL_1_description]  DEFAULT ('-') FOR [description]
GO

ALTER TABLE [dbo].[TBL_1]  WITH CHECK ADD  CONSTRAINT [TBL_1_TBL_value_types] FOREIGN KEY([value_type_ID])
REFERENCES [dbo].[TBL_Value_Type_Definition] ([value_type_ID])
GO

ALTER TABLE [dbo].[TBL_1] CHECK CONSTRAINT [TBL_1_TBL_value_types]
GO


CREATE TABLE [dbo].[TBL_2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [value_type_ID] [int] NOT NULL,
 CONSTRAINT [PK_TBL_2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_2] ADD  CONSTRAINT [DF_TBL_2_name]  DEFAULT ('-') FOR [name]
GO

ALTER TABLE [dbo].[TBL_2]  WITH CHECK ADD  CONSTRAINT [TBL_2_TBL_value_types] FOREIGN KEY([value_type_ID])
REFERENCES [dbo].[TBL_Value_Type_Definition] ([value_type_ID])
GO

ALTER TABLE [dbo].[TBL_2] CHECK CONSTRAINT [TBL_2_TBL_value_types]
GO

Scaffold-DbContext from Microsoft.EntityFrameworkCore.Tools nuget package

Scaffold-DbContext -Connection "Data Source=localhost; Initial Catalog=SO1;User ID=sa;Password=100;Encrypt=False" -Provider Microsoft.EntityFrameworkCore.SqlServer

GeneratedCode

SO1Context.cs

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EfCoreMissingRelated
{
    public partial class SO1Context : DbContext
    {
        public SO1Context()
        {
        }

        public SO1Context(DbContextOptions<SO1Context> options)
            : base(options)
        {
        }

        public virtual DbSet<Tbl1> Tbl1s { get; set; } = null!;
        public virtual DbSet<Tbl2> Tbl2s { get; set; } = null!;
        public virtual DbSet<TblValueTypeDefinition> TblValueTypeDefinitions { get; set; } = null!;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlServer("Data Source=localhost; Initial Catalog=SO1;User ID=sa;Password=100;Encrypt=False");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Tbl1>(entity =>
            {
                entity.ToTable("TBL_1");

                entity.Property(e => e.Id).HasColumnName("ID");

                entity.Property(e => e.Description)
                    .HasMaxLength(150)
                    .HasColumnName("description")
                    .HasDefaultValueSql("('-')");

                entity.Property(e => e.Name)
                    .HasMaxLength(100)
                    .HasColumnName("name")
                    .HasDefaultValueSql("('-')");

                entity.Property(e => e.ValueTypeId).HasColumnName("value_type_ID");

                entity.HasOne(d => d.ValueType)
                    .WithMany(p => p.Tbl1s)
                    .HasForeignKey(d => d.ValueTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("TBL_1_TBL_value_types");
            });

            modelBuilder.Entity<Tbl2>(entity =>
            {
                entity.ToTable("TBL_2");

                entity.Property(e => e.Id).HasColumnName("ID");

                entity.Property(e => e.Name)
                    .HasMaxLength(100)
                    .HasColumnName("name")
                    .HasDefaultValueSql("('-')");

                entity.Property(e => e.ValueTypeId).HasColumnName("value_type_ID");

                entity.HasOne(d => d.ValueType)
                    .WithMany(p => p.Tbl2s)
                    .HasForeignKey(d => d.ValueTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("TBL_2_TBL_value_types");
            });

            modelBuilder.Entity<TblValueTypeDefinition>(entity =>
            {
                entity.HasKey(e => e.ValueTypeId);

                entity.ToTable("TBL_Value_Type_Definition");

                entity.Property(e => e.ValueTypeId).HasColumnName("value_type_ID");

                entity.Property(e => e.Name)
                    .HasMaxLength(50)
                    .HasColumnName("name")
                    .HasDefaultValueSql("('-')");

                entity.Property(e => e.Type)
                    .HasMaxLength(10)
                    .HasColumnName("type")
                    .HasDefaultValueSql("('-')");

                entity.Property(e => e.Unit)
                    .HasMaxLength(10)
                    .HasColumnName("unit")
                    .HasDefaultValueSql("('-')");
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Tbl1.cs

using System;
using System.Collections.Generic;

namespace EfCoreMissingRelated
{
    public partial class Tbl1
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
        public int ValueTypeId { get; set; }
        public string Description { get; set; } = null!;

        public virtual TblValueTypeDefinition ValueType { get; set; } = null!;
    }
}

Tbl2.cs

using System;
using System.Collections.Generic;

namespace EfCoreMissingRelated
{
    public partial class Tbl2
    {
        public int Id { get; set; }
        public string Name { get; set; } = null!;
        public int ValueTypeId { get; set; }

        public virtual TblValueTypeDefinition ValueType { get; set; } = null!;
    }
}

TblValueTypeDefinition.cs

using System;
using System.Collections.Generic;

namespace EfCoreMissingRelated
{
    public partial class TblValueTypeDefinition
    {
        public TblValueTypeDefinition()
        {
            Tbl1s = new HashSet<Tbl1>();
            Tbl2s = new HashSet<Tbl2>();
        }

        public int ValueTypeId { get; set; }
        public string Name { get; set; } = null!;
        public string Unit { get; set; } = null!;
        public string Type { get; set; } = null!;

        public virtual ICollection<Tbl1> Tbl1s { get; set; }
        public virtual ICollection<Tbl2> Tbl2s { get; set; }
    }
}