Entity Framework (EF) Core is an object-relation mapper O/RM for mapping entities with database objects. In addition, EF Core provides easy to use functions which eliminates the need for writing code for accessing any data. .NET EF also provides command line tools to implement code first instead of database first approach.
What is Code First?
With Code First approach you can create entities or models in your code. Various attributes and configurations defines the relationships and keys in the database. Once the models are defined and configured we can migrate them to the database using the entity framework cli tools.
In order to better understand the code first approach its always better to have a practical example. We will create two entities User & Job. User will have a primary key Id and FirstName as string. Job entity will have a primary key Id, Name as string and UserId as foreign key to the User entity.
Prerequisites
Before we start we will need a MySQL server running locally on port 3306. Providing instructions on how to install a MySQL will be out of scope, but there are a lot of tutorials available to help you out. At this moment there is no need to create a new database or tables. Just having a running MySQL server will suffice. We will also need a MySQL client to add some data later on. You can use DBeaver.
Setting Up The Project
To get started install .NET 6.0 SDK if not already installed. Fire the below command to create a new dotnet application.
dotnet new webapi --name dotnet It will create a new .net project with controllers and a few other files. In order to support connection to MySQL we need to add a nuget package. We will also add Microsoft.EntityFrameworkCore which is basically an ORM for connecting to the database. To do so execute the below commands in the newly created dotnet project.
dotnet add package Pomelo.EntityFrameworkCore.MySql --version 6.0.1
dotnet add package Microsoft.EntityFrameworkCore --version 6.0.4
dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.4 Since we no longer require WeatherForecast.cs file remove it. Instead create two other entities in Job.cs & User.cs as below.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace dotnet;
public class User
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string FirstName { get; set; }
}
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace dotnet;
public class Job
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public string Name { get; set; }
public int UserId { get; set; }
[ForeignKey("UserId")]
public virtual User User { get; set; }
} We will also need a DbContext subclass to access these entities. Create a file name MySQLDBContext.cs and add the below content.
using Microsoft.EntityFrameworkCore;
namespace dotnet;
public class MySQLDBContext : DbContext
{
public DbSet<User> User { get; set; }
public DbSet<Job> Job { get; set; }
public MySQLDBContext(DbContextOptions<MySQLDBContext> options) : base(options) { }
} We want to configure .NET to use this DbContext class for O/RM mapping. Navigate to your Program.cs file and replace with below content. Note: Since .NET 6.0, Startup.cs file is removed and instead Program.cs is used for all the configurations.
using dotnet;
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Configuration;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddDbContext<MySQLDBContext>(options =>
{
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
});
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run(); Since we are using a Configuration which fetches the DefaultConnection from the ConnectionStrings we will have to add it to our appsettings file. To achieve that set the contents of appsettings.development.json and appsettings.json files as below.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DefaultConnection": "server=localhost; port=3306; database=super-app; user=root; password=$SuperApp1; Persist Security Info=False; Connect Timeout=300"
}
} Next we will create a GET api which returns a list of Job objects in the database. To do so remove the WeatherForecastController.cs and add a UserController.cs file with below content.
using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace dotnet.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class JobController : Controller
{
private MySQLDBContext _dbContext;
public JobController(MySQLDBContext context)
{
_dbContext = context;
}
[HttpGet]
public IList<Job> Get()
{
return (this._dbContext.Job.Include(x => x.User).ToList());
}
}
} We are all set code-wise. But we still need to setup our database. In order to do so we will create a User and Job table in our super-app database.
.NET EF Tool
.NET’s Entity Framework Core provides a very convenient way to achieve it. First install the dotnet-ef cli tool by executing the below command.
dotnet tool install --global dotnet-ef Once installed we will code first approach and create a migration of our entities which will then be pushed to our database.
dotnet ef migrations add InitialCreate
dotnet ef database update This above two statements once executed will create the database, the tables within and also setup the relationship between the two tables.
Adding Data to MySQL
In order to fetch data from the database we first need to add data in the tables. Install any MySQL client to connect to the database. My personal favourite is DBeaver. Now, you can add data from DBeaver by first adding a connection with details like Host=localhost, Port=3306, User=root & password=$SuperApp1.
Once connected navigate to User table and add a row and save the data. Similarly, navigate to the Job table, add a row and save the data. Our database is all set now. Let’s run our project and view the results.
Putting Everything Together
Go back to your project and run the below command to start our project.
dotnet watch run The above command will start your project and open a swagger link in the browser. Simply, open to the Job api and click Try it out followed by Execute. A nice response with a list of jobs will be displayed. Since we have just added one Job only one Job is returned as seen below.
[
{
"id": 1,
"name": "Tutor",
"userId": 1,
"user": {
"id": 1,
"firstName": "John Doe"
}
}
] Conclusion
To conclude entity framework core is one of the best O/RM I have encountered and works very well with a lot of different databases. Setting it up is as easy as firing a few commands. The migrations from entities to database is super smooth.
Hope you have enjoyed the content. Please find some of my other content that might interest you.