Database First with Entity Framework

Introduction

If you use the code-first approach, you don’t have a database and you create one with migrations. You can create new entities and ‘push’ them to the database. Entity Framework will create the database, if it doesn’t exist yet, and updates the database with changes in the entities.

The advantage of code first is that you can control the whole flow and design of the database with C#. You are not limited to legacy code. Most of the settings, like fields of a table, can be set through code and you can keep it simple.

In some cases, you don’t have the luxury of code first and you are stuck with an existing database. It is not a good idea to delete the database and build it back up with the code-first approach. The reason is simple: Deleting a database will also delete the data. Migrating the data can be a hustle and you can run into errors because there might be a configuration you didn’t see, like an index or a trigger.

In that case, you want Entity Framework to read the existing database and generate the entities. This way, you create the C# classes that represent the entities. If that is done correctly, you can continue adding and changing the entities in C# and use migrations to update the database.

Before We Start

In most articles, I have a simple console application with some code to start from. Usually with some movies. In this case, I don’t have a console application, but I have a database. The tables and columns look like this:

If you want to tag along in this article, here is the SQL you can execute to create the above database:

CREATE Database Movies
GO

USE Movies2

CREATE TABLE [dbo].[Movies]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Title] VARCHAR(100) NULL, 
    [Rating] NCHAR(10) NULL
)

CREATE TABLE [dbo].[Actors]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] VARCHAR(500) NULL, 
    [Age] INT NULL, 
    [Gender] INT NULL
)

CREATE TABLE [dbo].[ActorsMovies]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [ActorId] INT NULL, 
    [MovieId] INT NULL
)

