Ravindra Devrani

NOTE: You can find the source code here.

Schema Overview

// Department
public class Department
{
    public int DepartmentId { get; set; }
    public string Name { get; set; } = string.Empty;

    public ICollection<Employee> Employees { get; set; } = [];
}

// Employee

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; } = string.Empty;

    public int DepartmentId { get; set; }

    public Department Department { get; set; } = null!;

    public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = [];
}

// Skills

public class Skill
{
    public int SkillId { get; set; }
    public string Name { get; set; } = string.Empty;

    public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = [];
}

// EmployeeSkills (Junction table)

public class EmployeeSkill
{
    public int EmployeeId { get; set; }
    public int SkillId { get; set; }

    public Employee Employee { get; set; } = null!;
    public Skill Skill { get; set; } = null!;
}

In simpler terms:

A query is SARGable (Search ARGument able) if a database engine can take advantage of index.

What makes a query non SARGable?

These are the few factors that makes a query non sargable.

1. Put a column inside a function in the where clause.

select
 s.SalesOrderID,
 s.SalesPersonID,
 s.OrderDate,
 s.SalesOrderNumber
from Sales.SalesOrderHeader s
where YEAR(s.OrderDate) = 2011; -- Row Retrieved: 1607 and Row read : 31465

Note: I have created covering index on the OrderDate:

Initially, it was not intended to be an article. I was tinkering around sql and documenting few things, then I thought, it could turnout to a nice article. I have wrote lots of article and created lots of videos on c#, .net core and angular. But it is my first article on the SQL.

In this blog post, we are going to explore few terms of execution plan, like table scan, clustered index scan, index seek, RID lookup and key lookup.

In this tutorial we are going to learn:

• How to create a cli tool?
• How to create a nuget package?
• How to install it in our machine.

CLI tools are very useful, they are very easy to use. We are already using it in our every day.

dotnet new console -o CliToolDemo

It is the example of dotnet cli tool, simple way to create a .net console application. You can also create your own cli tools. We are going to use a package created by Microsoft named System.CommandLine , which is a pre-released version as of I am writing this blog post. It is a pre-released version for so long, I wonder what are the plans about this package. You can also use a library named Cocona to create cli tools, which is pretty simple and widely popular. But I am going to stick with System.CommandLine . You should definitely checkout the cocona.

REST APIs are stateless, so server does not store any information about the client. So we can not authorize the rest application in a traditional way. How does a server knows if the user is authenticated user or not? In this situation the Json Web Token (JWT) saves the day.

JSON Web Tokens are an open, industry standard RFC 7519 method for representing claims securely between two parties. Source: jwt.io

[Read More]

Problem details is a standard way to communicate error details in HttpResponse, defined in rfc 7807. Standard ProblemDetails Properties:

• Type: URI identifying problem type
• Title: Short error description
• Status: HTTP status code
• Detail: Specific error explanation
• Instance: URI identifying specific error occurrence

Problem details is automatically integrated with .net core APIs. When we return the BadRequest we generally get response with problem details.

// controller method
return BadRequest();

// response

{
 "type": "https://tools.ietf.org/html/rfc9110#section-15.5.1",
 "title": "Bad Request",
 "status": 400,
 "traceId": "00-2d4948694b0f223f7f5dff215b42481b-0288bb95d7604783-00"
}

The same thing happens when we return the NotFoundException.

💻Source Code: https://github.com/rd003/PostgressDapperDemo

Tools and technology used

• VS Code (editor)
• .Net 8
• Postgres
• Dapper

Let’s get started with creating the database first.

create database PersonDb;

Now, create a table within the database.

create table Person
(
 Id serial primary key,
 Name varchar(30) not null,
 Email varchar(30) not null
);

To create a new project you need to run these commands in a sequence.

> dotnet new sln -o PostgressDapperDemo

> cd PostgressDapperDemo

> dotnet sln add .\PostgressDapperDemo\

> code . #this command will open this project in the vs code

Nuget packages

Install the following nuget packages.

The Unit of Work Pattern is all about coordinated changes to the database. It groups multiple operations, such as inserts, updates, and deletes, into one transaction. This simply means that all the changes are done together as a complete action, or they all don’t happen at all. In case something goes wrong in one of the operations, the whole transaction rolls back and keeps the database consistent by not allowing partial updates. This makes it easy to handle errors and ensures reliable data.

Integration testing is a software testing technique, where individual units of a program are integrated together and tested as a group for interacting harmoniously with each other. It concerns the testing of interactions and interfaces between modules, components, or systems to see if they behave as expected once integrated.

📢 Always use real database for integration testing instead of InMemory Db.

Purpose

• This is to ensure that various components or modules behave according to expectation.
• In the case of integration, to find out whether there are problems concerning interfaces or inconsistencies in data.
• Verifying whether it meets the set specifications and functionality of an integrated system.

Tech Used in this project

• .Net 8 web APIs (controller)
• Sqlite
• EntityFrameworkCore
• xUnit
• In Memory Database (for testing)

Let’s get started

Create a sln file (I have named it PersonGithubActionsDemo) with two projects

As the name suggesting , Unit testing is a software testing where smallest units of the application such as methods are tested in the isolation, so that we can ensure our software is working as expected.

Commonly used testing frameworks

• MSTest
• nUnit
• xUnit

Mocking frameworks

Mocking framework is a library which allows us to mock the objects. For example, a PeopleController is injected with the IPersonRepository. While testing the PeopleController, we need the IPersonRepository. Mock frameworks comes to rescue in that situation. With the help of mock frameworks we can mock the IPersonRepository and mimic it’s behavior. Some popular mocking libraries are: