Ravindra Devrani

In this tutorial, we are going to make dotnet core web api application and perform all the CRUD (create, read, update and delete). I have tried to keep it simple and avoided any complexities like repository pattern.

💻Source code

Tech and tools used

• Dotnet 10
• MySql 8+ (in docker container. Click here, if you want to create a MySql container in docker)
• Entity Framework Core (ORM)
• .NET CLI and VS Code (alternate Visual Studio 2025)

Creating a project

Execute these commands in a sequence.

In this tutorial, we are going to make dotnet core web api application and perform all the CRUD (create, read, update and delete). I have tried to keep it simple and avoided any complexities like repository pattern.

💻Source code

Tech and tools used

• Dotnet 10
• MySql 8+ (in docker container. Click here, if you want to create a MySql container in docker)
• Dapper (ORM)
• .NET CLI and VS Code (alternate Visual Studio 2025)

Create database

Let’s start with creating a database and a table. You need to execute this script.

Execute the command below.

For bash:

docker run -d \
  --name mysql-dev \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=p@55w0rd \
  -v mysql-data:/var/lib/mysql \
  mysql:8.0.44-debian

For powershell:

docker run -d `
  --name mysql-dev `
  -p 3306:3306 `
  -e MYSQL_ROOT_PASSWORD=p@55w0rd `
  -v mysql-data:/var/lib/mysql `
  mysql:8.0.44-debian

📢 For mac silicon specific:

mysql:8.0.44-debian does not support ARM architectureUse – so use mysql:latest instead of mysql:8.0.44-debian or check official docker hub for suitable image.

Previously there was no simple solution for left or right join. We had to use DefaultIfEmpty or GroupJoin and SelectMany.

With DefaultIfEmpty():

var customerOrders = await (
    from c in ctx.Customers
    join o in ctx.Orders on c.CustomerId equals o.CustomerId into orders
    from o in orders.DefaultIfEmpty()
    select new {
        c.CustomerId,
        c.CustomerName,
        OrderId = o == null ? 0 : o.OrderId
    }
).ToListAsync();

I have used the way described above in most of my career. It was really hard to remember that syntax and I always struggled with that.

I am sharing an incident when I was building my first project. I had a cs degree, I knew c# and .net but I’ve never built anything with it except few console and very small windows app. I was writing a code of uploading images and I needed to write the same image upload code in multiple places. It was fine when I’ve copied it in 2 or 3 places, but I was getting annoyed when I had to copy it in multiple places. I did not bother to refactor it. Honestly, I didn’t even know about refactoring. But in future, I had to update the code and I had to update the code in multiple places. Then I got to know about utility or helper methods. Later I learned that it is a concept called DRY (Do not repeat yourself).

In this tutorial, we will understand what cartesian explosion is and how to solve that problem. Let’s look at this query:

var people = await ctx.People
   .Include(p => p.Emails)
   .Include(p => p.Addresses)
   .ToListAsync();

It translates to:

 SELECT "p"."Id", "p"."FirstName", "p"."LastName", "e"."Id", "e"."PersonEmail", "e"."PersonId", "a"."Id", "a"."PersonAddress", "a"."PersonId"
      FROM "People" AS "p"
      LEFT JOIN "Emails" AS "e" ON "p"."Id" = "e"."PersonId"
      LEFT JOIN "Addresses" AS "a" ON "p"."Id" = "a"."PersonId"
      ORDER BY "p"."Id", "e"."Id"

• Person joins with Email and Address. Both joins are at the same level.
• A person can have multiple emails and adressess.

Let’s say a person with id = 1 have 10 emails and 10 addresses. The query returns 1x10x10 = 100 rows for 1 person. It is just for one person, how much they can be for 100 people. This problem is known as cartesian explosion

To be honest, since I have started using docker I have never installed any database in my machine. I use multiple databases and keeping each database up and running takes a toll on resources of your machine. And… you can not install some database like “sql server” on “mac”, then docker is only option for you. Any way, I think it is enough introduction. Let’s get started.

To be honest, since I have started using docker I have never installed any database in my machine. I use multiple databases and keeping each database up and running takes a toll on resources of your machine. And… you can not install some database like “sql server” on “mac”, then docker is only option for you. Any way, I think it is enough introduction. Let’s get started.

(image credit: Gemini)

We are going to create folders and install packages in this step. I don’t have much to say for this tutorial. I have tried to explain necessary stuff through comments. In my opinion code is self-explanatory. So, let’s execute these commands in a sequence.

# create a directory
mkdir backend

# change directory
cd backend

# create the `src` directory inside the `backend`.
mkdir src

# create file
touch src/index.ts 

# initialize node js project. 
# Please stay inside the `backend` directory. 
npm init -y 

# install these packages
npm install express cors

# install the dev dependencies
# these packages won't be shipped to your production bundle
npm install -D typescript tsx @types/node @types/express @types/cors

# generate tsconfig.json file
npx tsc --init

Folder structure should look like this.

💻Source code

Lazy loading

You load related entities on demand rather loading at once. This approach is usually useful when you want to load related entities on a certain condition (otherwise always use eager loading).

To enable lazy loading, you need to install this package: Microsoft.EntityFrameworkCore.Proxies

And do following changes in Program.cs

builder.Services.AddDbContext<AppDbContext>(o=>o.UseLazyLoadingProxies()
.UseSqlite("Data Source = mydb.db"));

Look at the example below:

app.MapGet("/", async (AppDbContext context) => {
    foreach (var genre in await context.Genres.ToListAsync()) 
    {
        foreach (var book in genre.Books)
        {
            Console.WriteLine($"===> genre: {genre.Name}, Book: {book.Title}, Price : {book.Author}");
        }
    }
    return Results.Ok("Ok");
});

We are not loading books with genres at once. Rather we are loading books for each genres. Let’s take a look at logs of console.