Implementing ORM with .Net

We will look at implementing Object–relational mapping with Entity core framework. We will cover the following topics.

1. What is ORM?
2. What problem does it solve?
3. ORMs in .Net
4. Clean architecture application using Entity framework and SQLite

What is ORM?

Object-relational mapping is a technology that connects the OOP(Object Oriented Programming) with relational databases. In OOP there is an entity or class that represents table and class properties represents column in the database. So, we don't need to write query against the database.

An SQL query to retrieve information about a user from the database would be :

Select name, address, email from users where user_id = 23;        

The same information can be retrieved using ORM would be :

users.GetById(23);        

What problem does it solve?

ORM tries to solve the following problems:

1. Impedance mismatch - Data is represented in different formats in relational database as table with rows/columns. While in OOP the data is represented in form of classes/Objects. The ORM bridges the gap between the two and solve this mismatch problem.
2. SQL Injections - ORMs provide a way to build queries that are parameterized, this can help prevent SQL injection attacks.
3. Database Abstractions - ORMs often provide an interface that is independent of databases. This allows developers to easily switch between different databases with minimal code changes.
4. Boilerplate Code Reduction: ORMs can automatically generate code for basic CRUD (Create, Read, Update, Delete) operations, reducing the amount of repetitive code that developers need to write.
5. Improved Productivity: By handling the details of persistence, ORMs allow developers to focus on the business logic of their applications, potentially improving productivity.

ORMs in .Net

There are some popular ORM in .Net.

1. Entity Framework (EF) : It is one of the most popular open source ORM in .Net. It is an ORM for ADO .Net. Entity Framework Core (EF Core) is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
2. Dapper : It is a Micro ORM which means it provides minimal functionality. It is very fast, efficient and light weight. Unlike EF it doesn't generate SQL for you but it just maps the result of SQL queries to .net objects.
3. NHibernate : This is a mature and flexible ORM, but it has a steeper learning curve than Entity Framework. It's a .NET port of the Java Hibernate ORM.
4. LLBLGen Pro : This is a commercial ORM that provides a lot of features and flexibility.

Sample application

We will use the following:

1. IDE - Visual Studio code with SQLite extension installed
2. You should have .Net SDK installed
3. SQLite
4. Entity core framework

Creating projects based on clean architecture

Use the following commands to create a template project.

mkdir EFCoreDemo
cd EFCoreDemo        

Create the solution file using below command we will have SocialNetworkingAPI that will be a web API project using controllers.

We will have a Domain project that will contain the classes that represents table in the database.

The Persistence project will interact with database SQLite.

dotnet new sln
dotnet new webapi -o SocialNetworkingAPI
dotnet new classlib -n Domain
dotnet new classlib -n Persistence        

Adding projects to the solution:

dotnet sln add SocialNetworkingAPI/SocialNetworkingAPI.csproj
dotnet sln add Domain/Domain.csproj
dotnet sln add Persistence/Persistence.csproj        

Adding project reference using the following commands:

cd SocialNetworkingAPI
dotnet add reference ../Domain/Domain.csproj
dotnet add reference ../Persistence/Persistence.csproj
cd ../Persistence
dotnet add reference ../Domain/Domain.csproj
cd ..        

You should run the following command to restore the dependencies and tools for this project:

dotnet restore        

Creating Domain entities

Go to the Domain project and create a class called User. We will create an User of the SocialNetworkingAPI. Remove the default class1.cs that is automatically created.

namespace Domain;

public class User
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string Email { get; set; }
    public string Education { get; set; }
    public string PhoneNumber { get; set; }
    public string City { get; set; }
    public string Gender { get; set; }
}

        

Note: Entity framework to work all the properties must be public with get and set defined and also disable the nullable property in Domain.csproj.

Adding Entity Framework Db context

We will use Entity framework which is an ORM. We will use SQLite database as its very lightweight without needing any additional software. In the persistence project. Install Microsoft.EntityFrameworkCore.Sqlite package though NuGet and make sure the version should be same as the Entity framework version you are using.

Create a class that extends from DbContext library class of entity core framework as shown below:

using Domain;
using Microsoft.EntityFrameworkCore;

namespace Persistence;
public class DataContext : DbContext
{
    public DataContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; }
}        

