Entity Framework Fundamentals

Introduction

Developers, including yours truly, have been struggling with mapping data from a data source and code objects. But then, ORM was introduced. ORM stands for object-relational mapping and enables us to work with domain objects and properties. The mapping struggle was over. I remember using Automapper and nHibernate a lot for my code. But then, Microsoft released Entity Framework in 2008.

As usual, the newly released framework - which is built on ADO.NET - wasn't widely accepted. It had a lot of bugs and didn't do what was expected. In 2010, Microsoft released a new version, which was a bit better. They kept building on it and now it's one of the most common ORMs when you use Microsoft programming languages (C#, VB, etc). The current version is Entity Framework Core 7.0.

As I said before, Entity Framework is built on ADO.NET, but it has additional options. The mapping, for instance, is a very helpful feature. Many aspects (especially errors) can be related to ADO.NET. It's a good practice to know a little bit about ADO so you know what is under the hood of Entity Framework.

Entities are a big thing when you use Entity Framework. Not because it's in the name of the framework, but how it works. An entity is usually an object in code that represents an entity in the data source. I like to say it represents a table in the database. The properties of the entity are used in the database as columns and settings. For example: If you have a movie entity, it has an Id (int), Title (string), and maybe a Rating (int). Entity Framework can translate these to an MSSQL table with a column for Id (int), a Title (varchar(500)), and a Rating (int).

But Entity Framework can also map the data from a database table back to an object (the entity) in the code, and the other way around. I will show you how this works in this tutorial Entity Framework for dummies.

I will use the abbreviation EF which stands for Entity Framework in the rest of this blog.

Code First Versus Database First

EF has two ways of managing your database. In this tutorial, I will explain only one of them; code first. The other one is the database first. There is a big difference between them, but code first is the most used. But before we dive in, I want to explain both approaches.

Database first is used when there is already a database present and the database will not be managed by code. Code first is used when there is no current database, but you want to create one.

I like code first much more because I can write entities (these are basically classes with properties) and let EF update the database accordingly. It's just C# and I don't have to worry about the database much. I can create a class, tell EF it's an entity, update the database, and all is done!

Database first is the other way around. You let the database 'decide' what kind of entities you get. You create the database first and create your code accordingly.

As said, this tutorial is about code-first.

Before We Begin...

Before we start with Entity Framework, I want to start with a basic console application that has no Entity Framework or database. I've created an application that you can download from GitHub:

• https://github.com/KensLearningCurve/EntityFrameworkForDummies

A quick walk-through of this application. It's not really a high-end application—just a simple console application with a basic menu structure. If you start the application, you can see all the movies, see the details of the movies, and you can create a movie.

The program.cs isn't really special. It's just a representation of the movies. An actual front-end file. We are not going to change anything to the program.cs. The file that we are going to work with is MovieService.cs.

internal class MovieService
{
    private readonly List<movie> _movies = new()
    {
        new Movie{ Id = 1, Rating = 10, Title = "Shrek"},
        new Movie{ Id = 2, Rating = 2, Title = "The Green Latern"},
        new Movie{ Id = 3, Rating = 7, Title = "The Matrix"},
        new Movie{ Id = 4, Rating = 4, Title = "Inception"},
        new Movie{ Id = 5, Rating = 8, Title = "The Avengers"},
    };

    public IEnumerable<movie> GetAll()
    {
        return _movies.OrderBy(x => x.Title);
    }

    public Movie? Get(int id)
    {
        return _movies?.SingleOrDefault(x => x.Id == id);
    }

    public void Insert(Movie movie)
    {
        if (string.IsNullOrEmpty(movie.Title))
            throw new Exception("Title is required.");

        movie.Id = _movies.Max(x => x.Id) + 1;
        _movies.Add(movie);
    }

    public void Delete(int id)
    {
        Movie? toDelete = Get(id);

        if (toDelete != null)
            _movies.Remove(toDelete);
    }
}

The MovieService.cs contains all the logic for movies. It works closely with the object Movie.cs, which has all the properties of a movie. This class allows us to get all the movies, get a single movie by ID, create a movie, and delete a movie. A typical service class.

At the top, you see a private variable, _movies. This is a list of all the movies. These movies are hardcoded and you won't find them in any data source. At the end of this tutorial, these movies are in an MSSQL database and the public methods will read and write the data to that same database.

Let's begin!

Context

