EF Core database-first: proper workflow

Question

We are starting a new .NET Core project which will be using an existing large database with hundreds of tables. We will be using EF Core Power Tools to reverse engineer the database. We are trying to Reverse Engineer a model that is similar to our old application, including inheritance hierarchy, which is not present in the database schema, and therefore cannot be scaffolded.

After EF Core scaffolds the POCO entities and DbContext from the tables below, there are immediately things that need to be changed due to inheritance and other reasons. Some things can be changed through tweaks in partial classes, but other things need to be directly changed in POCOs or DbContext.

With that said, every time I try to reverse engineer my database, my changes in the POCO classes and DbContext will be overridden.

Question 1: what is the most appropriate database-first workflow

1. Scaffold once only: switch to using migrations and use entities and EF model configuration as the source of truth.
2. Manually update the entities and EF model configuration mappings when database changes.
3. Repeated scaffolding (reverse-engineering).

Question 2: if doing repeated scaffolding workflow, how do I avoid continuously overriding the changes in the model/entities each time I scaffold the database.

To persist data and to allow inheritance to work the same way it does in our old application, the following changes are needed:

1. In Animal class, remove all navigation references to children (dog, cat, mouse, etc.). Base class does not need a reference to all its child derived classes.

2. Modify Dog, Cat, Mouse classes to specify each one inherits from Animal base class.

3. Remove AnimalId from each Dog, Cat, Mouse classes as this property will inherit from base class.

4. Remove public virtual Animal Animal { get; set; } from each child class as this is an inheritance relationship instead.

5. In AnimalContext, remove entity.HasKey(e => e.AnimalId) from Dog, Cat, Mouse mappings due to inheritance.

6. In AnimalContext, remove entity.Property(e => e.AnimalId).ValueGeneratedNever() as this throws an exception:

   Cannot insert explicit value for identity column in table when IDENTITY_ISNERT is set to OFF.

7. In AnimalContext, remove entity.HasOne(d => d.Animal).WithOne(p => p.Cat).HasForeignKey<Cat>(d => d.AnimalId).HasConstraintName("FK_Cat_Animal") due to inheritance. Same for Dog and Mouse.

Animal.cs:

public partial class Animal
{
    public int AnimalId { get; set; }
    public int AnimalTypeId { get; set; }
    public string AnimalName { get; set; }

    public virtual Cat Cat { get; set; }
    public virtual Dog Dog { get; set; }
    public virtual Mouse Mouse { get; set; }

    public virtual ICollection<PhysicalAddress> PhysicalAddresses { get; set; } = new List<PhysicalAddress>();
}

Dog.cs:

public partial class Dog
{
    public int AnimalId { get; set; }
    public string Name1 { get; set; }
    public string BarkSound { get; set; }
    public int RowVersion { get; set; }

    public virtual Animal Animal { get; set; }
}

Cat.cs:

public partial class Cat
{
    public int AnimalId { get; set; }
    public string Name1 { get; set; }
    public string FavoriteFood { get; set; }
    public int RowVersion { get; set; }

    public virtual Animal Animal { get; set; }
}

Mouse.cs:

public partial class Mouse
{
    public int AnimalId { get; set; }
    public string Name1 { get; set; }
    public string HidingSpot { get; set; }
    public int RowVersion { get; set; }

    public virtual Animal Animal { get; set; }
}

PhysicalAddress.cs:

public partial class PhysicalAddress
{
    public int AddressId { get; set; }
    public int? AnimalId { get; set; }
    public int? NoticeCopyId { get; set; }

    public string Street { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public int? RegionId { get; set; }

    public int RowVersion { get; set; }

    public int SequenceNumber { get; set; }

    public virtual Animal Animal { get; set; }
}

AnimalContext.cs:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Animal>(entity =>
    {
        entity.ToTable("Animal", "dbo");

        entity.Property(e => e.AnimalName)
            .HasMaxLength(255)
            .IsUnicode(false);
    });