Here Users is the name of table. DbContextOptions is the connection string used to connect to the database.

We need to register the DataContext class in Program.cs present in SocialNetworkingAPI project.

using Microsoft.EntityFrameworkCore;
using Persistence;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<DataContext>(opt =>
{
    opt.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection"));
});

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();
        

Add an entry for DefaultConnection in the application.Development.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=SocialNetworking.db"
  }
}        

Creating Entity Framework code first migration

We will have to install dotnet-ef tool to create table using migration.

1. Go to https://www.nuget.org/packages/dotnet-ef
2. Copy the command from .Net CLI global
3. Install it in your local machine

After successful installation you will see the following message when you run dotnet ef.

We will use the following command :

dotnet ef migrations add InitialCreate -s SocialNetworkingAPI -p Persistence        

Note: The following command we are trying to create the tables from -s which is a starter project where Program.cs file resides and -p is the persistence project where the DbContext file resides. The above command will now give the following error:

Build started...
Build succeeded.
Your startup project 'SocialNetworkingAPI' doesn't reference Microsoft.EntityFrameworkCore.Design. This package is required for the Entity Framework Core Tools to work. Ensure your startup project is correct, install the package, and try again.        

Install Microsoft.EntityFrameworkCore.Design using NuGet in SocialNetworkAPI project and run the command again. You will notice that a migration folder is added in the Persistence project as shown below.

Closer look at classes generated by Migrations

The below code shows the code generated by the above command:

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Persistence.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Users",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "TEXT", nullable: false),
                    Name = table.Column<string>(type: "TEXT", nullable: true),
                    DateOfBirth = table.Column<DateTime>(type: "TEXT", nullable: false),
                    Email = table.Column<string>(type: "TEXT", nullable: true),
                    Education = table.Column<string>(type: "TEXT", nullable: true),
                    PhoneNumber = table.Column<string>(type: "TEXT", nullable: true),
                    City = table.Column<string>(type: "TEXT", nullable: true),
                    Gender = table.Column<string>(type: "TEXT", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Users", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Users");
        }
    }
}
        

1. Creates a C# class that extends from the Migration class which is a part of Entity framework core.
2. Up() and Down() are two important methods
3. Up : The Up() method is used to apply changes to the database schema. In this code it tries to create a table with name Users with columns as Id, Name, DateOfBirth etc. It also sets Id as Primary Key for Users table. migrationBuilder.createTable() method is used to create table. table.Column<T> is used to define the columns in the table. PrimaryKey method is used to set the primary key for the table Users.
4. Down : Down() is used to revert the changes done in Up method. In this case, it's dropping the "Users" table from the database. The migrationBuilder.DropTable() method is used to drop the table.

The #nullable disable directive is used to disable nullable reference types in this file. This is a feature in C# 8.0 and later that helps prevent null reference exceptions.

Create a database based on Migrations

We can create database when application starts this is equivalent to the dotnet ef update command. We will make use of code first approach here. Add the following code to the Program.cs file.

using var scope = app.Services.CreateScope();
var services = scope.ServiceProvider;
try
{
    var context = services.GetRequiredService<DataContext>();
    await context.Database.MigrateAsync();

}
catch (Exception ex)
{
    var logger = services.GetRequiredService<ILogger<Program>>();
    logger.LogError(ex, "An error occured during migration");
}
        

The above C# code is used to apply database migrations at runtime.

Lets breakdown the above code and understand what it does:

1. using var scope = app.Services.CreateScope(); : This line creates a new scope for retrieving services. In ASP.NET Core, services (like database contexts, repositories, etc.) are typically retrieved from a IServiceProvider. The CreateScope method is used to create a new scope for retrieving services. This is necessary because some services are registered as scoped and can only be retrieved within a specific scope.
2. var services = scope.ServiceProvider;: This line gets the service provider from the scope.
3. var context = services.GetRequiredService<DataContext>();: This line retrieves an instance of DataContext (which is a database context in Entity Framework Core) from the service provider.
4. await context.Database.MigrateAsync();: This line applies the pending database migrations asynchronously. If the database does not exist, it will be created.
5. The try/catch block: This is used to handle any exceptions that might occur during the migration. If an exception occurs, it gets an instance of ILogger<Program> from the service provider and logs the exception with the message "An error occurred during migration". The ILogger<T> interface is a part of the Microsoft.Extensions.Logging namespace and is used for logging in .NET Core applications. Start the .Net application using the following command:

dotnet watch        

This will start the application and will also have some information on how our database started. Displays that the Users table is created. Check what has happened go to SQLite extension in Vs code .

1. Open ctrl+shift+p open sqlite:SQL database
2. You will see that Users and __EFMigrationsHistory table is created as shown below:

3. See the content of __EFMigrationsHistory table if you click the play button

Seeding data to the Users table

Create a class Seed.cs in Persistence project as shown below:

using Domain;

namespace Persistence
{
    public class Seed
    {
        public static async Task SeedData(DataContext context)
        {
            if (context.Users.Any()) return;
            
            var users = new List<User>
            {
                new User
                {
                    Name = "Anil",
                    City = "Pune",
                    Email = "[email protected]",
                    DateOfBirth = DateTime.UtcNow.AddYears(-35),
                    Gender = "Male",
                    Education = "B.E",
                },
                new User
                {
                    Name = "Marie",
                    City = "Delhi",
                    Email = "[email protected]",
                    DateOfBirth = DateTime.UtcNow.AddYears(-25),
                    Gender = "Female",
                    Education = "M.E",
                },
                
            };

            await context.Users.AddRangeAsync(users);
            await context.SaveChangesAsync();
        }
    }
}        

This code will add records to the users table. We will now add the code to seed data in the database in Program.cs file and run the application.

await Seed.SeedData(context);        

Open SQLite explorer and see what is inside users table. The records are now inserted in the users table:

Adding an API controller

In this section we will add a controller and then use swagger to get users from database.

Go to the SocialNetworkingAPI project and add a C# class as shown below:

using Microsoft.AspNetCore.Mvc;

namespace SocialNetworkingAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class BaseApiController : ControllerBase
    {
        
    }
}        

This C# code defines a base controller for an ASP.NET Core API. Here's a breakdown of what the code does:

1. using Microsoft.AspNetCore.Mvc;: This line imports the Microsoft.AspNetCore.Mvc namespace, which contains classes and interfaces that support the MVC pattern for ASP.NET Core apps.

2. namespace SocialNetworkingAPI.Controllers: This line declares that the BaseApiController class is part of the SocialNetworkingAPI.Controllers namespace.

3. [ApiController]: This attribute indicates that the BaseApiController class is a controller for an API. This attribute enables certain API-specific features such as automatic model validation and automatic HTTP 400 responses.

4. [Route("api/[controller]")]: This attribute sets the route template for the controller. The [controller] placeholder in the route template is replaced by the controller's name. For example, if a controller named UserController inherits from BaseApiController, the route to the controller will be "api/User".

5. public class BaseApiController : ControllerBase: This line declares a public class named BaseApiController that inherits from ControllerBase. ControllerBase is a base class for an MVC controller without view support.

The BaseApiController class doesn't contain any actions itself, but it sets up common settings (like routing and API behavior) that will be inherited by other controllers. This is a common practice in ASP.NET Core to avoid duplicating these settings in every controller.

Create another class called UsersController.cs a shown below:

using Domain;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Persistence;

namespace SocialNetworkingAPI.Controllers;

public class UsersController : BaseApiController
{
    private readonly DataContext _context;
    public UsersController(DataContext context)
    {
        _context = context;
        
    }
    [HttpGet]
    public async Task<ActionResult<List<User>>> GetUsers()
    {
        return await _context.Users.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<User>> GetUser(Guid id)
    {
        return await _context.Users.FindAsync(id);
    }
        
}        

This class basically does the following :

1. Declares a private instance of DataContext which is a database context in EF core. This db context is initialized by constructor which is dependency injection.
2. Defines two action methods namely GetUsers() and GetUser(id) that handles HTTP get requests.
3. Here TolistAsync() and FindAsync() are methods defined in EF core.

Restart the application the swagger page is displayed as shown below:

Hooray now you have an end to end working application using EF core and SQLite.

Resources

The application code can be found at this location:

https://github.com/meghaSahay/EFCoreDemo/