EF Core under the hood: Count() vs Any()

coun_vs_any_thumb

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.

[Read More]

Linux Basic Commands

  1. Contents of a file: ls
  2. Content list with long format: ls -l or ls -l -h human readable or concat the both ls -lh.
  3. Change directory: cd DirectoryName
  4. Move to the upper directory: cd ..
  5. Switch to previous directory : cd -
  6. Change to home directory: cd ~
  7. Go to full path: cd /Home/Documents/Pitctures/MyPictures
  8. Tilde (~) for home directory : MyPc:~/Documents$ cd ~/Videos
  9. Clear screen: clear
  10. Show different drives in the computer (List block devices): lsblk
  11. Opening a file in the text editor :
# open in nano
nano filename

#open in xed editor
xed filename
  1. Root directory: cd /
  2. Press Tab to autocomlete.
  3. Create a directory : mkdir blah
  4. Remove a directory : rmdir blah
  5. Creating a blank file : touch something.txt 14.1. Creating multiple files at once: touch f1.txt f2.txt f3.txt f4.md f5.md
  6. Printing something in terminal : echo hello
  7. Create a file with text: echo "blah blah" > blah.txt
  8. Show contents of a file: cat blah.txt
  9. Copy file : cp /source/filename.ext destination/filename.ext
# note: I am currently in the `Desktop` directory
# copy `blah.txt` from `Desktop` to `Documents` directory

cp blah.txt ~/Documents/blah2.txt

# copy `blah.txt` from `Desktop` to `Documents` directory with changed filename

cp blah.txt ~/Documents/blah2.txt 

# it will copy `blah.txt from `Desktop` to `Documents` directory with changed file name `blah2.txt`
  1. Copy all files (. allfiles.allextensions): cp *.* ~/Documents

    [Read More]

How to Install DotNet SDK In Ubuntu Based Distros?

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:

[Read More]

Dapper: Output Parameter

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

Configuring dotnet core apps for OpenApi with SwaggerUi or Scalar

dotnet core apps for OpenApi with Scalar

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

[Read More]

Transactions in Dapper

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.

[Read More]

Inserting bulk records (1 million) in SQL Server

Creating a database

USE master
GO

DROP DATABASE IF EXISTS BookMillion
GO

CREATE DATABASE BookMillion
GO

USE [BookMillion]
GO

CREATE TABLE [dbo].[Book](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](100) NULL,
	[Author] [nvarchar](100) NOT NULL,
	[Country] [nvarchar](100) NULL,
	[ImageLink] [nvarchar](100) NULL,
	[Language] [nvarchar](20) NULL,
	[Link] [nvarchar](200) NULL,
	[Pages] [int] NULL,
	[Year] [int] NULL,
	[Price] [int] NULL

	CONSTRAINT PK_Book_Id PRIMARY KEY (Id)
)
GO

Bulk insert using recursive cte

USE [BookMillion]
GO

WITH Numbers AS (
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N < 1000000
)
insert into Book (Title,Author,Country,[Language],ImageLink,Link,Pages,Price,[Year])
select
'Book'+ cast(N as varchar),
'Author'+ cast(N as varchar),
'Country'+ cast(N as varchar),
'Language'+ cast(N as varchar),
'ImageLink'+ cast(N as varchar),
'Link'+ cast(N as varchar),
ABS(CHECKSUM(NEWID())) % 901 + 100, -- Pages between 100-1000
ABS(CHECKSUM(NEWID())) % 901 + 100, -- Price between 100-1000
ABS(CHECKSUM(NEWID())) % 825 + 1200 -- Year between 1200-2024
from Numbers

option (maxrecursion 0)

GO
sql 

Keyset Pagination In Entity Framework Core

keyset pagination in .net core

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.

[Read More]

Rest Api Designing Best Practices

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

There is also a video version of this post.

rest_api_design_practices

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

❌ Singular Nouns✅ Plural Nouns
/api/book/api/books
/api/movie/api/movies
/api/person/api/people
/api/customer/api/customers

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

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

5. Never use crud method names in url

HttpMethod❌❌❌✅✅✅
GET/api/books/GetAllBooks/api/books
GET/api/books/GetBookById/{id}/api/books/{id}
POST/api/books/CreateBook/api/books
PUT/api/books/UpdateBook/{id}/api/books/{id}
DELETE/api/books/DeleteBook/{id}/api/books/{id}

6. Use http method properly

HttpMethodEndpointDescription
GET/api/booksIndicates a get resources
GET/api/books/{id}Indicates a get resource with id
POST/api/booksIndicates creating a resource
PUT/api/books/{id}Indicates updating a resource
DELETE/api/books/{id}Indicates deleting a resource

7. Use Http StatusCodes Correctly

These are the most commonly used status codes.

[Read More]

Covering Indexes In Sql Server

Table structure and total records

Let’s look at our table structure as shown in the left panel of the picture

book_structure

Note: There is a non-clustered index on the Title column of the Book table, which has a total of 1 million records.

select
  Id,
  Title,
  Author,
  Price
from Book where title='The Divine Comedy'

query_execution_plan

As you have noticed in the execution plan, there is a key lookup. Since index is only available for the Title column, Title is retrieved from the non-clustered index. However, other columns like Id, Author and Price are not present in the non-clustered index, so they will be retrieved from the clustered index (the Book table). This results in an additional key lookup step and may decrease the performance. It’s like, you are looking for records in two places (non clustered index and clustered index).

[Read More]
sql