.NET Core 3.1 WebAPI with Entity Framework Core

.NET Core 3.1 WebAPI with Entity Framework Core

Today I’m going to show you, how to create our test User API with .NET Core 3.1 and Entity Framework Core. In previous articles, we have created this API in Nest.js and Rust. The code for this article is available on my GitHub.

Setting up WebAPI project.

To use .NET Core 3.1, first, you need to download SDK from Microsoft website https://dotnet.microsoft.com/download . After installation, you will have access to “dotnet” command-line tool. Let’s first create a folder for the project and initialize it with the .NET Core application. VS Code will ask you to download C# related extensions. Go ahead and install it. In addition, I have also added .NET Core Snippet Pack with frequently used snippets.

mkdir user-netcore
cd user-netcore
dotnet new webapi

It will create WebAPI folder structure with the WeatherForecast sample.

For our project, we don’t need it so just delete WeatherForecast* related files.

User data model

Create a new Models directory and inside our User model.

using System;

namespace user_netcore.Models
{
    public class User
    {
        public string id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string email { get; set; }
        public string name { get; set; }
        public DateTime createAt { get; set; }
        public string phoneNo { get; set; }
        public string companyName { get; set; }
        public string vatId { get; set; }


    }
}

Data Transfer Object

In real applications, we want to add an additional layer between the database and client view. By using DTO’s we can pick data from model to show end-users. A common example is a password field. When creating a user you need to save password hash and that’s it. After that, you don’t need to return the password back to the client so in your DTO model, it can be ignored.

In my simple example, we will remove information about the creation time.

Let’s create Dtos directory and inside GetUserDto class.

using System;

namespace user_netcore.Dtos
{
    public class GetUserDto
    {
        public string id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string email { get; set; }
        public string name { get; set; }
        public string phoneNo { get; set; }
        public string companyName { get; set; }
        public string vatId { get; set; }
    }
}

Service interface

Services are classed to execute actual operations on data. In our service we will retrieve users from database and from string. If you have application that will modify or create data this is the place where you use your selected endpoint libraries.

Let’s create Services directory and IUserService interface. We will use interface to be able to inject correct user service implementation during application start. We we will have string and database implementations.

IUserService:

using System.Collections.Generic;
using System.Threading.Tasks;
using user_netcore.Dtos;

namespace user_netcore.Services
{
    public interface IUserService
    {
        Task<List<GetUserDto>> get();
        Task<GetUserDto> getOne();
    }
}

It is good practice to mark API calls as async so we need to wrap out method in Task. For file implementations, there is no added value, because data will retrieve immediately, but for database connection, asynchronous execution will add value. It won’t hold your application while you are waiting for data.

To implement the service interface we need to add more features. As you could notice we are going to return out DTO but data endpoint is operating on the model.

Mapper

To map data between the user model and dto we are going to add AutoMapper library. There are two ways to add this package to your application. You can execute:

notnet add package AutoMapper.Extensions.Microsoft.DependencyInjection

or you can edit user-netcore.csproj file.

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RootNamespace>user_netcore</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="7.0.0" />
 </ItemGroup>


</Project>

After editing csproj file you need run

dotnet restore 

to apply changes.

Before using AutoMapper with user model you need to tell which DTO you would like to map. Create AutoMapperProfile class in the root directory.

using AutoMapper;
using user_netcore.Dtos;
using user_netcore.Models;

namespace user_netcore
{
    public class AutoMapperProfile : Profile
    {
        public AutoMapperProfile()
        {
            CreateMap<User, GetUserDto>();
        }
    }
}

The last step is to add AutoMapper on startup for application. Let’s do it by editing ConfigureServices in Startup.cs file. For development, you can also remove the app.UseHttpsRedirection(); from Configure method to stop redirecting your API to HTTPS.

using AutoMapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using user_netcore.Services;
using user_netcore.Data;
using Microsoft.EntityFrameworkCore;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace user_netcore
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            
            services.AddControllers();
            services.AddAutoMapper(typeof(Startup));

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            // app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

We are ready to get back and implement service for string user.

String User Service

It’s time to return the same data. Let’s implement first user service by crating UserServiceFromString in Services directory.

using System.Collections.Generic;
using System.Text.Json;
using System.Threading.Tasks;
using AutoMapper;
using user_netcore.Dtos;
using user_netcore.Models;

namespace user_netcore.Services
{
    public class UserServiceFromString : IUserService
    {
        private readonly IMapper mapper;
        public UserServiceFromString(IMapper mapper)
        {

            this.mapper = mapper;
        }
        public Task<List<GetUserDto>> get()
        {
            throw new System.NotImplementedException();
        }

        public async Task<GetUserDto> getOne()
        {
            string user = @"{'id':'89251ab3-1cdc-4629-9086-ce022cf6669e','firstName':'Marek','lastName':'Majdak','email':'info@sufrago.com','name':'sufrago','createAt':'2019-12-17T17:58:07.533406','phoneNo':'+48666666666','companyName':'Sufrago sp z o.o.','vatId':'PL 9512468001'}";
            user = user.Replace("'", "\"");

            var userEntity = JsonSerializer.Deserialize<User>(user, new JsonSerializerOptions
            {
                PropertyNameCaseInsensitive = true,
            });

            return mapper.Map<GetUserDto>(userEntity);
        }
    }
}

It is worth mentioning that for deserialization from string to User model I’m using a new System.Text.Json library from Microsoft that is faster than Newtonsoft. In the end, I’m using AutoMapper to return DTO from the model. We can use it because of dependency injection from the constructor. For testing the speed of API, as in the previous article, I will be using getOne from string implementation, so I can leave the first method not implemented. 