With Entity Framework, it all starts with a context. It associates entities and relationships with an actual database. Entity Framework comes with DbContext, which is the context that we will be using in our code. With the DbContext, you can read and write data from and to a database, track changes made to objects, and much more. I won't be covering all topics, just the basics.

The DbContext Class

The first thing we need to do is create a context class. This is just a normal C# class with an inheritance from DbContext. That DbContext is a class of Entity Framework. You will need to install a package before you can use it: Microsoft.EntityFrameworkCore. This package contains all the classes and objects we need.

Let's create a new class and call it 'MovieContext.cs'. Inherit DbContext and install the NuGet package Microsoft.EntityFrameworkCore. The class looks like this:

using Microsoft.EntityFrameworkCore;

namespace EntityFrameworkForDummies.ConsoleApp
{
    internal class MovieContext : DbContext
    {

    }
}

To install the package, you can press Ctrl +. When the cursor is on the DbContext and let Visual Studio install the latest version, or you can use the Package Manager, or use the following command line in the Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore -Version 6.0.8

All we have to do now in this class is tell the DbContext which MSSQL server to use. This can be a local, cloud, or another server. EF needs a connection string to know which server to use. I will be using a local database and the connection string looks a bit like this:

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=efdordummies;
Integrated Security=True;Connect Timeout=30;Encrypt=False;
TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

The Data Source is the location of the server. In my case, it's (localdb)\MSSQLLocalDb, which represents the local database that comes with Visual Studio.

The Initial Catalog is the name of the database. Give it a good, meaningful name. With EF, it's not necessary to create the database yourself; EF will do this for you if needed.
These are the two most important settings for now. I will not go into detail about the other options.

EF needs to be aware of the connection string and we can give this to EF in several ways:

1. Via a configuration file (appsettings, app.config, etc.) of the front-end application
2. Directly in the context constructor
3. By overriding the OnConfiguring method of the DbContext

It doesn't really matter which one you choose, but I'm working with option 3. If you use a configuration setting or injection, I would advise using the constructor.

Overriding the OnConfiguring isn't that hard, but getting EF to give the connection string can be a bit tricky.
EF is not only used for MSSQL, but also for other database structures, like Oracle and MySQL. We need to tell EF which database type we want to use by installing the right package. In this case, we want to install the package Microsoft.EntityFrameworkCore.SqlServer. It has all the functionalities to connect to an MSSQL database, read and write, and much more.

Overriding the OnConfiguring and adding the code looks like this:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;
    Initial Catalog=efdordummies;Integrated Security=True;Connect Timeout=30;
    Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;
    MultiSubnetFailover=False");
}

The DbContextOptionsBuilder has a direct link with EF and the settings of EF. By installing Microsoft.EntityFrameworkCore.SqlServer package, we can tell this options builder we want to connect with an (MS)SQL server with the given connection string.

Side note!
Don't hard code a connection string into the context. It's better to store it in a configuration file. If you have different environments (develop, test, production), you don't want to change the connection string each time you move to a different environment. Using configuration files allows you to create the settings per environment and never have to change them again.

The base is now set and ready. Next up is the association with the entities.

The DbSet

To tell Entity Framework what our entities are and how to store them in the database, we use DbSet. It's an Entity Framework property that will get the type of the entity and the name of the - in this case - table name. We want to store movie information in our database and we have a model for that: Movie. We can (re)use that as our entity. It looks like this:

internal class MovieContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;
        Initial Catalog=efdordummies;Integrated Security=True;Connect Timeout=30;
        Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;
        MultiSubnetFailover=False");
    }
}

I've declared a new property Movies. This will also become the table name in the database. The type of Movies is DbSet<Movie>, meaning I want to attach the object Movie as an entity to the context. How this information will be used in the database is a topic for later in this tutorial.

Migrations

Having a context class with a configured connection to a database and an entity is nice and all, but we still need to create the actual database including the table (Movies). In the past, we had to create the database by hand, using a tool like SQL Management Studio. But EF is capable to do it for us. Since we are using the code-first construction, we can create a script that will update our database with changes we made to the entities. We call these scripts migrations.

You create a migration with a command. You can execute this command with the Package Management Console, Developer PowerShell, or with a command prompt. To create a migration, you simply use the following command:

add-migration Initial

The first part add-migration will create a migration with the name of the second part; initial. Except, when you execute this, you will get an error:

