Using EF Core to Connect your Project to a Database

Installing necessary packages for EF Core

You need to install four NuGet Packages to work with Microsoft SQL Server:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Design
• Microsoft.EntityFrameworkCore.Tools

Working with EF Core

EF Core can change the schema of a database so that it will store whatever information you would like it to. This is a feature of EF known as a migration and is what we are trying to execute.

To get help about EF commands type dotnet ef into the terminal.

In order to use EF Core you have to derive entity classes from a class named DbContext and then add properties to that class with the information you want to store in the database.

You generally want to add the DbContext class inside of the Data section of your application.

It should look something like this:

using Microsoft.EntityFrameworkCore;

namespace <YourAppName>.Data
{
	public class <YourEntityType>DbContext : DbContext
	{
		public DbSet<YourModelType> NameOfModelType { get; set; }
	}
}

Connecting to a Database

List of databases supported by EF Core: https://docs.microsoft.com/en-us/ef/core/providers/

If you are not on Windows you can still run MicrosoftSqlServer by using a Docker container.

Store the connection string to the database in the project’s appsettings.json file like so:

"ConnectionStrings": {
	"<DatabaseName>Db": "Data Source=(localdb)\\MSSQLLocalDB;
			Initial Catalog=<DatabaseName>;
			Integrated Security=True;"
}

There are three parts to a typical connection string:

• Data Source=(localdb)\\MSSQLLocalDB; == the database server/SQL instance to connect to
• Initial Catalog=<DatabaseName>; == the specific database to connect to on that server (there could be multiple)
• Integrated Security=True; == the credentials passed to authorize the connection _ Which, in the case of Integrated Security=True, just means to use your Windows’ user identity to connect to the database _ (For most other database connection strings you will generally pass a username and a password)

Connecting the Database to the DbContext class

How does the connection string reach the DbContext class we first created?

Through the ConfigureServices method in the Startup.cs file for the application.

services.AddDbContextPool<<YourClass>DbContext>(options =>
{
	options.UseSqlServer(Configuration.GetConnectionString("<DatabaseConnectionStringName>Db"));
});

There are two methods that will help us connect to a DbContext: services.AddDbContext( ) and services.AddDbContextPool( )

services.AddDbContextPool( ) will attempt to pool DbContext’s that have already been created so as to reuse the connections in a more efficient manner while the program is alive, so that is the one to use unless you explicitly want the DbContext connection to be short-lived

You then pass in the DbContext class type we first created like so: services.AddDbContextPool<<YourClass>DbContext>()

However, we also need to pass some options in to the method using a lambda expression that tells the DbContextPool to use MicrosoftSqlServer, and then pass the name of the connection string from the appsettings.json file in to the Configuration.GetConnectionString( ) method.

Next, there is one more thing you need to put in place so that the framework will have the connection string and the options the DbContext class needs to know about to work with the database.

This is accomplished by using a constructor in the DbContext entity like so:

public <YourEntityType>DbContext(DbContextOptions<<YourEntityType>DbContext> options) : base(options)
	{
	}

Configuring the Migration

If you have the data access layer of your application in a seperate class library, something like <YourProjectName>.Data, then it needs some way to connect or reference the Startup.cs file in the main part of the application.

One way of getting around this modularization of your project is to simply tell EF Core that the data access part of the application is in a seperate folder than the folder that contains the Startup.cs file.

This is accomplished by issuing the following command in a terminal with the -s flag:

dotnet ef migrations add InitialCreate -s ..\<YourProjectDirectory>\<YourProjectName>.csproj

The -s parameter tells EF Core where the startup file is located, and by passing it the name of your .csproj file it will locate the Startup.cs file automatically.

If the command gave you an error, .NET Core 3 removed the dotnet-ef command from being part of the standard .NET Core SDK package so you have to install it explicitly with the following command:

dotnet tool install -g dotnet-ef

Also make sure your startup project has all of the NuGet packages installed as well.

Once you get the EF migration to work, it will create the database for you on the local MSSqlServer. You then want to run the following command to update the database, again specifying where the startup file is:

dotnet ef database update -s ..\<YourProjectDirectory>\<YourProjectName>.csproj

Everytime you make a change to the models or entities of your application you want to run an EF Core migration to reconfigure the database.