Controller

To define REST actions, create UserController in the Controllers directory. Make sure that there is “Controller” in the name as .NET Core will add it to your application if it exists.

I’m going to inject our service interface and define GET actions.

using System.Collections.Generic;
using System.Text.Json;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using user_netcore.Dtos;
using user_netcore.Services;

namespace user_netcore.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class UserController : ControllerBase
    {
        private readonly IUserService userService;
        public UserController(IUserService userService)
        {
            this.userService = userService;
        }

        public async Task<ActionResult<List<GetUserDto>>> Get()
        {

            return Ok(await userService.get());

        }

        [HttpGet("getOne")]
        public async Task<ActionResult<GetUserDto>> GetOneAsync()
        {

            return Ok(await userService.getOne());

        }
    }
}

In annotation Route, you can define how your API will be accessible. By using “[controller]” annotation, your user will be available by the controller name without the “Controller” part. In this case will will be address/user.

The HttpGet will define GET action (address/user/getone). In the first method .NET core will figure out it is GET on its own but you could also add the [HttpGet].

In Startup.cs in root directory let’s set correct service implementation.

       public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddAutoMapper(typeof(Startup));
            services.AddScoped<IUserService, UserServiceFromString>();
        }

You can run API by running dotnet command:

dotnet run

If everything went ok you can access address/user/getone method of your API. The address/user/ will throw an error as it is not implemented in the user from string service.

Adding Entity Framework for MySQL

I want to use our test database from previous articles. In this case, it will be a database first approach where we have an existing database. In our database, we have two user records.

First, let’s add the MySQL EF package and install Entity Framework CLI.

dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet tool install --global dotnet-ef

To run ef related commands you need to add also design package:

dotnet add package Microsoft.EntityFrameworkCore.Design

I’m going to create EF model from an existing database by running:

dotnet ef dbcontext scaffold "Server=localhost; Port=3306; Database=user; user=root; password=root;"
"Pomelo.EntityFrameworkCore.MySql" -o Data -c DataContext

This will create DataContext class with table database mapping:

using Microsoft.EntityFrameworkCore;
using user_netcore.Models;

namespace user_netcore.Data
{


    public partial class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options) { }
        public virtual DbSet<User> User { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<User>(entity =>
            {
                entity.ToTable("user");

                entity.HasIndex(e => e.firstName)
                    .HasName("IDX_e12875dfb3b1d92d7d7c5377e2")
                    .IsUnique();

                entity.Property(e => e.id)
                    .HasColumnName("id")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.lastName)
                    .HasColumnName("avatarId")
                    .HasColumnType("varchar(36)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.companyName)
                    .HasColumnName("companyName")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.createAt)
                    .HasColumnName("createAt")
                    .HasColumnType("timestamp(6)")
                    .HasDefaultValueSql("'CURRENT_TIMESTAMP(6)'");

                entity.Property(e => e.email)
                    .IsRequired()
                    .HasColumnName("email")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.firstName)
                    .IsRequired()
                    .HasColumnName("firstName")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.lastName)
                    .IsRequired()
                    .HasColumnName("lastName")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.name)
                    .IsRequired()
                    .HasColumnName("name")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.phoneNo)
                    .HasColumnName("phoneNo")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");

                entity.Property(e => e.vatId)
                    .HasColumnName("vatId")
                    .HasColumnType("varchar(255)")
                    .HasCharSet("latin1")
                    .HasCollation("latin1_swedish_ci");
            });
            OnModelCreatingPartial(modelBuilder);
        }
        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Let’s set up database configuration in appsettings.json configuration file and Startup.cs.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost; Port=3306; Database=user; user=root; password=root;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContextPool<DataContext>(x => x.UseMySql(Configuration.GetConnectionString("DefaultConnection"), x => x.ServerVersion("5.7.28-mysql")));
            services.AddControllers();
            services.AddAutoMapper(typeof(Startup));
            services.AddScoped<IUserService, UserServiceFromString>();
        }

Database user service

Everything is looking good now. We can implement IUserService for database and switch it in Startup.cs

UserService:

using System.Collections.Generic;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using AutoMapper;
using Microsoft.EntityFrameworkCore;
using user_netcore.Data;
using user_netcore.Dtos;
using user_netcore.Models;


namespace user_netcore.Services
{
    public class UserService : IUserService
    {
        private readonly IMapper mapper;
        private readonly DataContext context;

        public UserService(IMapper mapper, DataContext context)
        {
            this.context = context;
            this.mapper = mapper;
        }

        public async Task<List<GetUserDto>> get()
        {
            List<User> dbUsers = await context.User.ToListAsync();

            return dbUsers.Select(r => mapper.Map<GetUserDto>(r)).ToList();
        }

        public async Task<GetUserDto> getOne()
        {
            throw new System.NotImplementedException();
        }
    }
}

To use the database let’s inject our DataContext and change IUserService mapping in Startup.cs.

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContextPool<DataContext>(x => x.UseMySql(Configuration.GetConnectionString("DefaultConnection"), x => x.ServerVersion("5.7.28-mysql")));
            services.AddControllers();
            services.AddAutoMapper(typeof(Startup));
            services.AddScoped<IUserService, UserService>();
        }

Service is ready. Let’s run it and call address/users to make sure database data was retrieved.

GitHub – https://github.com/marekm1844/user-netcore

Previous articles:

If you have an interesting project or need a highly qualified team, take a look at Sufrago.com to learn more about our company and get in touch.

Leave a Reply

Your email address will not be published. Required fields are marked *