add-migration : The term 'add-migration' is not recognized as the name of a cmdlet, 
                function, script file, or operable program. 
                Check the spelling of the name, or if a path was included, 
                verify that the path is correct and try again.
At line:1 char:1
+ add-migration Initial
+ ~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (add-migration:String) [], 
                              CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

No need to panic! We just need to install another package. Did I mention EF is mostly about installing packages? This time, we need to install Microsoft.EntityFrameworkCore.Tools. If you look at the description, you'll notice it will install a few command line commands:

After installing this package, we can execute the add-migration again. A few things happen in this order:

• Visual Studio will build the solution, checking if there are errors.
• EF will check if there is a snapshot of the database (which will be discussed later).
• EF will create a migration file, containing C# that will be used to update the database.

If you look carefully, you will notice I didn't say the database will be actually updated. This add-migration is just a tool to create the changes, not execute them.

But how does EF know what the changes are? By using a snapshot. After the add-migration is finished, a new folder has been created in your project. The folder Migrations will hold all the migrations you have created. These files can also be pushed to a GIT repository. You will see two files:

• A file with a timestamp and the name of the migration, initial in our case.
• A snapshot file, MovieContextModelSnapshot.cs in our case.

If you open the snapshot file, you will see a representation of the movies table and the properties of that table. The more entities you use, the larger this file will become. In the end, the snapshot is the complete database based on your entities, not the database on the server.

The other file, the migration itself, just has a representation of the changes you made to your entities or structure. This file contains only the creation of Movies. It looks different than the snapshot.

namespace EntityFrameworkForDummies.ConsoleApp.Migrations
{
    /// <inheritdoc />
    public partial class Initial : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Movies",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Title = table.Column<string>
                            (type: "nvarchar(max)", nullable: false),
                    Rating = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Movies", x => x.Id);
                });
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Movies");
        }
    }
}

There are two methods: Up and Down. The Up-method is when you want to update your database. The contents of this method will be translated into an SQL script and executed on the SQL server. You can see the migrationBuilder will create a table (CreateTable) with a name (Movies) and columns (Id, Title, Rating). Here, you also see the types of those columns. EF has translated the string of C# into a nvarchar(max).

The Down-method will be executed if you want to reverse the migration. In this case, it's simple: Just delete the table Movies and the migration is made undone.

It's advisable not to change these methods. Simply because they are autogenerated and represent your entity Movie. Want to change something? Better create a new migration.

That's cool and all, but we still don't have a database. Correct! But the only thing you need to do is execute the following command:

update-database

This command will check which migrations haven't been executed yet. By using the snapshot? Nope, by using the __EFMigrationsHistory table. But this table isn't available the first time. This is a hint for EF to create the database, create the __EFMigrationsHistory, and execute all the migrations in order of creation date.

