C#, .NET and SQL Complete Interview Guide

1. C# Core Mechanics

What is a delegate, and why use one?

⚙️ Functionality (What it does)

A delegate is a type-safe function pointer. It is a variable that holds a method instead of a plain value. Because it acts like a standard variable, you can pass a method as a parameter into other functions, store it, or swap it dynamically at runtime.

💡 Simple Analogy

Think of a delegate like a universal remote control cable plug. You can plug it into an email sender, a text messaging sender, or a printer. The main control engine doesn't care what happens inside the machine; it just triggers whatever machine is currently plugged into the wire.

public delegate void NotificationHandler(string message);

public class OrderProcessor 
{
    public void Process(int orderId, NotificationHandler notify) 
    {
        // Core business logic goes here...
        
        // Fire whatever method is plugged into the delegate
        notify?.Invoke($"Order {orderId} processed successfully.");
    }
}

// Usage:
NotificationHandler h = NotificationServices.SendEmail;
h += NotificationServices.SendSMS; // Multicast: triggers both actions!
new OrderProcessor().Process(42, h);

🌍 Real-World Use Case

Payment Gateways: When building a checkout system, passing a payment processor delegate allows you to plug in Stripe, PayPal, or Apple Pay dynamically without writing complex if/else statements inside your core checkout engine.

Action vs Func vs Predicate

⚙️ Functionality (What it does)

Instead of declaring custom public delegate signatures manually, .NET provides built-in shortcuts. These are ready-made generic delegates designed to handle almost any method signature shape.

Action<string> log = s => Console.WriteLine(s);
Func<int, int, int> multiply = (x, y) => x * y; // Returns an int
Predicate<int> isAdult = age => age >= 18; // Returns true/false

🌍 Real-World Use Case

Used heavily inside LINQ extensions. For example, .Where() accepts a Predicate to test rows, while .Select() takes a Func to alter and map objects from one model shape into another.

What are tuples, and when do you use them instead of a class?

⚙️ Functionality (What it does)

A ValueTuple is a lightweight, temporary value type container. It groups data elements together on the fly without forcing you to write a separate class file. It is best used for sending multiple values out of private local routines.

(bool success, string message) ValidateLogin(string username, string password) 
{
    return username == "admin" ? (true, "Success") : (false, "Denied");
}

// Usage (Deconstruction)
var (isOk, statusMsg) = ValidateLogin("admin", "pass");

⚖️ When to use what?

Use Tuples for internal, short-lived operations. Use Classes or Records for your public API contracts, database objects, and structures that require long-term lifespan or specific behavior logic.

var vs dynamic

⚙️ Functionality (What it does)

var handles type checking at compile time. The compiler looks at the expression, locks in the true type, and keeps complete IntelliSense support. dynamic shifts type checking entirely to runtime. The compiler stays quiet, but typos can cause immediate application crashes during active execution.

var age = 30; // Compiled permanently as an integer.
// age = "thirty"; // Compile error!

dynamic variableData = 30;
variableData = "thirty"; // Perfectly fine at runtime!
// variableData.InvalidMethod(); // Compiles fine, but crashes your app when executed.

🌍 Real-World Use Case

Use var for 99% of regular development. Use dynamic explicitly when dealing with raw unmapped JSON strings, scripting integrations, or interacting with legacy Microsoft Office COM models.

const vs readonly

⚙️ Functionality (What it does)

Both keywords create unchangeable fields, but they evaluate at different application stages. const is hardcoded at compile time, meaning it can never look up dynamic values like system times. readonly resolves at runtime, allowing its value to be set inside a class constructor code block.

public class Settings 
{
    public const int MaxRetries = 3; // Fixed forever at compilation
    public readonly DateTime BootTime;

    public Settings() {
        BootTime = DateTime.Now; // Allowed because it resolves at runtime
    }
}

🌍 Real-World Use Case

Use readonly to protect class fields that are populated via Dependency Injection (like logging engines or database contexts) so they cannot be overwritten mid-operation.

Primitive vs Non-Primitive Types

