Entity Framework Core

Let me introduce myself

I have been a developer since 2002 and I have been in many companies and learned a lot (mostly how not to do things).

Then in 2016, I went on a world trip, traveling 1 1/2 years. In this period I learned so much and the feeling of freedom was overwhelming. But as usual; all good things have to end sometime and I had to return to my home country. In 2018 I was back and did what I always did: Find a job, sit in a team, and sit in silence while I developed software.

In 2021 I changed my profession from developer to IT teacher, mostly based on C# development. I started with a small group of juniors being readied for the big bad work called 'working at a company'. After that I started as a teacher for people with ADHD and autism; one of the best and most rewarding jobs I ever had.

But the traveling was calling again and I started my own website; kenslearningcurve.com where I help people learn and grow in the world of programming. And I love every minute of it.

Now I want to share my knowledge and experiences with everyone around the world.

Welcome to my e-book Entity Framework Core. My first e-book where I will tell you almost everything I know about Entity Framework Core and a little bit extra.

Chapter 1: Before we start

In this chapter, I will explain to you (the reader) what you can expect and how this e-book works. Most people will skip this chapter and dive right in, but I want to invite you to stick around and read what my idea of a book - and learning - is all about.

Practice makes perfect

While most books will tell you everything you should know in perfect scenarios and leave you with a lot of information, I want to challenge you to do as you learn.

That is why I created a start solution with some basic projects and code. This solution is the base of our study and learning. In each chapter, I will give you examples and you can implement those examples in the start solution.

A few chapters end with assignments. You need to do them before you can continue; the next chapter will use those assignments as examples or to continue building on the projects.

At the end of this e-book, you should have a complete application with all functionality using Entity Framework Core to start with. You learned a lot of terms you can use to explore more of this magnificent ORM.

This way you can not only read about something but actually experience it. A way of learning that I think works better and keeps your attention to the information.

Chapter 2: It all starts with a (SQL) database

This chapter is a little bit different than the others. I am not going to talk about C# or Entity Framework Core, but databases and SQL. Some important information if you want to work with databases.

If you know (T)SQL and you think you know the terms query, join, and transactions and know how to create a database in Visual Studio, you can skip this chapter and move to the next. But I will be using a database in the next article that I am making in this one. It's up to you.

In this chapter

In this chapter, you will learn the basics of a database. This isn't about Entity Framework Core yet, but some aspects of SQL would help you understand Entity Framework Core better.

We will look at how you can create a database and how to manage the data inside the database. To store the data inside a database you will need a table. We will also be looking at primary keys, different data types, and auto-increment.

Another big aspect of this chapter is to learn how to create and execute queries. You can retrieve, create, delete, or edit the data inside a database table with queries.

When we manage the basics of creating and executing queries, we will take it up a nudge and combine different data by using joins and combining data from different tables. Joins might cause some ambiguous names, so we will dive right into the aliases after the joins.

To avoid problems and remove or edit data by accident we use transactions. We can test a query before committing it.

At the end of this chapter, we will take a quick look at conditions inside SQL and stored procedures.

We will use the SQL Object Explorer inside Visual Studio to manage all this. A simple, yet powerful, window inside the IDE that does everything we need.

Let me introduce you to the database

Before diving into C# or Entity Framework Core and getting data from a database or sending information back, let's look at what a database is and how it all generally works.

There are four well-known and well-used database systems: MySQL, MSSQL, Oracle, and PostgreSQL. If you know how to work with one it isn't hard to learn to work with the other. Remember that I will be using MSSQL (Microsoft SQL) in my examples.

A database is a structured collection of data. It is organized, managed, and stored in a way we can easily retrieve, change, and send data. They help us centralize data so it can be accessed by different clients. Clients as in native applications, web applications, mobile applications, etc.

A database has a schema or objects. The most important one is tables. A table represents the data by defining columns. Each column has a certain type, just like a C# variable. Those types look like C# data types but are a bit different. The type of the column defines what kind of data can be placed in a cell. A cell is the row and column of the table.

A column can only have one data type. Changing this could cause data loss or errors (hope for the second one if you change it by accident). Here is a representation of how a table inside a database could look:

A table has a name and so do the columns. Here the same rule goes as with C#: Keep them short and simple, but they should tell what the table or column is about. A database can contain more than one table and each table can have several columns and rows.

Another important object to know is a stored procedure. Later you will learn how you can collect and manage data with queries. A stored procedure can be explained as a method in C#; It can do more than one thing with multiple lines of code. The lines of code in a stored procedure are not C#, but it's TSQL. Stored procedures are used to execute complex queries and manage data on a high level and the server.

If you have Visual Studio installed you have a localdb which is a local, simplified version of MSSQL. I will be using this throughout this article. But it is possible to install and/or use a SQL server.

The SQL Server Object Explorer

Had enough theory? Well, I do and I want to dive into Visual Studio. There are many tools to manage a database, which you need to do at some point. In the past, we used SQL Management Studio, but I find it a big tool for the small things I am doing with a database.

Lucky for us Visual Studio comes with some sort of database management window: The SQL Server Object Explorer. A nice little window with a lot of options. Let's start by making it appear in your Visual Studio, as you might not have it… Yet.

To open the SQL Server Object Explorer, open the View menu in Visual Studio and select SQL Server Object Explorer. Or press CTRL + Q, type 'sql server', and double click SQL Server Object Explorer.

If you expand the SQL Server, you will see something like (localdb)\MSQLLocalDB. This is your local MSSQL database you can use for development purposes. It is here where we will create and manage the databases.

Expanding this local database you will see three folders:

Databases

Here you will find your databases when you create them. The System Databases keeps track of all objects on the server. Whatever you do, never delete this one!

Security

Handles everything that is in some way connected to the security of your databases and database server (the localdb).

Server Objects

This folder contains objects associated with the SQL Server instance, not tied to specific databases. Think of backup devices, linked servers, server triggers, endpoints, and audits. They are crucial for administration, security, and network communication at the server level. We will not touch these in this book.

The one you will use the most is Databases.

A database and a table

A new database

Right-click on that folder Databases and select Add New Database. Still with me? Good! Now type a name. If you want to follow this book to the letter, use the same name as me: MovieLibrary. I keep the default database location but feel free to place your database somewhere else.

The database should be created and placed underneath the folder System Databases. If you expand your new database, you will see 8 new folders. You won't need all of them, so I will only explain the important ones:

Tables

Here you will find your tables and the data inside the tables. We will come back to this later.

Views

A view is a combination of multiple tables, generating a new table for you to use. This is read-only and you can access it like a table.

Programmability This one has more folders. But here you will store your stored procedures and functions. Functions are somewhat the same as stored procedures.

Security

The same as the Security folder of the database server, but this one only manages the security of the database. It can override the settings of the database server security.

While you won't use most of them, know it is there and you could use it if you need it. Let's create a table.

Add a table

If you expand the folder Tables, you will see three folders. I will not go into detail since all the minor functionalities of a database server, database, or table will take too much of our time.

Instead, we are going to create a new table. This works almost the same as creating a database: Right-click on the folder Tables and select Add New Table. A somewhat scary editor is opening. It is here where we will define our columns, including the data types of the data.

The above part is how you can create your columns or fields. Ironically enough you create a table in a table. You need to give each field a name, and a data type, and allow NULL as a value (yes/no), and a default value. Name and data type are required, others are optional (Allow Nulls is a boolean, so it's false by default).

The lower part represents the table and the fields you create in the upper part. This is TSQL; you can copy and execute it later.

Let's create our table! I want to store movies here, as I showed you in the image in the chapter Let me introduce you to the database. Fill in the grid/table as follows:

Auto-increment

The Id is still there and for a good reason. Each row in a table can have a unique identifier. This way we can retrieve just that one row, or record, that we want. An ID is usually an int, but it could also be a long (or a bigint in SQL) or a GUID. An ID should be unique and auto-increment, meaning that the value of the ID is always upped when a new record is added automatically. You don't have to set the ID by hand. You won't be able to add or edit the value.

But the auto-increment isn't set by default. If you select the ID and press F4 (or open the properties some other way) you see all the properties for this column. First, you see the general stuff we already set. But if you go lower you will find the Identity Specification.