In a previous tutorial, I wrote about the basics of Entity Framework, mainly focusing on the code-first approach. I did mention the database first a little bit, but just the idea behind it. This tutorial is specially written for the database-first approach. I will explain what it is and how you can use it. After this tutorial, you can use it on your own, or not… That’s up to you.
I am not going to explain what Entity Framework is. You can read that in the previous tutorial. And if you have no idea what it is, I strongly advise you to read it before continuing.
Things you should know
For this tutorial you need to know or have the following terms/techniques:
- C# and .NET.
I will be using .NET 7, but this also works with .NET 6. - Visual Studio
The commands and code can be used in Visual Studio 2022, but you could also use Visual Studio Code if you want. - The basic idea behind Entity Framework.
If you have no clue what it is, I refer to the other tutorial mentioned before.
Table Of Contents
The idea behind database first
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.
Preparation: A database
In most tutorials, 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, including the SQL script you can execute:
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.
Scaffolding
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:
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:
If you run this, Entity Framework will start to retrieve the information from the database and create files in the folder Entities.
The 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 you 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.
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 think this is easy to understand. Let’s take a look at the context class, the Movies2Context.
“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 changes to the database
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, as I did at 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.
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 ‘F:\POCs\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:
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.
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 use 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.
