Revolutionizing Database-First Development with Magic EF Scaffolding

Author: Lance Wright (Magic Coding Man)

Published: December 22, 2024

Database-first development has long been the underdog in modern enterprise workflows. While its simplicity and alignment with SQL-first approaches have undeniable appeal, its glaring flaws have driven many developers to abandon it in favor of code-first methodologies. But what if I told you that database-first doesn’t have to be the clunky, outdated relic it’s often made out to be? What if we could embrace the best of both worlds—retaining the raw power and control of database-first while solving its most notorious issues? Enter Magic EF Scaffolding.

Magic EF Scaffolding isn’t just another tool; it’s a complete overhaul of what database-first development can and should be. By resolving the pain points that have plagued this approach for years, Magic EF doesn’t just make database-first viable—it makes it a first-class citizen in the modern developer’s toolkit. But before we dive into the magic, let’s break down why database-first fell out of favor, why code-first rose to prominence, and the cracks in the code-first facade that Magic EF aims to fill.

Why Database-First Fell Out of Favor

Database-first development was once the go-to strategy, especially in enterprises with dedicated database administrators. It’s a natural fit for teams that value SQL-first workflows or need to work with existing databases. But here’s the problem: while it sounds great in theory, it’s often a nightmare in practice. Let’s unpack why.

Scaffolding Overwrites Your Code: Every time you scaffold, your changes to models, DbContexts, and relationships risk being obliterated. This means extra work to rebuild your extensions and customizations after every scaffold run.
Ambiguous Context Issues: Modern scaffolding tools like dotnet ef generate overlapping namespaces and conflicting annotations, resulting in ambiguous index errors that require tedious manual fixes.
Manual Intervention Required: Critical configurations, like OnModelCreating, are scaffolded into read-only contexts, locking you out of making custom adjustments without manual intervention.
No Pipeline Support: Database-first falls flat in CI/CD pipelines. Scaffolding for each environment introduces inconsistencies, especially with auto-generated key names or indexes. It’s nearly impossible to automate this process without introducing breaking changes.
Git Mess: Scaffolding modifies not only new models but also existing ones by adding inverse properties, cluttering your commit history with irrelevant changes.
Poor/Bad Migrations: Database first has historically had poor solutions to automate the migration process along with your deployment pipeline process. Solutions tend to require manual intervention, have poor support, or are horribly complex.

The result? Database-first becomes an exercise in frustration, forcing developers to repeat the same tedious fixes over and over. It’s no surprise that database-first has gained a reputation as “legacy” and been overshadowed by code-first.

Why Code-First Became the Darling of Developers

Code-first development addressed many of the pain points of database-first. It gave developers the control they craved, streamlined migrations, and made pipelines a breeze. Let’s give credit where it’s due—code-first is a fantastic approach. But like anything else, it’s not without its flaws.

Control and Flexibility: With code-first, developers have full control over models, migrations, and configurations. You can easily edit your DbContext and models without worrying about overwrites or scaffolding quirks.
Seamless Migrations: Code-first migrations are a game-changer. Need to add a column? Update your model, generate a migration, and apply it. The process is straightforward and well-suited for automated pipelines. In my opinion, the migration process with code first is vastly superior when making updates in pipelines between environments.
Unified Workflow: Code-first eliminates the disconnect between database and application logic, making it easier for developers to manage everything in one place.

But here’s the kicker: code-first isn’t perfect either.

Poor Fit for Existing Databases: Integrating with a pre-existing database using code-first is a headache. It’s hard to reverse-engineer a database into models, and you lose the simplicity of direct SQL interaction.
Lack of Separation of Concerns: Code-first blends application logic and database design, making it difficult to separate responsibilities between software developers and database administrators.
Complex Workflow: Updating models, generating migrations, and applying changes isn’t as effortless as it sounds. It’s a lot of steps compared to just writing a SQL script and scaffolding.
Drift and Visibility Issues: Code-first struggles with capturing “drift”—the small, automatic changes databases make over time, like index generation or key naming. And the lack of a direct database-first view can make debugging harder.
Slow & Tedious Setup: My largest issue personally with code first is how slow it is to work with. I hate manually writing every aspect of the DbContext, referenced connections, and models. I dislike running the migration generation file and then utilizing it after every change in development.