ALTER TABLE ActorsMovies
   ADD CONSTRAINT FK_ActorId_Actors_Id FOREIGN KEY (ActorId)
      REFERENCES Actors (Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

ALTER TABLE ActorsMovies
   ADD CONSTRAINT FK_MovieId_Movies_Id FOREIGN KEY (MovieId)
      REFERENCES Movies (Id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

DECLARE @actorId int
DECLARE @movieId int

INSERT INTO Movies (Title, Rating) VALUES ('Shrek', 5)
SET @movieId = @@IDENTITY
INSERT INTO Actors (Name, Age, Gender) VALUES ('Cameron Diaz', 50, 1)
SET @actorId = @@IDENTITY
INSERT INTO ActorsMovies (ActorId, MovieId) VALUES (@actorId, @movieId);
INSERT INTO Actors (Name, Age, Gender) VALUES ('Mike Myers', 59, 0)
SET @actorId = @@IDENTITY
INSERT INTO ActorsMovies (ActorId, MovieId) VALUES (@actorId, @movieId);

INSERT INTO Movies (Title, Rating) VALUES ('Inceptiopn', 1)
SET @movieId = @@IDENTITY
INSERT INTO Actors (Name, Age, Gender) VALUES ('Leonardo DiCaprio', 48, 0)
SET @actorId = @@IDENTITY
INSERT INTO ActorsMovies (ActorId, MovieId) VALUES (@actorId, @movieId);

INSERT INTO Movies (Title, Rating) VALUES ('The Matrix', 4)
SET @movieId = @@IDENTITY
INSERT INTO Actors (Name, Age, Gender) VALUES ('Keanu Reeves', 58, 0)
SET @actorId = @@IDENTITY
INSERT INTO ActorsMovies (ActorId, MovieId) VALUES (@actorId, @movieId);

INSERT INTO Movies (Title, Rating) VALUES ('The Muppets', 5)
SET @movieId = @@IDENTITY
INSERT INTO Actors (Name, Age, Gender) VALUES ('Kermit the Frog', 67, 0)
SET @actorId = @@IDENTITY
INSERT INTO ActorsMovies (ActorId, MovieId) VALUES (@actorId, @movieId);

This database will act as if it’s a database we need to import with Entity Framework.

I do create a console application, but there is no code needed. Entity Framework will provide the code. I will name this application Movies.ConsoleApp and use .NET 7.

Scaffold-DbContext

Now we have our database ready. Time to get the information from the database and put it into code. Yes, database first means we create C# code from an existing database.

To do this, we need a few things:

• A command line
  I will be using the Package Manager Console in Visual Studio 2022.
• A connection string to the database
• An empty project
  I have the empty console application.
• The NuGet package Microsoft.EntityFrameworkCore.Tools
  This should be installed in your project.

When you are all set, it’s time to get the information from the database and create the C# code. This process is what we call scaffolding.

To scaffold the database, you will need the command Scaffold-DbContext. It has a few parameters, such as the connection string to the database, a provider (which database type are you using), and an output directory where you want Entity Framework to place the C# files. There are other parameters, but we won’t be needing them for now.

The command would look a bit like this:

Scaffold-DbContext [-Connection] [-Provider] [-OutputDir]

To find the Connection string, you don’t have to look far. Just open the database in the SQL Server Object Explorer, expand the database, right-click on the name, select Properties, and find the property Connection string. Simply copy that value and paste it on the [-Connection] parameter of the command.

The Provider is the type of database. I use the SQL Server, so the provider would be Microsoft.EntityFrameworkCore.SqlServer. Another example of a provider is Oracle and it would use Oracle.EntityFrameworkCore namespace as the provider. Don’t forget to install the right provider package with NuGet! Since I will be using SQL Server, I need to install the package Microsoft.EntityFrameworkCore.SqlServer.

The last parameter, OutputDir, is the folder, or directory if you want, where Entity Framework will place the files. In my case, I will be using Entities.

Put this all together and you might end up with something like this:

Scaffold-DbContext "Data Source=(localdb)\MSSQLLocalDB;
Initial Catalog=Movies;Integrated Security=True;Connect Timeout=30;
Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;
Multi Subnet Failover=False" Microsoft.EntityFrameworkCore.SqlServer 
-OutputDir "Entities"

If you run this, Entity Framework will start to retrieve the information from the database and create files in the folder Entities.

Generated Files

As you might have figured out yourself, there are a few files that look familiar. The files Actor, ActorsMovie, and Movie are entities and represent the tables in the database. The file Movies2Context.cs is the context file, which contains a class with inheritance with DbContext.

So, what might have done yourself with the code-first approach Entity Framework has done it for you.

If we open an entity, it doesn’t look really special. Let’s take a look at the Actor entity.

public partial class Actor
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public int? Age { get; set; }

    public int? Gender { get; set; }

    public virtual ICollection<ActorsMovie> ActorsMovies { get; } = new List<ActorsMovie>();
}

It has all the properties we might expect. Except… the Name has a length of 500 in the database. We normally use an attribute for this. So, is this ignored? No, it’s not. I will come back to that later.

The last property, ActorsMovies, is created because of the relationship between ActorsMovies and the Actor, as you can see in the SQL script I gave you earlier. This property will also exist in the Movie entity.

I think this is easy to understand. Let’s take a look at the context class, the Movies2Context.

public partial class Movies2Context : DbContext
{
    public Movies2Context()
    {
    }

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

    public virtual DbSet<Actor> Actors { get; set; }

    public virtual DbSet<ActorsMovie> ActorsMovies { get; set; }

    public virtual DbSet<Movie> Movies { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;
           Initial Catalog=Movies2;Integrated Security=True;
           Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;
           Application Intent=ReadWrite;Multi Subnet Failover=False");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Actor>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Actors__3214EC078BB3A95D");

            entity.Property(e => e.Name)
                .HasMaxLength(500)
                .IsUnicode(false);
        });

        modelBuilder.Entity<ActorsMovie>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__ActorsMo__3214EC072E0DD618");

            entity.HasOne(d => d.Actor).WithMany(p => p.ActorsMovies)
                .HasForeignKey(d => d.ActorId)
                .OnDelete(DeleteBehavior.Cascade)
                .HasConstraintName("FK_ActorId_Actors_Id");

            entity.HasOne(d => d.Movie).WithMany(p => p.ActorsMovies)
                .HasForeignKey(d => d.MovieId)
                .OnDelete(DeleteBehavior.Cascade)
                .HasConstraintName("FK_MovieId_Movies_Id");
        });

        modelBuilder.Entity<Movie>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Movies__3214EC07E0F7DED3");

            entity.Property(e => e.Rating)
                .HasMaxLength(10)
                .IsFixedLength();
            entity.Property(e => e.Title)
                .HasMaxLength(100)
                .IsUnicode(false);
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

