Conventional vs. attribute routing?

With conventional routing, you map out all your URL patterns in one central route table. Attribute routing, on the other hand, lets you drop those rules directly onto the controller and action itself. The examples here use older tech like ASP.NET MVC 5 and Web API 2, but don't worry—the exact same concept applies to ASP.NET Core.

Conventional routing:-

Patterns are registered once at startup file and apply to many controllers by naming convention.

public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });
}

/Products/Details/5 goes to ProductsController.Details(5). Add a new controller and its actions follow the same pattern with no extra routing code.

Attribute routing:-

The URL is declared next to the code that handles it.

// RouteConfig: enable it BEFORE the conventional routes
routes.MapMvcAttributeRoutes();
// Web API 2: config.MapHttpAttributeRoutes();

[RoutePrefix("orders")]
public class OrdersController : Controller
{
    [Route("")]                              // /orders
    public ActionResult Index() { return View(); }

    [Route("{id:int}")]                      // /orders/42
    public ActionResult Details(int id) { return View(); }

    [Route("{id:int}/items/{itemId:int}")]   // /orders/42/items/7
    public ActionResult Item(int id, int itemId) { return View(); }
}

Differences:-

	Conventional	Attribute
Where defined	Central route table (RouteConfig)	On controllers and actions
Style	One pattern serves many controllers	One URL per action, explicit
URL flexibility	Best for uniform shapes like {controller}/{action}/{id}	Handles irregular, nested, or custom URLs easily
Readability	You must check the route table to know a URL	The URL is visible right on the action
Maintenance	Change one rule to affect many actions	Edit each action, but changes stay local
Refactoring risk	Renaming a controller or action changes its URL	URLs stay stable when you rename methods
Constraints	Defined in the route's constraints object	Inline, such as {id:int}, {name:alpha}
Order	First match in the table wins, so order matters	Rarely order-sensitive, but ambiguity still errors
Best for	Classic MVC sites with page-like URLs	REST APIs, nested resources, SEO-friendly URLs

Why attribute routing is often preferred for APIs

• Nested resources such as /customers/{customerId}/orders/{orderId} are awkward to express with a global pattern.
• Stable public URLs. Renaming GetOrder to FetchOrder does not change /orders/{id}.
• Optional parameters and defaults can be written inline: [Route("archive/{year:int?}")].
• Route names can be set for URL generation: [Route("{id}", Name = "OrderById")].
• Multiple URLs for one action are possible by stacking attributes.

When conventional routing still fits

• A traditional content site where nearly every page follows {controller}/{action}/{id}.
• Many controllers that should behave uniformly, with no need to repeat a route on each one.
• A default homepage and catch-all fallbacks in a single place.

You can mix both

A common setup uses attribute routes for specific or API-style URLs and a conventional default route for everything else. Register attribute routes first so they take priority:

routes.MapMvcAttributeRoutes();   // specific routes first

routes.MapRoute(                  // general fallback last
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });

Common mistakes

• Forgetting MapMvcAttributeRoutes(). The [Route] attributes are silently ignored.
• Calling it after the conventional route. The general route can catch requests first.
• Two actions matching the same URL and verb. This throws an "ambiguous match" error. Use constraints or different templates.
• Missing [RoutePrefix] or typos in prefixes. URLs end up different from what you intended.
• Mixing with Web API. Web API has its own route table, so MapHttpAttributeRoutes() is separate from MapMvcAttributeRoutes().

In ASP.NET Core

The same two ideas exist. Conventional routing uses MapControllerRoute, and attribute routing uses [Route], [HttpGet("...")], and similar attributes. One key difference is that in Core, an action that has route attributes is not reachable through conventional routes. Each action uses one style or the other.

Which one to choose?

• Building an API? Use attribute routing.
• Classic MVC site with uniform URLs? Conventional routing is fine and less repetitive.
• Need both? Use attribute routes for the special cases and a conventional default as the fallback.

Write a query to perform a conditional aggregation (count males and females in each department).

To perform conditional aggregation in SQL, you combine the SUM() or COUNT() aggregate functions with a CASE WHEN statement. This allows you to inspect row attributes individually and increment separate totals within the same GROUP BY execution.

The Query

Assuming your table is named employees and contains columns for department and gender, here is the standard, optimized SQL query:

sql