So, while code-first is undeniably powerful, it’s not a one-size-fits-all solution. It works great for greenfield projects or when the database is entirely within the application’s control. But in the real world, with slower development setup, multi-application setups, or complex enterprise environments, it starts to show its cracks.

Enter Magic EF: Bridging the Gap

This is where Magic EF Scaffolding steps in, bridging the gap between database-first and code-first. It keeps the power and simplicity of database-first while resolving its flaws, bringing new capabilities, and introducing many of the benefits of code-first. So, lets go over what Magic EF Scaffolding brings to database first in the world of Csharp.

Automated Protocol

One of the most powerful aspects of Magic EF is how simple the resolutions are. Simplicity tends to be the best resolution in any application. And this simplicity comes from the automated generation of the Magic EF protocol and the automated process of how it can be utilized.

Magic EF doesn’t replace dotnet scaffolding. It enhances the scaffolding process and is meant to be paired. You’re meant to run the dotnet scaffolding first which creates the scaffolded models and ReadOnlyDbContext. Then immediately after the dotnet scaffold runs, we utilize the Magic EF commands to enhance, repair, and build the protocol. Which in the provided GitHub link, there’s a simple power script file provided that bundles all the automated commands together for you!

Resolving Scaffolding Issues

When running Magic EF after the dotnet scaffolding process, it has the following commands that resolve major issues:

--ambiguousIndex: Fixes all ambiguous context issues generated from the dotnet scaffolding process.
--removeOnConfiguring: Removes the OnConfiguring from the ReadOnlyDbContext as this should be controlled by the developer and must be removed for the Magic EF protocol.
--separateVirtualProperties: Removes the generated scaffolded virtual inverse properties relating foreign connections. Then builds separated renamed files connected to the original partial class and places the virtual properties in the new location. Thus making a cleaner GIT repository for referencing changes and history.

These commands alone already make pairing Magic EF a powerful helper when utilizing database first scaffolding. Resolving issues and making a cleaner GIT environment. But this also resolves tremendous issues that come with database first scaffolding within a pipeline! Allowing you to safely scaffold your codebase to have the DbContext match accordingly per environment no matter the change or drift!

A Protocol That Truly Is Magic

Resolving commonplace scaffolding errors for an easier development experience and resolving pipeline issues is only the start. Magic EF brings to the table a protocol that not only resolves additional significant issues surrounding database first, but it extends the capabilities in ways I believe is beyond code first.

The protocol is about separating what is read only from what is an extension to your scaffold. Because remember, when you scaffold, you can’t touch the created files as it’ll be generated over when you run the scaffold again. This is why after the dotnet scaffolding process, one of the more powerful commands within Magic EF is the --scaffoldProtocol argument. This command will detect new additions to the codebase and what was scaffolded. It will then generate multiple related files to each new scaffolded item, and it will NEVER regenerate over that file, as these are your extensions to the scaffold. We'll go into what it generates, but lets note the directories it creates/targets first:

Extensions: Directory for generated Magic EF extension files.
MetaData: Directory for generated Magic EF metadata class files.
Concrete: Directory for generated Magic EF concrete repository files.
Interfaces: Directory for generated Magic EF interface files.
DbHelpers: Directory for generated Magic EF helper files.
DbModels: Directory for where the dotnet scaffolding places the read only versions of the created models.

We'll go more into detail about why these are separated, what each does, how to use, and more.

Extensions & Metadata

When dotnet scaffolding creates a model, we should never touch that file at all! Instead Magic EF detects and will build an extension and metadata class automatically for you so that you can work with the model safely. Lets go over an example of what is generated in the Magic EF protocol.

Scaffold Model (Read Only)

We'll make an example where we created a table in the database called, "dbo.Employee" and after running the dotnet scaffold, we'll have the following model generated.

// Auto-generated by dotnet scaffolding
// File: .\DbModels\Employee.cs
public partial class Employee
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DateOfJoining { get; set; }
}

Extension Class

The partial extension class allows you to add custom properties or methods while ensuring that the scaffold model remains untouched. You can use [NotMapped] for additional properties that shouldn’t be persisted to the database.

