Q: What is Async/Await in ASP.NET Core APIs and why is it important?

async/await enables non-blocking I/O operations — the thread is released while waiting for database/network calls, allowing it to serve other requests. Synchronous (blocks thread): [HttpGet] public IActionResult GetProducts() { var products = _db.Products.ToList(); // thread waits here return Ok(products); } Asynchronous (non-blocking): [HttpGet] public async Task<IActionResult> GetProducts() { var products = await _db.Products.ToListAsync(); // thread free while waiting return Ok(products); } Under high load — async APIs handle significantly more concurrent requests with the same hardware.

Q: What is Middleware in ASP.NET Core? Give real examples.

Middleware is software that runs in the request-response pipeline — every HTTP request passes through it in order. Request → Middleware 1 → Middleware 2 → Middleware 3 → Controller Response ← Middleware 1 ← Middleware 2 ← Middleware 3 ← Controller Real world analogy: Airport security — every passenger (request) goes through check-in, security scan, gate check — in order. Common built-in middleware: app.UseHttpsRedirection(); // HTTP → HTTPS app.UseAuthentication(); // Who are you? app.UseAuthorization(); // What can you do? app.UseRouting(); // Which controller? app.MapControllers(); // Call the controller Order matters — UseAuthentication must come before UseAuthorization.

Question 1

What is Covering Index?

Accepted Answer

This concept builds on what you just learned above, so understand it as a natural next step.

The problem it solves:

Suppose you have a non-clustered index on EmpID, and you run:

sql

SELECT EmpID, EmpName, Salary FROM Employees WHERE EmpID = 5

SQL Server uses your non-clustered index on EmpID to quickly find the row. But wait — the index only stored EmpID. You also need EmpName and Salary. So SQL Server has to:

Find EmpID = 5 in the index (fast)
Follow the pointer back to the main table (clustered index / heap) to fetch EmpName and Salary (extra step — called a Key Lookup)

This extra trip back to the main table costs performance, especially if 10,000 rows match.

What is a Covering Index?

A covering index is a non-clustered index that includes all the columns a query needs, so SQL Server never has to go back to the main table at all. The index "covers" the query entirely.

Think of it like this: You're a hotel receptionist and someone asks you "What is the room number and phone extension for guest Rahul?" If your register only has room numbers, you'd have to look up the phone extension elsewhere (extra trip). But if your register has BOTH the room number AND phone extension, you can answer everything without leaving your desk. That register is a "covering index."

How to create one — using INCLUDE:

CREATE NONCLUSTERED INDEX IX_Emp_EmpID
ON Employees (EmpID)
INCLUDE (EmpName, Salary);

Here, EmpID is the key column (what you search by), and EmpName, Salary are included columns (extra data stored in the leaf level of the index so SQL doesn't need to look them up).

Now the same query:

SELECT EmpID, EmpName, Salary FROM Employees WHERE EmpID = 5

SQL does only ONE operation — Index Seek — finds the row in the index and already has all three columns right there. No Key Lookup needed. Blazing fast.

Key distinction — Key columns vs INCLUDE columns:

Key columns: Columns in the WHERE, JOIN ON, or ORDER BY clause — these form the B-tree structure of the index
INCLUDE columns: Columns only in the SELECT — they sit at the leaf level only and don't affect sorting, but make the index self-sufficient for that query

When to use it:

When you see a Key Lookup in the execution plan — that's the signal to consider a covering index
When a specific query runs very frequently and performance is critical
When adding columns to the key itself would be wasteful (e.g., you don't want to sort by Salary, you just need it returned)

Question 2

Clustered vs Non-Clustered Index

Accepted Answer

First, understand what an index is.

Imagine a thick book about 1000 employees. If you want to find "Rahul Sharma", you don't read page 1 to 1000 — you go to the index at the back of the book, find "Sharma, Rahul → Page 347", and directly jump there. That's what a database index does — it helps SQL find rows fast without scanning everything.

Now, SQL Server has two types of indexes.

Clustered Index

A clustered index physically sorts and stores the actual table data in the order of the index key.

Think of it like an Aadhaar card database sorted by Aadhaar number. The physical files in the cabinet are arranged by number — 0001, 0002, 0003... The data itself is in order.

Key facts:

A table can have only ONE clustered index — because you can only physically sort data one way
By default in SQL Server, when you create a Primary Key, it automatically creates a Clustered Index on that column
Since the data IS the index, there is no separate structure — finding the index means finding the data directly
Very fast for range queries like WHERE EmpID BETWEEN 100 AND 200 because the rows are physically next to each other

Non-Clustered Index

A non-clustered index is a separate structure that stores the index key + a pointer back to the actual data row.

Think of it like the index page at the back of a textbook. The index page is separate from the main content. It says "Topic: Pension Fund → See Page 78." You look at the index, get the page number, then flip to that page.

Key facts:

A table can have up to 999 non-clustered indexes
It stores a copy of the indexed columns + a row locator (pointer to find the actual row)
If the table has a clustered index, the pointer is the clustered key value. If no clustered index exists, it's a Row ID (file:page:slot)
Great for columns you frequently search/filter/join on but that aren't the primary key

Side-by-side comparison:

Point	Clustered	Non-Clustered
Physical sort	Yes, data is physically reordered	No, separate structure with pointers
Count per table	Only 1	Up to 999
Storage	Data pages ARE the index	Separate index pages + pointers
Speed (direct lookup)	Fastest (data found directly)	Slightly slower (pointer lookup needed)
Best for	Primary key, range queries	Filter columns, join columns, search columns
Default created on	Primary Key	Any additional column you index

Real-world example from your NPS project:

In your NPS database, the PRAN (Permanent Retirement Account Number) column is likely the Primary Key — so SQL Server creates a Clustered Index on it automatically. When you do a lookup by PRAN, it's lightning fast because it goes directly to the data.

But if you also frequently search by CorporateID or MobileNo, you'd add a Non-Clustered Index on those columns so those queries are also fast.

Question 3

What is Minimal API in ASP.NET Core and when should you use it?

Accepted Answer

Minimal API is a lightweight way to build APIs without Controllers — introduced in .NET 6.

Traditional Controller-based:

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    [HttpGet]
    public IActionResult Get() => Ok(_products);
}

Minimal API:

app.MapGet("/api/products", () => Results.Ok(_products));
app.MapPost("/api/products", (Product p) => Results.Created($"/api/products/{p.Id}", p));

When to use Minimal API:

Microservices with few endpoints
Serverless functions
Simple CRUD APIs
Performance-critical scenarios (less overhead)

When to use Controllers:

Large applications with many endpoints
Teams used to MVC structure
Complex filters and action results needed

Question 4

What is JWT Authentication and how does it work in ASP.NET Core?

Accepted Answer

JWT (JSON Web Token) is a stateless authentication mechanism — the server doesn't store session data.

How it works:

User logs in → server validates credentials
Server creates a JWT token (signed with secret key)
Client stores token (localStorage or cookie)
Client sends token in every request header: Authorization: Bearer
Server validates token signature — no database lookup needed

JWT structure: Header.Payload.Signature

Header: algorithm used
Payload: user data (id, role, email)
Signature: tamper-proof hash

Setup in ASP.NET Core:

builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(options => {
        options.TokenValidationParameters = new TokenValidationParameters {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidateLifetime = true,
            ValidateIssuerSigningKey = true,
            IssuerSigningKey = new SymmetricSecurityKey(
                Encoding.UTF8.GetBytes(config["Jwt:Key"]))
        };
    });

Question 5

What is Async/Await in ASP.NET Core APIs and why is it important?

Accepted Answer

async/await enables non-blocking I/O operations — the thread is released while waiting for database/network calls, allowing it to serve other requests.

Synchronous (blocks thread):

[HttpGet]
public IActionResult GetProducts()
{
    var products = _db.Products.ToList(); // thread waits here
    return Ok(products);
}

Asynchronous (non-blocking):

[HttpGet]
public async Task GetProducts()
{
    var products = await _db.Products.ToListAsync(); // thread free while waiting
    return Ok(products);
}

Under high load — async APIs handle significantly more concurrent requests with the same hardware.

Question 6

What is Middleware in ASP.NET Core? Give real examples.

Accepted Answer

Middleware is software that runs in the request-response pipeline — every HTTP request passes through it in order.

Request → Middleware 1 → Middleware 2 → Middleware 3 → Controller
Response ← Middleware 1 ← Middleware 2 ← Middleware 3 ← Controller

Real world analogy: Airport security — every passenger (request) goes through check-in, security scan, gate check — in order.

Common built-in middleware:

app.UseHttpsRedirection();  // HTTP → HTTPS
app.UseAuthentication();    // Who are you?
app.UseAuthorization();     // What can you do?
app.UseRouting();           // Which controller?
app.MapControllers();       // Call the controller

Order matters — UseAuthentication must come before UseAuthorization.

Mastery Tracks

Database

ASP.NET Core Web API

JAVA

Dream Companies

Mastery Tracks

Database

ASP.NET Core Web API

JAVA

Dream Companies