This __EFMigrationsHistory keeps track of which migrations have been executed on the database. If you view the data in this table, you will see just the initial migration. If you would create a new migration, the initial migration will not be executed again, but just the new migration. If you delete all the records of the migrations history (don't!) and update your database again, it will execute all the migrations again, resulting in errors because Movies table already exists.

Apart from history information, you also see the Movies-table. The properties (columns) are like those we used in the entity.

Another great feature of migrations is that you can exchange them with other developers in the team. Most of us use Git(hub) to store code and other developers can grab that code too. Migrations are just files you can push to git, let other developers fetch those files, and update their database with the migrations you created. This way, you all have the same database structure.

Making Changes

We have our database and Entity Framework. All we have to do now is change the code of the service. We need to do a few things in the MovieService class:

• Initialize the context class.
• Refactor the methods.
• Remove the list of hardcoded movies.

We leave the Program.cs as is because it relies on the information from the MovieService.

Using the datacontext

Maybe this is the easiest step. Let's create a private property that holds an initialized MovieContext. Then we create a constructor that will set this property:

private MovieContext _movieContext { get; set; }

public MovieService()
{
    _movieContext = new MovieContext();
}

Now we can use the _movieContext, with initialized MovieContext, throughout the class.

Refactoring the Methods

This one is a little bit trickier. We need to make the current methods use MovieContext. Some methods are really easy to change, like the GetAll() and the Get(int id). First the GetAll():

public IEnumerable<Movie> GetAll()
{
    return _movieContext.Movies.OrderBy(x => x.Title);
}

public Movie? Get(int id)
{
    return _movieContext.Movies?.SingleOrDefault(x => x.Id == id);
}

I replace the _movies with the _movieContext.Movies. That's it! EF will, when this method is called at runtime, change this code to a TSQL query, which will look a bit like this: SELECT Id, Title, Rating FROM Movies.

The second method, Get(int id), has the same change: _movies replaced by _movieContext. EF will create a query like this: Select Id, Title, Rating FROM Movies WHERE Id = #Id#.

Let's fix the Insert method next. In the 'old' method, I had to figure out the id myself. But EF will set the Id since it's a key and has auto-increment. We can remove this line of code. Then, we can replace the _movies with _moviesContext.Movies. All done? Nope. We need to tell EF to commit to the changes.

Deleting, updating, and creating data in a database through EF doesn't work out of the box. We need to save the changes to the database with SaveChanges(). This method lives on the context class, which inherits DbContext.

SaveChanges() will create a transaction and executes all updates, creates, and deletes currently not executed in the context (MoviesContext in our case). You can create more movies to save to the database and execute the SaveChanges as soon as you are done.

SaveChanges() returns an int, but this is not the Id of an entity. it's the number of rows affected after the transaction has been successfully executed.

If we put it all together, our Insert-method looks like this:

public void Insert(Movie movie)
{
    if (string.IsNullOrEmpty(movie.Title))
        throw new Exception("Title is required.");

    _movieContext.Movies.Add(movie);
    _movieContext.SaveChanges();
}

The Delete(int id) function isn't that hard anymore. We need to retrieve the entity from the database by calling the Get(int id) (check) and removing the entity from the database.

Remember I said the context tracks entities? To retrieve an entity from the database, EF has added tracking. We can then delete the entity from the database. Don't forget to call SaveChanges()!

public void Delete(int id)
{
    Movie? toDelete = Get(id);

    if (toDelete != null)
    {
        _movieContext.Movies.Remove(toDelete);
        _movieContext.SaveChanges();
    }
}

Changing the Entity Properties

Everyone makes mistakes, and that's alright. Mistakes are here to be fixed. In this case, I totally forgot to add the plot to the movie, which is important too. The plot tells a bit about the movie and pretty important if you want to decide if the movie is watchable for you.

Let's add the plot to the Movie object. It's a string, if you are wondering.

internal class Movie
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int Rating { get; set; }
    public string Plit { get; set; }
}

Now we need to add the Plot to the table Movies in the database. All we have to do is create a new migration and update the database:

> add-migration AddingPlot
> update-database

And the result:

Ow darn... I made a typo (...). I created Plit instead of Plot. As I said: Everyone makes mistakes, and that's alright. Let's fix this error.

There are two things I can do:

1. I can rename Plit to Plot, create a new migration and, update the database.
2. I can roll back the previous migration, rename Plit to Plot, create a new migration, and update the database.

I think most people would go for option 1. The downside is that you have two migrations that are basically the same and will both be executed when you update a database. If you make more mistakes the number of migrations will grow rapidly.

The smarter thing to do would be to go for option 2. Roll back the migration, delete it, and try again. To do this, you update the database to the latest correct migration first. That's the initial migration for our example:

update-database Initial

This will result in the following log:

PM> update-database Initial
Build started...
Build succeeded.
Reverting migration '20221130023924_AddingPlot'.
Done.

"Reverting migration" … interesting. Entity Framework has now executed the Down method of the Plot migration. If you look at your database, you will notice that the column Plit is gone.

All that is left now is fixing the typo in the Movie object, creating a new migration, and updating the database as shown in the previous chapter. This gives us clean migrations and not fixes on fixes.

Conclusion

Entity Framework is maybe the most important part when creating applications in C# that require some form of database. It can help us (developers) keeping us away from database structures and applications like SQL Studio Management.

It's not hard to implement Entity Framework and refactoring an existing application to use Entity Framework is a piece of cake. Even when the application has a working database, just use the database-first method; let's Entity Framework import the database to the solution. But creating a database from scratch with the code-first approach is easier as you have more control of what is happening in your code and database.

The use of migrations - and especially the migration files - are a great way to manage your database. You can easily reverse errors and share the files across your team via Git or other repository system. This way you have a clean and consistent database all the time.

This article just covers the basics. But there is much more to Entity Framework. I hope you get some idea of how to start.

History

• 2^nd December, 2022: Initial version