    modelBuilder.Entity<Cat>(entity =>
    {
        entity.HasKey(e => e.AnimalId);

        entity.ToTable("Cat", "dbo");

        entity.Property(e => e.AnimalId).ValueGeneratedNever();
        entity.Property(e => e.FavoriteFood)
            .HasMaxLength(50)
            .IsUnicode(false);
        entity.Property(e => e.Name1)
            .HasMaxLength(60)
            .IsUnicode(false);
        entity.Property(e => e.RowVersion).HasDefaultValue(1);

        entity.HasOne(d => d.Animal).WithOne(p => p.Cat)
            .HasForeignKey<Cat>(d => d.AnimalId)
            .HasConstraintName("FK_Cat_Animal");
    });

    modelBuilder.Entity<Dog>(entity =>
    {
        entity.HasKey(e => e.AnimalId);

        entity.ToTable("Dog", "dbo");

        entity.Property(e => e.AnimalId).ValueGeneratedNever();
        entity.Property(e => e.BarkSound)
            .HasMaxLength(50)
            .IsUnicode(false);
        entity.Property(e => e.Name1)
            .HasMaxLength(60)
            .IsUnicode(false);
        entity.Property(e => e.RowVersion).HasDefaultValue(1);

        entity.HasOne(d => d.Animal).WithOne(p => p.Dog)
            .HasForeignKey<Dog>(d => d.AnimalId)
            .HasConstraintName("FK_Dog_Animal");
    });

    modelBuilder.Entity<Mouse>(entity =>
    {
        entity.HasKey(e => e.AnimalId);

        entity.ToTable("Mouse", "dbo");

        entity.Property(e => e.AnimalId).ValueGeneratedNever();
        entity.Property(e => e.HidingSpot)
            .HasMaxLength(50)
            .IsUnicode(false);
        entity.Property(e => e.Name1)
            .HasMaxLength(60)
            .IsUnicode(false);
        entity.Property(e => e.RowVersion).HasDefaultValue(1);

        entity.HasOne(d => d.Animal).WithOne(p => p.Mouse)
            .HasForeignKey<Mouse>(d => d.AnimalId)
            .HasConstraintName("FK_Mouse_Animal");
    });

    modelBuilder.Entity<PhysicalAddress>(entity =>
    {
        entity.HasKey(e => e.AddressId).HasName("PK_Address2");

        entity.ToTable("PhysicalAddress", "dbo");

        entity.HasIndex(e => new { e.AnimalId, e.SequenceNumber, e.NoticeCopyId }, "IX_Address_2").IsUnique();

        entity.Property(e => e.City)
            .HasMaxLength(50)
            .IsUnicode(false);
        entity.Property(e => e.PostalCode)
            .HasMaxLength(50)
            .IsUnicode(false);
        entity.Property(e => e.RowVersion).HasDefaultValue(1);
        entity.Property(e => e.SequenceNumber).HasDefaultValue(1);
        entity.Property(e => e.Street)
            .HasMaxLength(50)
            .IsUnicode(false);

        entity.HasOne(d => d.Animal).WithMany(p => p.PhysicalAddresses)
            .HasForeignKey(d => d.AnimalId)
            .OnDelete(DeleteBehavior.Cascade)
            .HasConstraintName("FK_Address_Animal2");
    });

    OnModelCreatingPartial(modelBuilder);
}

SQL script:

CREATE TABLE [dbo].[Animal]
(
    [AnimalId] [dbo].[IdType] IDENTITY(1,1) NOT NULL,
    [AnimalTypeId] [int] NOT NULL,
    [AnimalName] [varchar](255) NULL,

    CONSTRAINT [PK_Animal] 
        PRIMARY KEY CLUSTERED ([AnimalId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PhysicalAddress]
(
    [AddressId] [dbo].[IdType] IDENTITY(1,1) NOT NULL,
    [AnimalId] [dbo].[IdType] NULL,
    [NoticeCopyId] [dbo].[IdType] NULL,
    [Street] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [PostalCode] [varchar](50) NULL,
    [RegionId] [int] NULL,
    [RowVersion] [int] NOT NULL,
    [SequenceNumber] [int] NOT NULL,

    CONSTRAINT [PK_Address2] 
        PRIMARY KEY CLUSTERED ([AddressId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
        CONSTRAINT [IX_Address_2] 
            UNIQUE NONCLUSTERED ([AnimalId] ASC, [SequenceNumber] ASC, [NoticeCopyId] ASC) 
                   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                         ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                         OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PhysicalAddress] 
    ADD CONSTRAINT [DF_Address_DataVersion2] DEFAULT (1) FOR [RowVersion]
GO

ALTER TABLE [dbo].[PhysicalAddress] 
    ADD CONSTRAINT [DF_Address_SequenceNumber2] DEFAULT (1) FOR [SequenceNumber]
GO

ALTER TABLE [dbo].[PhysicalAddress] WITH NOCHECK 
    ADD CONSTRAINT [FK_Address_NoticeCopy2] 
        FOREIGN KEY([NoticeCopyId]) REFERENCES [dbo].[NoticeCopy] ([NoticeCopyId])
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PhysicalAddress] CHECK CONSTRAINT [FK_Address_NoticeCopy2]
GO

ALTER TABLE [dbo].[PhysicalAddress] WITH NOCHECK 
    ADD CONSTRAINT [FK_Address_Animal2] 
        FOREIGN KEY([AnimalId]) REFERENCES [dbo].[Animal] ([AnimalId])
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PhysicalAddress] CHECK CONSTRAINT [FK_Address_Animal2]
GO

ALTER TABLE [dbo].[PhysicalAddress] WITH NOCHECK 
    ADD CONSTRAINT [FK_Address_Region2] 
        FOREIGN KEY([RegionId]) REFERENCES [dbo].[Region] ([RegionId])
GO

ALTER TABLE [dbo].[PhysicalAddress] CHECK CONSTRAINT [FK_Address_Region2]
GO

ALTER TABLE [dbo].[PhysicalAddress] WITH NOCHECK 
    ADD CONSTRAINT [CK_Address_Must_Have_Parent2] 
        CHECK (([AnimalId] IS NOT NULL OR [NoticeCopyId] IS NOT NULL))
GO

ALTER TABLE [dbo].[PhysicalAddress] CHECK CONSTRAINT [CK_Address_Must_Have_Parent2]
GO

CREATE TABLE [dbo].[Dog]
(
    [AnimalId] [int] NOT NULL,
    [Name1] [varchar](60) NULL,
    [BarkSound] [varchar](50) NULL,
    [RowVersion] [int] NOT NULL,

    CONSTRAINT [PK_Dog] 
        PRIMARY KEY CLUSTERED ([AnimalId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Dog] 
    ADD CONSTRAINT [DF_Dog_RowVersion] DEFAULT (1) FOR [RowVersion]
GO

ALTER TABLE [dbo].[Dog] WITH NOCHECK 
    ADD CONSTRAINT [FK_Dog_Animal] 
        FOREIGN KEY([AnimalId]) REFERENCES [dbo].[Animal] ([AnimalId])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Dog] CHECK CONSTRAINT [FK_Dog_Animal]
GO

CREATE TABLE [dbo].[Cat]
(
    [AnimalId] [int] NOT NULL,
    [Name1] [varchar](60) NULL,
    [FavoriteFood] [varchar](50) NULL,
    [RowVersion] [int] NOT NULL,

    CONSTRAINT [PK_Cat] 
        PRIMARY KEY CLUSTERED ([AnimalId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Cat] 
    ADD CONSTRAINT [DF_Cat_RowVersion] DEFAULT (1) FOR [RowVersion]
GO

ALTER TABLE [dbo].[Cat] WITH NOCHECK 
    ADD CONSTRAINT [FK_Cat_Animal] 
        FOREIGN KEY([AnimalId]) REFERENCES [dbo].[Animal] ([AnimalId])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Cat] CHECK CONSTRAINT [FK_Cat_Animal]
GO

CREATE TABLE [dbo].[Mouse]
(
    [AnimalId] [int] NOT NULL,
    [Name1] [varchar](60) NULL,
    [HidingSpot] [varchar](50) NULL,
    [RowVersion] [int] NOT NULL,

    CONSTRAINT [PK_Mouse] 
        PRIMARY KEY CLUSTERED ([AnimalId] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Mouse] 
    ADD CONSTRAINT [DF_Mouse_RowVersion] DEFAULT (1) FOR [RowVersion]
GO

ALTER TABLE [dbo].[Mouse] WITH NOCHECK 
    ADD CONSTRAINT [FK_Mouse_Animal] 
        FOREIGN KEY([AnimalId]) REFERENCES [dbo].[Animal] ([AnimalId])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Mouse] CHECK CONSTRAINT [FK_Mouse_Animal]
GO

Answer 1

I use database first exclusively, for existing and new databases and I never bother with scaffolding. Simply create the entity classes to represent the tables and use a combination of attributes and IEntityTypeConfiguration<TEntity> implementations for things like relationships /w ApplyConfigurationsFromAssembly() to locate the entity type configurations. You don't have to map out the entire database in one go, it can be fleshed out as needed.

As for inheritance, a base class should not have any references to it's subclasses. References are a "has-a" relationship, which is call composition. Inheritance is an "is-a" relationship. Cat, Dog, etc. "are an" animal where code using these entities decide whether they are working with a Cat or Dog specifically, or working with Animals generally. Anything "general" about animals that all animals share belongs in the Animal base class and nothing more. Anything specific to a particular type of animal belongs in the subclass.

Animal should have no navigation reference to Cat or Dog or Mouse etc., and Cats/Dogs/etc. should have no reference back to an Animal. References between entity class types are called navigation properties and they are used to represent relationships between two different entities. In an inheritance model, a Cat does not need a reference to a row in an Animal table (if that is how the schema is mapped out) it will have access to its Animal properties automatically through inheritance. EF will take care of linking and loading those properties automatically. Trying to map out that a Cat entity has a reference to it's inherited Animal base entity will only confuse the mappings. EF will expect that a Cat has a direct relationship to some other animal and expect a counter-relationship.

With entities and inheritance you typically would expose DbSets of Cats, Dogs, etc. rather than Animals. EF does need to be configured based on how these are stored and mapped in the database. This includes Table-Per-Hierarchy (TPH), Table-Per-Concrete Class (TPC), and Table-Per-Type (TPT). Configuring for each of these scenarios is outlined quite well by MS. (https://learn.microsoft.com/en-us/ef/core/modeling/inheritance) Any method working with something like a collection of Animals should not care whether it gets a mix of cats, dogs, etc. If it does, it should be split up into sub-class specific methods.

As a general rule when it comes to entity design, inheritance should be used sparingly and not confused with composition. For example I have seen schemas in production systems that look something like this:

public class Address
{
    // Address related properties.
}

public class Customer : Address
{
    // customer properties
}

public class Company : Address
{
    // company properties
}

Here since companies and customers both had address properties and the developers only wanted to write those once, they created an Address as a base class and extended it for each. This is wrong in the sense that companies and customers are not the same thing, they aren't addresses, they merely contain an address. Instead, this should have been solved with composition. Companies and Customers "have an" address:

public class Address
{
   // Address related properties.
}

public class Customer
{
    // customer properties

    public virtual Address Address { get; protected set; }
}

public class Company
{
    // company properties

    public virtual Address Address { get; protected set; }
}

Inheritance is about enabling common behavior shared by subclasses. One significant limitation around inheritance is that you can only have one base class, no multiple-inheritance in C#. Say you want to share something like Contact Details as well as addresses? Composition on the other hand allows companies and customers to reference Addresses, Contacts, etc. and fits well with relational mapping. Be cautious about what relationships between entities are actually "is a" vs. "has a" when planning out the entity classes around the tables.