⚙️ Functionality (What it does)

Primitives (like int, bool, double) hold their true data values directly inside their own memory location (the stack when local). Non-primitives (like class, array, interface) are reference types. The variable simply holds a memory address pointer on the stack, while the true complex data payload rests on the managed heap.

2. Memory Internals and LINQ

Stack vs heap?

⚙️ Functionality (What it does)

The Stack is an ultra-fast, structured memory system operating on a Last-In, First-Out (LIFO) plan. It manages execution tracking and value types, automatically wiping clean the second a method ends. The Heap is a large, shared playground for complex reference objects. It stays active until scanned and handled by the automated Garbage Collector.

💡 Simple Analogy

Think of the stack as your immediate desktop surface where you put active worksheets. The heap is the large storage room downstairs. You can house huge boxes down there, but you need an index tracking card (a pointer) on your desktop to find them.

What are boxing and unboxing?

⚙️ Functionality (What it does)

Boxing takes a raw value type and wraps it inside an object container on the heap. Unboxing casts it back out to the stack. This pattern causes major memory friction because every boxed instance demands a new heap memory allocation.

int balance = 500;
object box = balance; // Boxing happens here (Heap allocation)
int original = (int)box; // Unboxing happens here

// ❌ Avoid legacy styles that box values:
var oldCollection = new System.Collections.ArrayList();
oldCollection.Add(100); 

//  Use modern generic tools instead:
var clearCollection = new List<int>();
clearCollection.Add(100); // Fast, zero boxing!

How does the Garbage Collector work?

⚙️ Functionality (What it does)

The Garbage Collector automatic sweep goes through three primary stages: Mark (checks application root pointers to identify active rows), Sweep (removes unreferenced items), and Compact (shuffles live blocks together to eliminate gaps).

Note: Large Items exceeding 85,000 bytes go straight to a dedicated Large Object Heap (LOH) which skips standard compaction cycles to save processing cycles.

Can .NET leak memory?

⚙️ Functionality (What it does)

Yes. If a short-lived component hooks up to an event exposed by a long-term running infrastructure service but fails to unhook itself, the long-term root service retains that active memory address reference pointer, blocking the GC from reclaiming the memory space.

public class ViewTab : IDisposable 
{
    private readonly GlobalEventBroker _broker;
    public ViewTab(GlobalEventBroker broker) {
        _broker = broker;
        _broker.RefreshRequested += OnRefresh; // Link established
    }
    
    public void Dispose() {
        _broker.RefreshRequested -= OnRefresh; //  Snapped! Prevents a leak.
    }
    private void OnRefresh(object s, EventArgs e) {}
}

IDisposable vs Finalizer

⚙️ Functionality (What it does)

IDisposable offers explicit, immediate cleanup for external unmanaged items (such as databases or network connections) using a using block structure. A Finalizer is an unguided fallback safety net called by the GC at an unexpected future point if a developer forgets manual tracking.

public class ResourceLink : IDisposable 
{
    private bool _disposed;
    
    public void Dispose() {
        Dispose(true);
        GC.SuppressFinalize(this); // Skips unnecessary finalizer queue tasks
    }
    protected virtual void Dispose(bool disposing) {
        if (!_disposed) {
            if (disposing) { /* Clear managed objects */ }
            /* Clear unmanaged native pointers here */
            _disposed = true;
        }
    }
    ~ResourceLink() => Dispose(false); // Safety net rule
}

LINQ LEFT OUTER JOIN

⚙️ Functionality (What it does)

A Left Outer Join fetches every row from the primary source, alongside matching elements from the child source. If the child source lacks matching records, it populates a null fallback placeholder so parent rows aren't stripped out.

var leftJoin = from c in customers
               join o in orders on c.Id equals o.CustomerId into groupData
               from subOrder in groupData.DefaultIfEmpty()
               select new { c.Name, Total = subOrder?.Total ?? 0 };

IEnumerable vs IQueryable

⚙️ Functionality (What it does)

IEnumerable targets local memory tasks. Against databases, it downloads the whole target table over the network, running filters inside application memory. IQueryable constructs expression trees that translate into raw SQL scripts executed directly on the database engine server.

// ❌ BAD: Pulls entire table into memory first
var badSearch = db.Logs.AsEnumerable().Where(l => l.Level == "Error").Take(5);

//  GOOD: Database processes request, returns 5 rows
var goodSearch = db.Logs.Where(l => l.Level == "Error").Take(5).ToList();

3. SQL: Joins, Indexes, and Window Functions

SQL LEFT JOIN and Orphans (Anti-Joins)

⚙️ Functionality (What it does)

A LEFT JOIN shows all left table rows plus right table matching records. By introducing a WHERE right.Key IS NULL clause, you filter out rows that have matches, isolating orphan data records.

SELECT c.Id, c.Name 
FROM Customers c 
LEFT JOIN Orders o ON o.CustomerId = c.Id 
WHERE o.Id IS NULL; -- Catches customers who have zero orders

🌍 Real-World Use Case

System Cleanup: Isolating abandoned shopping carts or removing user accounts that haven't generated any transaction activity inside your logs.

Non-Clustered, Key Lookup, and Covering Indexes

⚙️ Functionality (What it does)

A Non-Clustered Index is a separate ledger pointing back to raw data coordinates. If a query requests fields outside this index scope, the engine must make an extra trip to pull those data pieces, causing a slow Key Lookup. A Covering Index completely eliminates this extra lookup by attaching extra columns directly to the index using an INCLUDE clause.

CREATE NONCLUSTERED INDEX IX_Orders_Cust 
ON Orders (CustomerId) 
INCLUDE (OrderDate, Total); -- Covers queries completely, skipping extra lookup loops.

Deleting Duplicates without a Primary Key

⚙️ Functionality (What it does)

You can leverage a Common Table Expression (CTE) and a ROW_NUMBER() partition to group identical rows together, assigning sequential numbers to them, and then deleting any entries numbered greater than 1.

WITH CTE_Clean AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email, Name ORDER BY (SELECT NULL)) AS RowNum 
    FROM Users
)
DELETE FROM CTE_Clean WHERE RowNum > 1;

ROW_NUMBER vs RANK vs DENSE_RANK

⚙️ Functionality (What it does)

These ranking formulas change how duplicate ties are prioritized inside data row sequences.

CROSS APPLY vs OUTER APPLY

⚙️ Functionality (What it does)

Both execute a subquery or table function for each row in the parent table. CROSS APPLY matches INNER JOIN behavior by removing parent rows that yield no subquery results. OUTER APPLY mimics a LEFT JOIN, retaining parent entries with NULL values if the subquery returns empty.

SELECT c.Name, x.Total 
FROM Customers c 
OUTER APPLY (SELECT TOP 1 Total FROM Orders o WHERE o.CustomerId = c.Id ORDER BY Total DESC) x;

🌍 Real-World Use Case

Top N Reports: Generating a list of all clients alongside their single highest transaction value item.

WHERE vs HAVING

⚙️ Functionality (What it does)

WHERE filters raw data records before grouping tasks execute. HAVING filters summary groups after GROUP BY aggregations are processed.

SELECT CustomerId, SUM(Total) 
FROM Orders 
WHERE OrderDate >= '2026-01-01' -- Pre-filter raw entries
GROUP BY CustomerId 
HAVING SUM(Total) > 1000; -- Post-filter aggregated groups

4. OOP and Design Patterns

Abstract Class vs Interface

⚙️ Functionality (What it does)

An Abstract Class defines core identity characteristics (an "is-a" relationship), allowing you to share fields, state variables, and constructor logic across child objects (limited to single inheritance). An Interface defines peripheral capability contracts (a "can-do" relationship), establishing method blueprints without containing instance state variables (supporting multiple implementations).

public abstract class Vehicle {
    public string Model { get; }
    protected Vehicle(string model) => Model = model;
    public abstract void Move();
}
public interface IFlyable { void TakeOff(); }