// Custom extension class for Employee
// File: .\Extensions\EmployeeExtension.cs
using System.ComponentModel.DataAnnotations.Schema;

[MetadataType(typeof(EmployeeMetaData))]
public partial class Employee
{
    // Example of an additional property not mapped to the database
    [NotMapped]
    public string FullNameWithDate => $"{Name} (Joined: {DateOfJoining.ToShortDateString()})";

    // Example of a method related to the Employee
    public int GetYearsSinceJoining()
    {
        return (DateTime.Now.Year - DateOfJoining.Year);
    }
}

But I want you to notice the MetadataType attribute added to the automatically created extension class. This will actually connect to the generated metadata class!

Metadata Class

The metadata class is used for data annotations to add validation or display rules. These attributes are applied indirectly to the scaffolded class using the [MetadataType] attribute.

// Metadata class for Employee
// File: .\MetaData\EmployeeMetaData.cs
using System.ComponentModel.DataAnnotations;

public class EmployeeMetaData
{
    [Required(ErrorMessage = "Name is required.")]
    [StringLength(100, ErrorMessage = "Name cannot exceed 100 characters.")]
    public string Name { get; set; }

    [Display(Name = "Date of Joining")]
    public DateTime DateOfJoining { get; set; }
}

Why This Approach Is Powerful

Scaffold Model: Ensures your database mappings stay intact and are auto-generated from the database schema.
Extension Class: Allows you to add custom logic, calculated properties, or any functionality without touching the scaffolded code.
Metadata Class: Adds validation and UI annotations to the model in a clean and maintainable way.

DbContext & Repositories

The next powerful aspect of utilizing Magic EF is how it automatically connects and creates classes for centralized, easy to use, concrete LINQ to SQL extensions and helpers. I’ve not yet mentioned that when running --scaffoldProtocol it additionally generates interface and concrete classes. This was unmentioned until now because it ties in with the built-in database helper repository classes generated dynamically around your project and setup. Let’s dive further into what’s generated, but first we must discuss DbContext.

DbContext

Magic EF suggests and automates within the provided script the creation of the “ReadOnlyDbContext.cs” which is never to be touched. And the command that’s baked into the script and was previously discussed, “--removeOnConfiguring” will remove the “OnConfiguring” for you. As the protocol during the initial setup, you provide the DbContext name you desire. Lets say I wanted to simply name my DbContext, “MyDbContext”. Well then during the setup with Magic EF it will generate the following:

using Microsoft.EntityFrameworkCore;

namespace ExampleDataAccess
{
    public partial class MyDbContext : ReadOnlyDbContext
    {
        public MyDbContext()
        {
        }

        public MyDbContext(DbContextOptions<ReadOnlyDbContext> options)
            : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseSqlServer(GetConnectionString());

        public string GetConnectionString()
        {
            // Write your logic to return the connection string
            return Environment.GetEnvironmentVariable("DbString");
        }
    }
}

As you can see, Magic EF generates a class based on your specified name and detects and propertly places the namespace on your behalf. It moves the OnConfiguring into a new location for your fine control. And you can utilize the simple provided "GetConnectionString()" to place your logic to grab the database connection string. Whether it's through an environment variable, Azure key vault, or anything else. You're the developer. And you're supposed to have the power to make this decision! And now you have your custom DbContext class which is utilized by Magic EF dynamically and is meant for you to control as you wish.

Repository Helpers

But Magic EF takes this protocol even further. Baked into the commands and during the initial setup. Magic EF dynamically detects class names, namespaces, scaffolded content, and more.

Baked into Magic EF protocol for scaffold repository use and connections is the following one time generated helper files:

DbHelpers: Dynamically generated helper class with a helper method "MyDbContext GetMyDbContext()" which makes it easy to call the DbContext for manual use if you don't want to use the more refined Magic EF generated methods.
I/ReadOnlyRepository: Repository use and control methods that dynamically attach to your scaffolded models.
- TEntity GetById(object id);
- IQueryable<TEntity> GetAll(MyDbContext? _ContextOverride = null);
- IQueryable<TEntity> GetAllWithContext(out MyDbContext context);
- IQueryable<TEntity> GetAllNoTracking(MyDbContext? _ContextOverride = null);
- IQueryable<TEntity> GetAllNoTrackingWithContext(out MyDbContext context);
I/Repository: Automatically connected repository methods to easily add, update, and delete items.
- void Add(TEntity entity);
- void AddRange(IEnumerable<TEntity> entities);
- void Update(TEntity entity)
- void UpdateRange(IEnumerable<TEntity> entities);
- void Delete(TEntity entity);
- void DeleteRange(IEnumerable<TEntity> entities);
LazyLoad Extension: A powerful lazy load extension to load connected virtual variables connected to a model after context is already lost.

Concrete & Interfaces

Now this is the final piece of the puzzle, I promise! I know it’s a lot, but this will make everything connect. As mentioned before, there’s automatically generated Interface files and Concrete files that extend your scaffolded models functionality and connects the repository to everything! Remember our example of the scaffold files that occurred for the “Employee” table? What’s also created is the following:

Interface Class

namespace ExampleDataAccess.Interfaces
{
    public interface IEmployeeRepository : IRepository<Employee>
    {
    }
}

Concrete Repository Class

using ExampleDataAccess.Interfaces;

namespace ExampleDataAccess
{
    public class EmployeeRepository : RepositoryBase<Employee>, IEmployeeRepository
    {
        public Employee GetById(int Id)
        {
            // Call the default implementation with the parameters as a dictionary
            return GetById((object)new Dictionary<string, object> { { "Id", Id } });
        }

        public Employee GetById(object id)
        {
            var idDict = id as IDictionary<string, object>;
            if (idDict == null)
            {
                throw new ArgumentException("id must be a dictionary with string keys");
            }

            if (!idDict.TryGetValue("Id", out object tempId))
            {
                throw new ArgumentException("id must contain key 'Id'");
            }

            if (!(tempId is int))
            {
                throw new ArgumentException($"id['Id'] must be of type int");
            }

            var IdValue = (int)tempId;

            // Fetch the record from the database
            return _dbSet.FirstOrDefault(c => c.Id == IdValue);
        }
    }
}

Now what's going on here?! Magic EF was able to detect the primary key of the related scaffolded model. It works for multi key tables as well. And due to the requirement of this being so dynamic, we pass in an object, but an override of GetById is also generated to enforce developer strict type use. Creating an automatically generated method for GetById.

But the concrete file that’s generated connects the interface and the Repository base as well for you. This is how the repository code is connected automatically to your scaffolded models. Allowing you to write override methods to the Add, Delete, Update, and others. It’s a great place to write custom LINQ to SQL methods as well related to the database table. Which centralizes logic, reports, commonly used calls, and can often be a great place to override things like the Add to create logic like triggers, but more performant!

Magic EF is more than just string editing. It’s genuinely digesting the changes, automating the creation of necessary files, and automatically connecting your database to your codebase with extreme ease.

The protocol provides you with the ability to extend and edit your models to your desire with extreme control. It resolves commonplace errors during the scaffolding process. It only adds, never overwriting any of your extensions, concrete, or changes to helpers. It’s a pairing with dotnet scaffolding that always should have been.

The Power Of Protocol & Automation

Now the fun part! With an understanding of what's going on behind the scenes, hopefully you can appreciate the magic of how easy the process of utilizing the scaffolding with LINQ to SQL. In our example where we just ran an SQL command to create the Employee table in development. You could run the dotnet scaffold and Magic EF commands manually, or just utilize the provided "Scaffold_Script_Example.ps1" in the GIT repository.

You'll only need to download the script and change the first 4 variables to your specifications. Then, whether it's your first time running the script or using it for the millionth time to scaffold again the changes. After it generates all the files for you. You can immediately begin utilizing the scaffolded content without any manual intervention on your part!

With our newly scaffolded Employee model and Magic EF protocol. In our codebase, it's immediately ready to utilize in various ways:

The "GetAll" is returning the IQuery<Employee> on your behalf. And immediately gain the ability to write clauses and query.

