in_ Controller_file

 1.)post -- CreateAtRoute & CreateAtAction CreatedAtAction and CreatedAtRoute are helper methods in ControllerBase used in HTTP POST endpoints to return a 201 Created status code, include a Location header pointing to the new resource, and return the created object in the body.

2) Pagination

• OffSet Pagination : - Use OrderBy(), Skip() & Take();
• Keyset or cursor-based pagination : - using refrence value and where().

3) Async API + CancellationToken -Async programming in .NET allows the application to handle I/O-bound operations efficiently without blocking a thread while waiting for the operation to complete. It improves scalability, especially when handling multiple concurrent API requests.

A CancellationToken allows a caller to communicate that an ongoing operation should stop.

For example:

• A user closes their browser.
• The client cancels an HTTP request.
• A request exceeds an application-defined time limit.
• An application is shutting down.

Instead of continuing unnecessary work, the application can respond to the cancellation request. 4) PUT & Patch • PUT (Full Replace): Replaces the entire resource. • You must send all fields, even unchanged ones. • Missing fields are overwritten to null or defaults. • Idempotent: Running it multiple times yields the exact same state. • Analogy: Buying a completely new phone to replace your old one. • PATCH (Partial Update): Modifies only specific fields. • You only send the fields you want to change. • Omitted fields are left untouched. • Not strictly idempotent: Repeated requests can cause sequential changes (e.g., adding to a list). • Analogy: Putting a new case on your existing phone.

5) handle concurrent request in update

• Optimistic concurrency control - with concurrency token such as RowVersion and Handle DBUpdateConcurrencyException appropriately.

• Pessimistic concurrency control (Locking) - UPLOCK

---

2) Pagination

using Entity framework core, pagination falles into two strategies: - Offset pagination: (using Skip() and take()) - Keyset/Cursor-based pagination : (using a refrence value and where())

i) Offset pagination (Skip() & Take())

This approch takes a PageNumber and a PageSize. it calucalte how many recoreds to bypass and then grabs the required chunk.

[HttpGet("offSet")]
public async Task<IActionResult> GetPagedOrders ([FromQuery] int pageNumber =1,[FromQuery] int pageSize=10){
  if(pageSize>100) pageSize = 100;
  if(pageNumber<1) pageNumber=1;
 var query = await _context.Orders.OrderBy(o=>o.Id).Skip((pageNumber-1)*pageSize).Take(pageSize).ToListAsync();

 return Ok(query);
}

• SQL Translation: OFFSET N ROWS FETCH NEXT M ROWS ONLY OR LIMIT M OFFSET N
• Downside: Performance degrades drastically : on deep pages. To execute Skip(100000000), the databse engine must scan and discard the million records before returning the pageSize.

ii) Keyset (Cursor-based) Pagination

insted of instructing the database to skip a specific row count, this approch queries for records that the chronologically follow the last seen record from the previous batch.

it filters on unique, sequential column (like an Id or a CreatedAt timestamp combined with a tie-breaking Id)

[HttpGet("offSet")]
public async Task<IActionResult> GetPagedOrders ([FromQuery] int? lastId,[FromQuery] int pageSize=10){
  if(pageSize>100) pageSize = 100;
  
  IQueryable<Order> query = _context.Orders;

  // Direct database index seek instead of an arbitrary skip 
  if(lastId.HasValue){
    query = query.Where(o=> o.Id > lastId.Value);

    // Take one extra item to evaluate if there is a subsequential page
    var items = await query.OrderBy(o=> o.Id)
                .Take(pageSize +1)
                .ToListAsync();
    var hasNextPage = item.count>pageSize;
    if(hasNextPage){
        items.RemoveAt(items.count -1);
         // Discard the extra indicator row 
    }
    var nextCursor = hasNextPage? items.LastOrDefault()?.Id: null;

    return Ok(new {Data = items, NextCursor = nextCursor});
  }
}

Direct Strategy Comparison

Feature	Offset Pagination (Skip/Take)	Keyset / Cursor Pagination
Database Execution	Scans and discards all skipped rows (OFFSET)	Performs an index lookup (WHERE)
Deep Page Performance	Degrades exponentially as the page index deepens	Constant O(1) time regardless of depth
Random Page Access	Supported (e.g., jump directly to Page 45)	Unsupported (Sequential Next/Previous only)
Resilience to Changes	Poor (Items shift, causing duplicates or omissions)	High (Stays stable during concurrent mutations)
Multi-Column Sorts	Trivial (Simply append .ThenBy())	Highly complex (Requires composite WHERE clauses)

5) How to handle concurrency update in C# web api

• there are two ways
  • Optimistic Concurrency control (recommended)
  • Pessimistic Concurrency control (Lock)

Optimistic Concurrency control (recommended)

it works with concurrency token such as RowVersion and Handle DBUpdateConcurrencyException appropriately. (eg: 90% + of standard web apps and crud interface)

step 1:

• Add concurrency token (RowVersion) in entity, this maps to an automatically managed byte[] array.

public class Product{
    public int Id {get; set;}
    public string Name{get;set;}
    Public int Price {get; set;}
    // The database will auto-increment this bytes array on every update 
    [Timestamp]
    public byte[] RowVersion {get; set;}
}

(Alternatively. if we use a database like PostgreSQL or MySQL, we can use a guid or uint property mapped with the [concurrencyCheck] attribute or EF core's .IsRowVersion() Fluent API).

step 2:

Handle the Exception in Controller

• When an update confict happens, EF core throw a DbUpdateConcurrencyException we must catch this exception and return in HTTP 409 Conflict status code to the client.

[HttpPut("{id}")]
public async Task<IActionResult> UpdateProduct(int id, [FromBody] ProductDto dto){
    var product = await _context.Products.FindAsync(id);
    if(product==null) return NotFound();

    // 1. Map client to change
    product.Name = dto.Name;
    product.Price = dto.Price;
    // 2. Attach client original rowVersion to detect if another requst change it
    _context.Entry(product).Property(p=> p.RowVersion).OriginalValue = dto.RowVersion;

    try{
        await _context.SaveChangesAsync();
        return NoContent();
    }
    catch( DbUpdateConcurrencyException ex){
        //3. Resolve confilict: fatch what is currently in the DB
        var databaseValue = await ex.Entery.Single().GetDatabaseValuesAsync();
        if(databaseValue == null){
            return NoFound("The product was deleted by another user");
        }
        // Return 409 conflict along the fresh database value so the client can merge
        return StatusCode(StatusCodes.Status409Conflict, databaseValue.ToObject());
    }
}

Pessimistic Concurrency control(locking)

Use this option only for high-risk operations where conflicts are frequent or unacceptable (eg. banking/financial transactions, stock inventory booking.)it locks the database row until API transation completes, forcing another request to wait.

in EF core by starting an explicit transation and applying an update lock via row SQL extension

using var transation = await _context.Database.BeginTransactionAsync();

// use sql Server's UPLOCK hit to lock to the row during the read phase 
var product = await _context.Products.FromSQLRaw("select * from products with ( UPLOCK) where Id = {0}", id).SingleOrDefault();

// perform modification
product.price = newPrice;

await _context.SaveChangesAsync();
await transation.CommitAsync(); // the lock is released here