“Holy cr… What happened here??” was my first reaction when I used the database-first approach for the very first time. Let’s walk through it. First, it has an empty constructor. No idea why. Then there is a constructor with the DbContextOptions parameter, which sounds a little bit more logical since we need to set the connection string.

Then we see the DbSets, making the connection between the entity and the table names. Nothing special here.

Up next is the override on OnConfiguring. It actually sets the connection as hardcoded. Something we really don’t want. If we have multiple environments, you want to have a generic connection string. Maybe in the appsettings or something.

Then we have the override on the OnModelCreating. Okay, feels like a good one. Here it sets the primary keys and… Tadaaa… the max length of the fields. Entity Framework does most of the configuration on the entities and tables here. Maybe not ideal, but it works.

Making Some (Database) Changes

Alright, we are all set and we have the C# code. How awesome, right?! But, how do we make changes to the database? Like, maybe we want to add a field to the Movies table. Or maybe we want to add a completely new table! With database-first, it works a bit differently.

With the code-first approach, we can create migrations from changes we made in the entities and the DataContext. After that, we can simply send the command Update-Database, and the SQL (or Oracle or … whatever) is being updated.

This doesn’t work for database-first, because the actual database is leading. In this case, we need to make changes in the real database and get the changes from the database, and put them in our C# code.

If you think, like I did the first: “Nah, I am just gonna create a migration and update my database.” Sure, go ahead, but the migration will also contain all the existing tables and fields already in the database; there has never been a migration yet.

Alright, an example. Let’s add the field ReleaseDate to the Movie entity. This is a DateTime. The first step is to add it to the table in the SQL database.

ALTER TABLE [dbo].[Movies]
    ADD [ReleaseDate] DATETIME NULL;

You can execute this SQL statement in your own SQL IDE or Visual Studio.

The console application will still work fine since it has no idea about the change in the table Movies. To make it aware of this, we need to re-scaffold the database. But if you run the command we used earlier, you get an error:

The following file(s) already exist in the directory C:\YOUR_PATH_HERE\Movies.ConsoleApp\Entities: Actor.cs,ActorsMovie.cs,Movie.cs. Use the Force flag to overwrite these files.

It suggests using the Force flag, meaning it will override all the entities and DataContext. Good idea? Well, maybe. You are not supposed to change the scaffolded code anyway. But if you have a big database, it could take a while. Especially when the database is not local.

There is a way to tell Entity Framework which table(s) you want to scaffold. Look at the command below:

Scaffold-DbContext 
    "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Movies;
     Integrated Security=True;Connect Timeout=30;Encrypt=False;
     Trust Server Certificate=False;Application Intent=ReadWrite;
     Multi Subnet Failover=False"
    Microsoft.EntityFrameworkCore.SqlServer 
    -OutputDir "Entities"
    -Tables Movies
    -Force

I added the flag -Tables with the name of the table I want to scaffold. But we still need the force-flag, because otherwise, it will still complain the file Movies.cs already exists. After executing this command, let’s take a look at the Movies.cs.

public partial class Movie
{
    public int Id { get; set; }

    public string? Title { get; set; }

    public string? Rating { get; set; }

    public DateTime? ReleaseDate { get; set; }
}

And there is the ReleaseDate. It’s not really magic, but it is rather cool.

Scaffold in Other Project

If you want to scaffold the database information in another project, a data layer for example, you can the same command as before, but you change the default project in the Package Manager Console. Make sure you make the project with Microsoft.EntityFrameworkCore.Tools as a startup. The project that should get the scaffolded database needs to be referenced in the startup project.

Conclusion

To be honest: I don’t like the database-first approach. Simply because I need a real database all the time. It can also pollute your projects if you are not careful.

But… in some cases, you need this. If you are working with a legacy system (aka old) you might have a database with data and all the configuration. If you want to use any form of Entity Framework, you are bound to the database-first approach. Unless you can convince your team and/or boss to rebuild the database with a code-first approach.

One of the benefits of the database-first approach is that a lot of configuration in the DataContext is created by Entity Framework and you don’t have to figure out how you need to set up all the relations between entities. Something I still can’t figure out how to do it properly.

History

• 15^th February, 2023: Initial version