Dotnet

Let’s say you want to execute a code block when Book table is not empty. In Entity Framework Core, we can achieve this in two ways (there might be others but I am unaware of them):

Option 1:

 if(context.Books.Count()>0)
 {
     // do something
 }

Option 2:

 if (context.Books.Any())
 {
     // do something
 }

Note 📢: I am testing these queries against a table containing 1 million rows.

My Distro

I am using linux mint 22.1 which is based on Ubuntu 24.04.

Straightforeward command

sudo apt-get update

sudo apt-get install -y dotnet-sdk-9.0

But…

I have tried to run this command sudo apt-get install -y dotnet-sdk-9.0 but unfortunately I got no success. I have found that, this command works only with Ubuntu 24.10. For Ubuntu 24.04 I need to use different approach.

Uninstall prior version if exists

sudo apt-get remove dotnet-sdk-8.0

Now, run these commands in a sequence:

Stored procedure

CREATE OR ALTER PROCEDURE [dbo].[CreateTrackEntry]
  @EntryDate DATE,
  @SleptAt DATETIME2,
  @WokeUpAt DATETIME2,
  @NapInMinutes SMALLINT,
  @TotalWorkInMinutes SMALLINT,
  @Remarks NVARCHAR(1000) = NULL,
  @TrackEntryId INT OUTPUT
AS
BEGIN
   -- code removed for brevity

END

We have a stored procedure that returns TrackEntryId as an output parameter. Let’s see how can we execute it from the dapper?

using IDbConnection connection = new SqlConnection(_connectionString);

var parameters = new DynamicParameters(trackEntryToCreate);
// Input params
parameters.Add("@EntryDate", trackEntryToCreate.EntryDate);
parameters.Add("@SleptAt", trackEntryToCreate.SleptAt);
parameters.Add("@WokeUpAt", trackEntryToCreate.WokeUpAt);
parameters.Add("@NapInMinutes", trackEntryToCreate.NapInMinutes);
parameters.Add("@TotalWorkInMinutes", trackEntryToCreate.TotalWorkInMinutes);
parameters.Add("@Remarks", trackEntryToCreate.Remarks);

// output params
parameters.Add("@TrackEntryId", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync("CreateTrackEntry", parameters,commandType:CommandType.StoredProcedure);

int trackEntryId = parameters.Get<int>("@TrackEntryId");

SwaggerUI, which was previously bundled with .NET Core APIs, has been dropped in .NET 9. However, .NET Core Web APIs still support generating OpenAPI documents. .NET Core apps have built-in support for generating information about endpoints and it uses Microsoft.AspNetCore.OpenApi package for that. To configure interactive UIs for these OpenAPI documents, we have several options. We are going to explore these two:

1. Swashbuckle SwaggerUI
2. Scalar

Create a new project, if does not have an existing

Execute these commands in a sequence

Isn’t it already described in Dapper docs? Sure it is. Why do I bother to write this? Am I just wtiting it for the sake of “posting”? No, I am not. Actually, I was trying to write the code by using Dapper’s docs. Unfortunately, I ran into a few bugs. I am using .NET 9, by the way and this is not even a blog post; it’s just a code snippet. I thought I should share it, may be someone else is facing the same problem as me.

First we need to know about the traditional offset based pagination and the problems it introduces.

Offset pagination

In the code below we are using the offset pagination.

[HttpGet("offset")]
public async Task<IActionResult> GetBooks(int limit=10, int page=1)
{
    var books = await _context.Books
        .AsNoTracking()
        .OrderBy(a => a.Id)
        .Skip(limit * (page - 1))
        .Take(limit)
        .ToListAsync();
    return Ok(books);
}

Which translates to the following sql:

SELECT
   [b].[Id],
   [b].[Author],
   [b].[Country],
   [b].[ImageLink],
   [b].[Language],
   [b].[Link],
   [b].[Pages],
   [b].[Price],
   [b].[Title],
   [b].[Year]
FROM [Book] AS [b]
ORDER BY [b].[Id]
OFFSET @__p_0 ROWS
FETCH NEXT @__p_1 ROWS ONLY

Note: In every pagination logic, ordering must be unique. In our case we are using Id which is unique.

There are some common practices one should take care of while designing REST APIs.

There is also a video version of this post.

1. Use descriptive names for resources

• ❌ /api/getAllBooks
• ❌ /api/retrieveBooks
• ❌ /api/manageBooks
• ❌ /api/process
• ✅ /api/books

2. Use nouns not verbs

• ❌ /api/mangage-books
• ✅ /api/books

3. Use plural nouns

4. Use hyphens (-) in url for better readabilty

• ❌ /api/useraccounts
• ✅ /api/user-accounts

5. Never use crud method names in url

6. Use http method properly

7. Use Http StatusCodes Correctly

These are the most commonly used status codes.

Example Setup

I am using a MinimalApi application. Create a new one if you need.

dotnet new webapi -n DITest

Open it in a IDE or editor of your choice.

public interface IMyService
{
    Guid InstanceId { get; }
}

public class MyService : IMyService
{
    public Guid InstanceId { get; }

    public MyService()
    {
        InstanceId = Guid.NewGuid();
        // We are logging in the constructor, so that we get notified whenever the instance is created
        Console.WriteLine($"==> Service created with InstanceId: {InstanceId}");
    }
}

IMyService have a read-only property named InstanceId of type Guid, which is set from the constructor. We are logging inside the constructor, so that we can get notified whenever the new instance is created.

I don’t think there is any need of introduction. Let’s jump to the code section. We coders understand with code more. Let’s understand the concept then you don’t need to remember any definition.

This is the recordset against which I am running queries.

First and foremost let’s say we need a record with LastName=”Doe”.

I have used database first approach in the .NET Framework 4.X. But It is the first time I am trying to use Db First approach in the .NET Core (I am using .net 9). Microsoft refers it ‘Scaffolding (Reverse Engineering)’. Personally I prefer Dapper for DB First approach. May be you have an existing database and you want to use it with ENTITY FRAMEWORK, then this approach might be helpful. I am playing around it and documenting it and sharing that journey with you. Let’s see what it offers.