In this article, we’ll take a look at the EF Core Database-First approach. In this approach, we create our database first. We then model our entities. This approach is useful when we work with an existing database. This is also useful when we migrate from existing applications.

A relational database deals with related data. So, we’ll also take a look at different types of relationships in the database and how we can represent those while modeling our entities.

There are 3 types of relationships in relational database design:

One-to-One – A row in Table A can have only one matching row in Table B, and vice versa.

One-to-Many (or Many-to-One) – A row in Table A can have many matching rows in table B, but a row in table B can have only one matching row in Table A.

Many-to-Many– A row in Table A can have many matching rows in Table B, and vice versa.

We have divided this article into the following sections:

Creating a Database and Tables

As the first step, we are going to create the database and tables.

So for example, let’s create a database to manage books. We are going to create tables for storing information about Books, Authors, Publishers etc. and establish relationships between them. 

This is the complete SQL script for creating database tables and relationships.

After running the script, we can see the tables and relationships created as below:

DB Diagram

Database design explained

Tables:

Author– Stores the information about the authors.

AuthorContactContains the contact information about the authors.

Book– Stores the information about the books.

Publisher– Keeps the information about the publishers.

BookCategory– Keeps the master list of all the categories.

BookAuthors– Represents the mapping between the books and the authors.

Relationships:

Let’s take a look at how we implement the different types of relationships in our database design.

One-to-One(1:1)

In the above design, AuthorandAuthorContact have a 1:1 relationship between them. Each entry in theAuthor table has a corresponding entry in theAuthorContact table. They are related by theAuthorId foreign key.

This type of relationship is not very common. We could also keep the author contact information in theAuthor table. But in certain scenarios, there could be some valid reasons to split out information into different tables like security, performance etc.

One-to-Many(1:N)

In the above design, PublisherandBook have a 1:N relationship between them. A publisher can publish many books, but a book can have only one publisher. They are related by thePublisherId foreign key.

This is the most common type of relationship in any database.

Many-to-Many(M:N)

In the above design, BookandAuthor have an M:N relationships between them. A book can have many authors and at the same time, an author can write many books. They are related by an intermediate tableBookAuthors. This is also called an associative or junction table.

We can translate an M:N relationship to two 1:N relationships, but linked by an intermediary table.

Inserting Test Data

Now that we have created our tables and established relationships between them, let’s insert some test data into them. Let’s use the below DB script to insert data:

After running the above insert script, our database tables will look like this

Tables With Data

Data Modelling – Creating Models and a Context

So, now we have our database tables with data. Let’s model our entities based on those.

As a first step, let’s set up an ASP.NET Core Web API Project. We have explained this in detail in one of our other articles: Creating and configuring a new ASP.NET Core Web API project.

The article linked above covers a lot of additional topics. You may go through the entire article if you want to, but the section linked above is quite enough to follow along with this article.

Following the article linked above, let’s create a new project calledEFCoreDatabaseFirstSample.

Creating Models

Now it’s time to create the EF model based on our existing database.

Go to Tools –> NuGet Package Manager –> Package Manager Console

Run the following command to create the models from the existing database:

The above command will generate the following classes:

Generated Classes

BookStoreContext is the DB context class and other classes are the models.

Now, let’s look at how EF Core represents the relationships.

One-to-One(1:1)

Let’s take a look at the Author class: 

Remember that theAuthor has a 1:1 relationship with theAuthorContact. To represent this we have anAuthorContactproperty in theAuthor class. This is called the Navigation Property.

One-to-Many(1:N)

Let’s take a look at the Publisher & Book classes:

Remember that thePublisherhas a 1:N relationship with theBook.

Here, the Publisher is called the Principal Entity and the Book is called Dependent Entity.

Publisher.PublisherId is the Principal Key and Book.PublisherId is the Foreign Key.

Publisher.Books is the Collection Navigation property.

Book.Publisher is the Reference Navigation property.

Many-to-Many(M:N)

Note: As of now, EF Core does not support many-to-many relationships without using an entity class for representing the join table. However, we can represent it by using an entity class for the join table. We could then map two separate one-to-many relationships.

Let’s take a look at the Book, Author & BookAuthors classes. (Book and Author classes are already shown above):

We can see that both the Book and the Author has a collection navigation propertyBookAuthorsWe have established the M:N relationship between theBook and the Author by these two 1:N relationships.

Creating a Repository

Now that we have successfully created the models and context, let’s implement a simple data repository using the repository pattern. We have explained this pattern in detail in one of our other articles: Implementing the repository pattern. If you get stuck with the code, you can always refer to the mentioned article and to our source code for this article, as well.

Let’s add a new folder under Models and name it Repository. We’ll then create a new interface called IDataRepository:

We will later inject this interface into our controller. Then the API will communicate with the data context using this interface. Of course, we are going to register all the repo services in the Startup class, as you can find out by your self in our source code.

Next, let’s create concrete classes that implement theIDataRepository interface. We’ll add a new folder under Models called DataManager.

Let’s keep things simple and focus on implementing only the required functions.

Querying & Loading Related Data

EF Core uses navigation properties in our model to load related entities. We use three common ORM patterns for loading related data.

When we use eager loading, we load the related data from the database as part of the initial query.

Explicit loading means that we load the related data explicitly from the database at a later time.

Lazy loading is a way of loading the related data from the database when we access the navigation property.

Eager loading

We can use theIncludemethod to specify related data that need to be included in the query results. In the following example, the Authors that are returned in the results will have their  AuthorContacts property auto-populated.

Let’s add a new class AuthorDataManager which implements theIDataRepository in the DataManager folder, and register it in the Startup class.

We’ll then implement the GetAll():

The above code loads all the authors with their contact details at once since we are using eager loading. We shall verify this later when we test it.