SELECT 
    department,
    -- Method 1: SUM with CASE WHEN (Standard across all SQL flavors)
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count,
    
    -- Method 2: COUNT with CASE WHEN (Alternative syntax)
    COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS alt_male_count,
    COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS alt_female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

How It Works

• The CASE Evaluator: For every row processed within a department, the CASE statement inspects the value of the gender column.
• SUM(CASE ... THEN 1 ELSE 0): If the condition matches, it outputs a 1. If it doesn't match, it outputs a 0. The SUM() function then tallies up these numbers, effectively calculation a count.
• COUNT(CASE ... THEN 1 END): The COUNT() function ignores NULL values. If the condition matches, it returns a 1. If it doesn't match, it implicitly returns NULL (since there is no ELSE clause), so it isn't counted.

---

Complete Playground Script (Schema + Test Data)

You can use this complete script to build the schema, load data, and instantly verify the execution logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, gender) VALUES
(1, 'Alice', 'Engineering', 'Female'),
(2, 'Bob', 'Sales', 'Male'),
(3, 'Charlie', 'Engineering', 'Male'),
(4, 'Diana', 'Engineering', 'Female'),
(5, 'Evan', 'Sales', 'Male'),
(6, 'Fiona', 'Marketing', 'Female'),
(7, 'George', 'Sales', 'Male'),
(8, 'Hannah', 'Marketing', 'Female');

-- 3. EXECUTE CONDITIONAL AGGREGATION
SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

Expected Query Output

department	male_count	female_count
Engineering	1	2
Marketing	0	2
Sales	3	0

What is Managed Identity, and why do we use it?

Managed Identity

A managed identity is an identity in Microsoft Entra ID that Azure creates and manages for an Azure resource (an App Service, Function App, VM, Container App, AKS workload, and so on). The resource uses it to authenticate to other Azure services without any credentials in your code or config. Azure handles the credential behind the scenes: it creates it, stores it, and rotates it automatically, and you never see or handle the secret.

The problem it solves

Normally, to call something like Key Vault, SQL, or Storage, your app needs a credential: a connection string, API key, or client secret. That creates a chain of problems:

• The credential has to be stored somewhere (config, CI variables, a vault).
• It can leak through source control, logs, or screenshots.
• It expires and needs rotation, which often causes outages when someone forgets.
• To read the secret from Key Vault, you need another credential (the "secret zero" problem).

Managed identity removes the credential from the picture entirely. The identity is the resource, and access is granted through RBAC.

How it works

1. You enable a managed identity on the resource.
2. Azure registers a service principal for it in Entra ID.
3. You grant that identity a role on the target resource (e.g., Key Vault Secrets User, Storage Blob Data Reader).
4. At runtime, your code asks a local token endpoint on the host (the Instance Metadata Service, or the App Service identity endpoint) for an access token for the target resource.
5. Azure issues a short-lived token, and your code presents it to the target service, which checks the RBAC assignment.

The token request is only possible from code running on that resource, which is why no secret is needed. The Azure SDKs do all of this for you:

var credential = new DefaultAzureCredential();

var blobs = new BlobServiceClient(
    new Uri("https://mystorage.blob.core.windows.net"), credential);

DefaultAzureCredential uses the managed identity when running in Azure, and falls back to your az login or Visual Studio sign-in locally, so the same code works in both places.

Two types

	System-assigned	User-assigned
Lifecycle	Tied to the resource; deleted with it	Standalone resource with its own lifecycle
Sharing	One resource only	Can be attached to many resources
Best for	Simple, single-resource scenarios	Sharing one identity across apps, pre-provisioning access before deployment, or keeping access stable when you recreate resources

When using a user-assigned identity, tell the SDK which one to use, since a resource can have several:

var credential = new DefaultAzureCredential(
    new DefaultAzureCredentialOptions { ManagedIdentityClientId = "<client-id>" });

Setting it up

# System-assigned
az webapp identity assign --name my-app --resource-group my-rg

# User-assigned
az identity create --name my-identity --resource-group my-rg
az webapp identity assign --name my-app --resource-group my-rg \
  --identities <identity-resource-id>

# Grant access
az role assignment create \
  --assignee <principalId> \
  --role "Storage Blob Data Reader" \
  --scope <storage-account-resource-id>

Why use it

• No secrets to manage: nothing to store, leak, or rotate by hand.
• Better security posture: short-lived tokens, and access governed by RBAC with least privilege and a clear audit trail in Entra ID and Azure Monitor.
• Less operational risk: no expired-credential outages.
• Simpler code and pipelines: no secret plumbing between environments.
• Free: there's no extra charge for managed identities.