var query = new DetailedAuditRepository().GetAll() // easy context creation
    .Where(x => x.DateOfJoining.Date <= DateTime.UtcNow.AddDays(-30))
    .ToList(); // Force the query to run here and place the rest in memory

Easy context passing for joins.

// Easy context passing for joins
var data = new EntityARepository().GetAllWithContext(out var sharedContext)
    .Where(a => a.IsActive)
    .Join(new EntityBRepository().GetAll(sharedContext),
          a => a.ForeignKeyId,
          b => b.Id,
          (a, b) => new { EntityA = a, EntityB = b })
    .ToList();

Easy use & re-use. Easy updates, add, delete, and more! It's so easy!

var _repository = new EmployeeRepository();

var employee = _repository.GetById(3);
employee.DateOfJoining = DateTime.UtcNow;

_repository.Update(employee);

Lazy load virtual properties after context is already lost!

var entity = new EntityRepository().GetById(1);
var relatedData = entity.LazyLoad(x => x.RelatedEntity);

There is a lot more capabilities as well, but Magic EF not only builds an effective protocol that resolves database first issues, but to utilize, create, and work with scaffolding has never been easier in the development process.

The Vision Behind Magic EF Scaffolding

Magic EF Scaffolding was designed to address the long-standing pain points of database-first development, especially in multi-environment and pipeline scenarios. Traditional challenges, such as ambiguous index errors, rigid scaffolding processes, and lack of extensibility, have deterred developers from embracing database-first strategies. Magic EF flips the narrative, automating these processes while enforcing best practices, enabling developers to scaffold, enhance, and deploy with confidence and control.

Key Features and Innovations

Here’s what sets Magic EF apart and why it’s a must-have for any database-first developer:

Automation and Extensibility: Magic EF doesn’t just scaffold models; it creates a protocol for extensibility. It organizes files into concrete, metadata, and extension classes while generating interfaces and helpers.
Pipeline-Ready: Magic EF resolves common pipeline-breaking issues, such as ambiguous indexes and improper configurations, ensuring smooth deployment across environments.
Separation of Concerns: Scaffolded code is separated into read-only and customizable components, ensuring safety and clarity in codebase management.
Robust Repository Pattern: Auto-generated repositories provide built-in methods for CRUD operations, including LINQ-to-SQL capabilities and error handling.
Seamless Environment Management: Scaffolding dynamically adjusts to different environments, making database-first development scalable and reliable.

Getting Started and Learning More

Ready to dive in? Check out these resources to get started with Magic EF Scaffolding:

GitHub Repository: Comprehensive documentation, setup instructions, and examples.

How to Setup Video Guide - Step-by-step instructions for setting up Magic EF.

▶ Watch Video

How to Setup Video Guide - Learn about its capabilities and see it in action.

▶ Watch Video

Database First With Code-First Migration Strategies

Hopefully I’ve started or already have convinced you that Magic EF resolves and enhances database first to the point that I prefer it 100% of the time over code first! I develop faster and follow better practices with Magic EF database first protocol than I ever did/could with code first. But we can’t poo poo in my opinion that the migration aspect of code first is superior.

I’ve heard counter points as to why you can have a better migration system with database first, but I respectfully have always disagreed. The code first strategy to bring changes between environments has always been cleaner, more reliable, less prone to manual error, and vastly easier to work with in a pipeline deployment process. What wasn’t discussed in this article was the Magic EF protocol which blends database first and code first together in a way that I believe is best. We shouldn’t declare that database first or code first is better than the other. Both have their strengths and weaknesses. So, why not take from each and blend what is best? The idea of database first, doesn’t necessarily mean that we can’t add code first migration strategies to our development process.

This article will not go into detail on the Magic EF migration process and strategy, but it’s critical to mention that it does assist with your database changes during the migration process. You can read more on the database first with code first migration strategies separately at:

Click Here To Read "Database First With Code First Migration Strategies"

Conclusion

Magic EF Scaffolding bridges the gap between database-first development and modern workflows, empowering developers with tools to automate, enhance, and scale their projects. By resolving age-old challenges and providing a robust, extensible framework, Magic EF transforms how developers approach Entity Framework. It’s not just a tool—it’s a revolution. Explore Magic EF today and unlock the true potential of database-first development.