Explicit loading

We can explicitly load a navigation property using the DbContext.Entry().

Let’s add a new class BookDataManager which implements the IDataRepository interface and register it in the Startup class as well.

We’ll then implement the Get()method:

The above code is used to get the details of a Book. See how we are explicitly loading the list of BookAuthors and Publisher later. We’ll verify the explicit loading behavior later when we test this functionality.

Lazy loading

The simplest way to use lazy-loading is by installing the Microsoft.EntityFrameworkCore.Proxies package and enabling it with a call to UseLazyLoadingProxies.

This is shown in the below code

EF Core will then enable lazy loading for any navigation property that can be overridden. Only thing is that it must be virtual and on a class that can be inherited from.

For example, in the below Authorclass, the BookAuthors navigation property will be lazy-loaded:

Let’s then disable lazy-loading at a context level. This helps to avoid circular referencing issues:

We’ll enable lazy-loading explicitly when we need to utilize it.

Let’s implement the GetDto() method in the AuthorDataManagerclass:

In the code above, since we are using lazy loading, only the Author entity will be loaded initially. Later the AuthorContact property will be loaded only when we reference it inside the DTO mapper. We’ll verify this behavior later when we test this.

Note: The referenced property can be lazy-loaded only inside the scope of the data context class. Once the context is out of scope, we will no longer have access to those.

Saving Related Data

In this section, we’ll explain how we can Add, Update and Delete related entities.

Add

If we create several new related entities, adding one of them to the context will cause the others to be added too.

For example, in the below code, let’s implement the Add() method in AuthorDataManager.

This will cause both Author and AuthorContact entities to be created:

Update

Now let’s implement the update. The below code implements the Update() method in AuthorDataManager class:

The above code will cause the Author,AuthorContactand BookAuthors entities to be updated. We’ll verify this later when we test this.

Delete

Delete operation can be tricky with related entities. There are three actions EF can take when a parent entity is deleted.

  • The child can be deleted
  • The child’s foreign key values can be set to null
  • The child remains unchanged

We should configure the DeleteBehavior appropriately based on our application logic. In the below example, let’s say when a publisher is deleted, we need the publisher’s book also to be deleted.

First, let’s configure this in the OnModelCreating method in our context:

Now let’s implement the Delete() method in PublisherDataManager class:

The above code will delete the Publisherand any related Book entities. We’ll verify this later when we test this functionality.

Creating the API Controller

Now that our DataManager is all set, let’s create the API Controller and create the endpoints for handling CRUD operations. This is described in detail in one of our other articles: Creating a .NET Core Web API Controller

Following the above article, let’s create the AuthorsController,BooksController and PublishersController class in the Controllers folder as shown below.

For keeping things simple and focused, we’ll implement only those endpoints required to understand the concepts we discuss in this article.

Let’s implement the GetAll,Get,Post and Put method in the AuthorsControllerclass:

Then let’s implement the Get() method in the BooksController:

Finally, let’s implement the Delete() method in the PublisherController:

Testing the API

Now we’ll test the controller methods using Postman. We’ll also verify the results in the database. Later, we’ll inspect the actual SQL queries executed in the database using the SQL Server Profiler.

Loading the Data

First, let’s test the GetAll endpoint of Authors:

GET All Authors

Remember that we used eager loading for implementing this functionality. If we look at the Profiler, we can see that the query fetches data by joining Author and AuthorContact tables:

GetAll Profiler

Next, let’s test the Get endpoint of the Book:

GET Book

Remember that we used explicit loading to implement this functionality. Here note that only those properties that we chose to load explicitly have data. Other related properties are empty.

In the Profiler, we can see that initially, an SQL query fetches data from the Book table. Later, queries are generated to fetch data from other tables when we explicitly load data from other entities.

GET Book Profiler

Now, let’s test the Get endpoint of Author:

Get Author

Remember that we used lazy loading to implement this functionality. In the Profiler, we can see that initially only data from the Author table is loaded. Later, when we refer the AuthorContact entity inside the DTO Mapper class, another query loads data from the AuthorContact table:

Get_Author_Profiler

Updating Data

Now, let’s test the Add endpoint of Author:

POST_Author

We can see that two INSERT queries are generated to insert data into tables Author and AuthorContact:

INSERT_Profiler

We can verify that our Add endpoint inserts data in both tables:

DB_Result_After_POST

Now let’s test the Update endpoint of Authors.

We’ll insert some data into Publisher,BookCategory and Book table:

Let’s modify the Author we just inserted. Let’s edit the ContactNumber and map the newly added Books to this author:

POST_Author

In the Profiler, we can see an UPDATE query for the AuthorContact table and two INSERT queries for the BookAuthors table:

UPDATE_Profiler

Let’s verify the results in the database:

DB_Result_After_PUT

Finally, let’s test the Delete endpoint of Publisher.

We’ll insert a test publisher and two related books:

Now let’s test the Delete endpoint.

DELETE_Publisher

In the Profiler, we can see that the related data is first removed from theBook table. Then the publisher record is deleted from thePublisher table.

DELETE_Profiler

Let’s verify the changes in the database.

DB_Result_After_DELETE

Conclusion

In this article, we have covered the following topics.

  • EF Core Database-First approach and when to use it.
  • Different types of relationships in a database.
  • Creating a database and tables with relationships.
  • Modeling the entities with relationships.
  • Loading and saving related data using the repository pattern.
  • Different patterns for loading related data.
  • Creating API endpoints for operating on related data.
  • Testing the endpoints and inspecting the generated database queries.

Hope you enjoyed the article. Happy programming!

If you have enjoyed reading this article and if you would like to receive the notifications about the freshly published .NET Core content we encourage you to subscribe to our blog.