Limitations to know about

• It only works for Azure resources (and Azure Arc-enabled servers). Code running on-premises or on a laptop can't use one directly; use DefaultAzureCredential fallbacks, workload identity federation, or service principals there.
• The target service must support Entra authentication. Most Azure services do (Key Vault, Storage, Service Bus, SQL, Cosmos DB, Event Hubs), but some third-party services or older features still need keys.
• Role assignments take a few minutes to propagate, and tokens are cached, so changes aren't always instant.
• It authenticates the resource, not an end user. If you need per-user access, you need user sign-in (OAuth/OIDC) instead.
• For Azure SQL, you also have to create a database user for the identity (CREATE USER [my-app] FROM EXTERNAL PROVIDER) and grant it database roles.

Related: workload identity

For apps outside Azure (GitHub Actions, Kubernetes on other clouds), workload identity federation gives a similar credential-free experience by trusting tokens from an external identity provider. It's what the azure/login@v2 OIDC setup in a GitHub Actions workflow uses.

What is Routing?

Routing is the process of deciding where to send something should go to next, based on where it is trying to reach. A router (or routing system) looks at the destination, checks a set of rules, and forwards the item along the best matching path.

A simple analogy

Think of a postal sorting office. A letter arrives with an address, and the clerk checks the rules ("anything for this postcode goes to that truck") and sends it on. No single clerk needs to know about the whole journey, only the next step. Routing in computing works the same way.

The two main kinds

1. Network routing: moving data packets between the networks.

• Each packet carries a destination IP address.
• A router checks its routing table and forwards these packet to the next hop, such as another router or the final machine.
• This is how the data crosses your home network, your ISP, and the internet to reach a server.
• If no specific rule matches, a default route sends the packet to a general gateway.

2. Application (URL) routing: moving web requests to the right code.

• The destination is a URL such as '/orders/42'.
• A routing system matches that URL pattern to a specific handler, such as a function, controller, or page.
• This is how the one web application serves many different pages and API endpoints.

Request: GET /orders/42
Rule:    /orders/{id}  ->  OrdersController.Details(id)
Result:  Details(42) runs

The core parts

Part	What it is
Destination	The address, IP or URL, the item is heading to
Rules / table	The list of patterns and where each one leads
Matching	Picking the best rule, usually the most specific one
Next hop / handler	Where the item is sent, a router or a piece of code
Default	A fallback for anything with no matching rule

Why we need it

• Scale: The senders don't need to know every path to every destination.
• Flexibility: you can change where the traffic goes by editing a rule, not every sender.
• Control and security: you can force traffic through a firewall or block certain paths.
• Clean design: web URLs stay readable and independent of how the code is organized.
• Efficiency: traffic takes the best available path.

How do you secure an Azure Function?

Securing an Azure Function

Securing a function is layered: control who can call it, what it can reach, and where its secrets live. Start with the first three layers, then add the rest as your requirements grow.

1. Authenticate and authorize callers (inbound)

Function keys (the default for HTTP triggers). AuthorizationLevel.Function requires a key in the x-functions-key header (or ?code=). Keys are simple but they're shared secrets with no identity attached, so treat them as a minimum, not a full solution. Avoid AuthorizationLevel.Anonymous unless something else (such as API Management or Easy Auth) is enforcing access in front of it. Note that Admin level uses the master key, which grants access to everything, so never share it.

App Service Authentication ("Easy Auth") with Microsoft Entra ID. This is the recommended approach for user- or service-facing APIs. The platform validates tokens before your code runs. Enable it under Settings → Authentication and set unauthenticated requests to return 401:

az webapp auth update --name my-func-app --resource-group my-rg \
  --enabled true --action Return401

You then add an Entra ID identity provider (via the portal or az webapp auth microsoft update) with your app registration. For finer control, check roles or scopes in code from the validated claims.

API Management in front. Put APIM in front of the function to get JWT validation, rate limiting, IP filtering, and subscription keys, and then restrict the function so it only accepts traffic from APIM.

Webhooks from third parties (Stripe, GitHub): verify the provider's signature header inside your function rather than relying only on a key.

2. Restrict the network