public class Airplane : Vehicle, IFlyable {
    public Airplane(string m) : base(m) {}
    public override void Move() => Console.WriteLine("Rolling down runway");
    public void TakeOff() => Console.WriteLine("Ascending into sky");
}

OOP Keyword Modifiers

• abstract: An incomplete template design that cannot be instantiated via the new keyword.
• sealed: Locks down inheritance capabilities, preventing other classes from extending it.
• static: Binds elements directly to the class blueprint level; no instances can be constructed.
• partial: Splits a single class across multiple files, which are merged together during compilation.
• record: A specialized immutable data type featuring built-in value-equality comparisons.

Thread-Safe Singleton Pattern

⚙️ Functionality (What it does)

Ensures that exactly one instance of a class is ever constructed across the application runtime, utilizing double-check locking to eliminate threading conflicts without introducing performance bottlenecks.

public sealed class SystemCache 
{
    private static volatile SystemCache _instance;
    private static readonly object _lock = new();
    
    private SystemCache() {} // Blocks external creation
    
    public static SystemCache Instance {
        get {
            if (_instance == null) { // First check: Skips locking if already built
                lock (_lock) {
                    if (_instance == null) { // Second check: Safely creates instance
                        _instance = new SystemCache();
                    }
                }
            }
            return _instance;
        }
    }
}

Factory Method vs Abstract Factory

⚙️ Functionality (What it does)

Factory Method uses a single creation method to return a single object instance based on subclass decisions. Abstract Factory orchestrates a suite of multiple factories to build out a cohesive family of related, unlinked components (like generating Mac menus, Mac input buttons, and Mac scrollbars together).

5. Azure Integration with .NET

Cosmos DB Best Practices

⚙️ Functionality (What it does)

To maximize performance in Cosmos DB, you must avoid exhausting system ports, optimize query network routing, and manage your Request Unit (RU) costs efficiently.

• Register the CosmosClient object as a permanent Singleton instance to protect your application from socket exhaustion.
• Enable ConnectionMode.Direct settings to bypass gateway proxies and connect straight to partition backends via raw TCP.
• Prioritize Point Reads (searching using both the unique ID and the Partition Key together) over standard SQL string queries to optimize RU consumption.

// Safe setup routine
builder.Services.AddSingleton(new CosmosClient(connectionString, new CosmosClientOptions { 
    ConnectionMode = ConnectionMode.Direct 
}));

// Most cost-effective point read retrieval style
var record = await container.ReadItemAsync<User>(id, new PartitionKey(userCountry));

Azure Service Bus: Queues vs Topics

⚙️ Functionality (What it does)

Queues operate on a one-to-one delivery plan; exactly one consumer processes each message. Topics provide a one-to-many publish-and-subscribe pipeline, broadcasting copies of a single message out to multiple subscribers simultaneously.

⚠️ The Safe Processing Routine

Disable the AutoCompleteMessages parameter. Explicitly acknowledge and call CompleteMessageAsync **only after** your database updates have been committed to disk. If your application crashes mid-process, the unacknowledged message safely returns to the queue instead of being lost forever.

var processingOptions = new ServiceBusProcessorOptions { AutoCompleteMessages = false };
processor.ProcessMessageAsync += async args => {
    await ApplyChangesToSqlDatabase(args.Message); // Step 1: Save data first
    await args.CompleteMessageAsync(args.Message);  // Step 2: Clear from queue last
};

Azure Functions Isolated Worker Model

⚙️ Functionality (What it does)

This design splits your custom function logic out into a distinct console execution process entirely separate from the underlying Azure hosting system process. This isolation gives you full control over application dependencies and allows you to adopt new versions of .NET immediately.

Crucial Rule: Keep functions completely stateless so the automated consumption hosting plans can scale out to many instances during traffic spikes without creating data sync conflicts.

var appHost = new HostBuilder()
    .ConfigureFunctionsWorkerDefaults()
    .ConfigureServices(services => services.AddTransient<IEmailService, EmailService>())
    .Build();
appHost.Run();