• Access restrictions: allow only specific IP ranges, service tags, or VNets (e.g., only your APIM or Front Door).
• Private endpoints: make the function reachable only from inside your VNet (Premium, Flex Consumption, or Dedicated plans).
• Front Door or Application Gateway with WAF: adds DDoS and OWASP protection, and you can lock the function to accept traffic only from them.
• VNet integration (outbound): lets the function reach private resources like databases and Key Vault over the VNet, so those can have their public access turned off.

3. Use managed identity instead of connection strings (outbound)

Give the function a managed identity and grant it RBAC roles on the resources it needs. This removes stored credentials entirely.

az functionapp identity assign --name my-func-app --resource-group my-rg

// In the isolated worker, no keys required
var client = new BlobServiceClient(
    new Uri("https://mystorage.blob.core.windows.net"),
    new DefaultAzureCredential());

Triggers and bindings can use identity-based connections too. For example, with Service Bus you set ServiceBusConnection__fullyQualifiedNamespace instead of a connection string, and grant the identity the Azure Service Bus Data Receiver role. The same pattern works for the function's own host storage account (AzureWebJobsStorage__accountName), which is worth doing so you can disable shared key access on that account.

4. Protect secrets

• Store remaining secrets in Key Vault and use Key Vault references in app settings: @Microsoft.KeyVault(VaultName=my-vault;SecretName=MySecret).
• Never commit secrets to source control or put them in local.settings.json files that get checked in (that file is for local development only).
• Rotate function keys periodically, and use per-client keys or host keys rather than distributing the default key broadly.

5. Harden the transport and the platform

• HTTPS only: az functionapp update --set httpsOnly=true
• Minimum TLS version 1.2, and disable FTP/FTPS deployment where you can (--ftps-state Disabled).
• Disable SCM basic auth and deploy using Entra identities or federated credentials from CI/CD.
• CORS: list only the exact origins that need browser access, and never use * for authenticated APIs.
• Keep the runtime current: use a supported .NET and Functions runtime version, and update dependencies.

6. Code-level hygiene

• Validate and sanitize all inputs, including queue and blob payloads, not just HTTP.
• Don't return stack traces or internal details in error responses.
• Apply least privilege. A function that only reads a queue shouldn't have write access to the storage account.
• Avoid logging sensitive data (tokens, PII, secrets).

7. Monitor and detect

• Enable Application Insights and diagnostic logs to Log Analytics.
• Turn on Microsoft Defender for Cloud (Defender for App Service covers Function Apps) for threat detection and recommendations.
• Alert on spikes in 401/403 responses, unusual invocation counts, and failed Key Vault access.

A sensible baseline

For a typical HTTP-triggered API:

1. Enable Easy Auth with Entra ID (return 401 for unauthenticated calls).
2. Use a managed identity with least-privilege RBAC for everything the function touches.
3. Put APIM or Front Door in front and restrict inbound access to it.
4. Store leftover secrets in Key Vault, enforce HTTPS and TLS 1.2+, and disable FTP and basic auth.
5. Send logs to Application Insights and enable Defender for Cloud.

Find customers who have not made any purchase.

To find customers who have never made a purchase, you need to identify rows in your customers table that have no corresponding matching records in your orders (or purchases) table.

Here are the three most efficient SQL methods to achieve this, along with a complete schema script to test it yourself.

---

Method 1: Using LEFT JOIN (Recommended & Most Common)

This method joins both tables and filters out any rows where the order details come back empty (NULL).

sql

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Use code with caution.

Method 2: Using NOT EXISTS (Highly Efficient for Large Datasets)

This method checks every customer record and returns it only if a subquery searching the orders table yields zero results.

sql

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Use code with caution.

Method 3: Using NOT IN

A simple approach that reads like plain English. (Warning: This method will fail to return results if the orders.customer_id column contains any NULL values).

sql

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE customer_id IS NOT NULL
);

Use code with caution.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to see the queries in action:

sql

-- 1. Create Tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 2. Insert Sample Data
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'John Doe'),      -- Has placed an order
(2, 'Jane Smith'),    -- Has NOT placed an order
(3, 'Bob Johnson'),   -- Has placed an order
(4, 'Alice Williams');-- Has NOT placed an order

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(101, 1, '2026-10-25'),
(102, 3, '2026-10-26'),
(103, 1, '2026-10-27');

-- 3. Run the selection (Will return Jane Smith and Alice Williams)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Use code with caution.

Expected Query Output

customer_id	customer_name
2	Jane Smith
4	Alice Williams