Learning Objectives
By the end of this article, you'll learn how to:

• Create a file upload component in Angular
• Handle file selection using the change event
• Store the selected file in the component
• Write unit tests for file input controls
• Simulate file selection in Angular unit tests

HTML Template
<input
  id="myFile"
  type="file"
  (change)="onFileSelected()"
  #fileInput
/>

Whenever the user selects a file, the change event calls the onFileSelected() method.

Component
import { Component } from '@angular/core';

@Component({
  selector: 'app-input-file',
  templateUrl: './input-file.component.html'
})
export class InputFileComponent {

  uploadedFile!: File;

  onFileSelected(): void {

    const inputNode = document.querySelector('#myFile') as HTMLInputElement;

    if (inputNode.files && inputNode.files.length > 0) {
      this.uploadedFile = inputNode.files[0];
      console.log(this.uploadedFile);
    }

  }

}

How It Works
When the user selects a file:

• The component locates the file input element.
• The browser stores the selected file(s) in the files collection.
• The first file is assigned to the uploadedFile property.

Although this works, Angular recommends avoiding direct DOM access where possible.

Writing the Unit Test
The browser normally populates the files property, so during unit testing we need to mock it ourselves.
import { ComponentFixture, TestBed } from '@angular/core/testing';
import { InputFileComponent } from './input-file.component';

describe('InputFileComponent', () => {

  let component: InputFileComponent;
  let fixture: ComponentFixture<InputFileComponent>;

  beforeEach(async () => {

    await TestBed.configureTestingModule({
      declarations: [InputFileComponent]
    }).compileComponents();

    fixture = TestBed.createComponent(InputFileComponent);
    component = fixture.componentInstance;
    fixture.detectChanges();

  });

  it('should store the selected file', () => {

    const file = new File(
      ['Dummy Content'],
      'sample.txt',
      {
        type: 'text/plain'
      }
    );

    const input = fixture.nativeElement.querySelector('#myFile');

    Object.defineProperty(input, 'files', {
      value: [file]
    });

    input.dispatchEvent(new Event('change'));

    expect(component.uploadedFile).toEqual(file);

  });

});

Understanding the Test
Step 1: Create a Mock File
const file = new File(
  ['Dummy Content'],
  'sample.txt',
  {
    type: 'text/plain'
  }
);

This creates a fake File object that behaves exactly like a file selected by the user.

Step 2: Mock the Browser's files Property
Object.defineProperty(input, 'files', {
  value: [file]
});

Since the browser owns the files property, we replace it with our mock file during testing.

Step 3: Simulate File Selection
input.dispatchEvent(new Event('change'));

This triggers the same event that occurs when a user selects a file.

Step 4: Verify the Result
expect(component.uploadedFile).toEqual(file);

The test passes if the component correctly stores the selected file.

A Better Angular Approach
Instead of querying the DOM with document.querySelector(), Angular encourages passing the event object directly.
<input
  type="file"
  (change)="onFileSelected($event)"
/>

Component
onFileSelected(event: Event): void {

  const input = event.target as HTMLInputElement;

  if (!input.files?.length) {
    return;
  }

  this.uploadedFile = input.files[0];

}

This approach is:

• More Angular-friendly
• Easier to unit test
• Doesn't directly access the DOM
• Better for maintainability

Unit Test for the Improved Version
it('should store the selected file', () => {

  const file = new File(
    ['Angular Testing'],
    'document.pdf',
    {
      type: 'application/pdf'
    }
  );

  const event = {
    target: {
      files: [file]
    }
  } as unknown as Event;

  component.onFileSelected(event);

  expect(component.uploadedFile).toEqual(file);

});

Notice that we no longer need to manipulate the DOM. We simply create a mock event object and call the component method directly, making the unit test cleaner and easier to understand.

Conclusion
Unit testing file uploads in Angular is straightforward once you know how to mock the browser's files property. While older implementations often relied on document.querySelector(), modern Angular applications should use the event object passed by the change event. This results in cleaner code, simpler unit tests, and components that are easier to maintain.

The overall testing strategy is simple:

• Create a mock File object.
• Assign it to the input's files property (or pass it through the event object).
• Trigger the change event.
• Verify that the component stores the selected file correctly.

Following this approach will help you confidently test file upload functionality in any Angular application.

Summary
Testing file uploads in Angular involves simulating the browser's file selection behavior by creating mock File objects and triggering the change event. While directly accessing the DOM with document.querySelector() works, passing the event object to the component method is the recommended Angular approach because it improves maintainability, simplifies unit testing, and reduces coupling between the component and the DOM.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

One of the most crucial SQL Server speed optimization strategies is the use of indexes. Well-designed indexes may greatly speed up query execution and increase application responsiveness in Online Transaction Processing (OLTP) systems, where thousands of transactions may take place per second.

Indiscriminately creating indexes, however, might complicate maintenance, slow down insert and update processes, and increase storage utilization. Making indexes that minimize overhead while supporting the most popular query patterns is the aim.

In this article, you'll learn practical indexing strategies for large OLTP systems, understand different index types, analyze execution plans, and follow a structured methodology for evaluating indexing changes.

Note: This article focuses on indexing strategies and execution plan analysis. Performance improvements vary depending on workload, schema, hardware, and data distribution.

Why Indexes Matter
Without an index, SQL Server typically scans an entire table to locate matching rows.

Application
      │
      ▼
 SQL Query
      │
      ▼
Table Scan
      │
      ▼
Slow Response

With a properly designed index:

Application
      │
      ▼
 SQL Query
      │
      ▼
 Index Seek
      │
      ▼
Fast Response

An Index Seek usually requires significantly less work than a Table Scan, especially for large tables.

Clustered vs Nonclustered Indexes

SQL Server supports several index types.

Choosing the correct index depends on how the application queries the data.

Clustered Index
A table can have only one clustered index.

Example:
CREATE TABLE Orders
(
    Id INT PRIMARY KEY CLUSTERED,
    CustomerId INT,
    OrderDate DATETIME,
    Total DECIMAL(18,2)
);

Clustered indexes work well for:

• Primary keys
• Sequential inserts
• Range queries

Avoid frequently changing clustered key values because updating them affects the physical row order.

Nonclustered Index
Suppose orders are frequently searched by customer.
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

Instead of scanning the entire table, SQL Server can locate matching rows using the index.

Composite Indexes
Queries often filter on multiple columns.

Example:
SELECT *
FROM Orders
WHERE CustomerId = 10
AND OrderDate >= '2026-01-01';

A composite index is more appropriate.
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON Orders(CustomerId, OrderDate);

The order of columns is important. Place the most selective or frequently filtered column first when it aligns with your query patterns.

Covering Indexes
Consider the following query:
SELECT CustomerId,
       OrderDate,
       Total
FROM Orders
WHERE CustomerId = 10;

Instead of performing additional key lookups, include the required columns.
CREATE NONCLUSTERED INDEX IX_Orders_Customer
ON Orders(CustomerId)
INCLUDE (OrderDate, Total);

A covering index allows SQL Server to satisfy the query directly from the index.
Filtered Indexes

When only a subset of rows is queried frequently, use filtered indexes.

Example:
CREATE NONCLUSTERED INDEX IX_Orders_Open
ON Orders(Status)
WHERE Status = 'Open';

Filtered indexes are smaller and can improve performance for selective workloads.
Avoid Over-Indexing

Every index must be maintained during:

• INSERT
• UPDATE
• DELETE

Too many indexes can slow write operations.

Instead of indexing every column, analyze real query patterns and create only the indexes that provide measurable value.

Analyze Execution Plans
Execution plans reveal how SQL Server executes queries.

Enable an actual execution plan in SQL Server Management Studio or use:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Review execution plans for:

• Table Scan
• Index Scan
• Index Seek
• Key Lookup
• Sort
• Hash Match

A frequent Table Scan on a large table often indicates a missing or ineffective index.

Missing Index Recommendations
SQL Server may suggest missing indexes during execution plan analysis.

Example recommendation:

Missing Index:

• Orders(CustomerId)
• INCLUDE(OrderDate)

Treat these suggestions as starting points rather than automatic solutions. Evaluate each recommendation against your workload before implementing it.

Fragmentation
Over time, indexes become fragmented due to insert, update, and delete operations. Common maintenance tasks include:

• Reorganize indexes
• Rebuild indexes
• Update statistics

Regular maintenance helps SQL Server generate efficient execution plans.

End-to-End Optimization Workflow
A structured tuning process includes:

• Identify slow queries.
• Capture the execution plan.
• Review scans and key lookups.
• Create or modify indexes.
• Update statistics if necessary.
• Re-test the query.
• Monitor production performance.

This workflow helps ensure indexing decisions are based on evidence rather than assumptions.

Index Type Comparison

Performance Evaluation Methodology
The research brief mentions execution plan comparisons but does not include benchmark results. To evaluate indexing changes in your own environment:

Test Environment
Maintain consistency across benchmark runs:

• SQL Server version
• Database size
• Hardware
• Application version
• Query workload
• Test Scenarios

Compare:

• No index
• Single-column index
• Composite index
• Covering index
• Filtered index
• Metrics to Collect

Measure:

• Query execution time
• Logical reads
• Physical reads
• CPU utilization
• Execution plan cost
• Insert/update duration
• Index storage size
• Useful Tools

Useful tools include:

• SQL Server Management Studio
• Actual Execution Plans
• Query Store
• SQL Server Profiler (where appropriate)

Extended Events
SET STATISTICS IO

SET STATISTICS TIME

Validate changes using production-like data volumes rather than small development datasets.

Best Practices

• Index columns frequently used in filters and joins.
• Keep indexes as narrow as possible.
• Use covering indexes for critical read queries.
• Review execution plans before adding indexes.
• Update statistics regularly.
• Monitor index fragmentation.
• Remove unused indexes.
• Validate changes under representative workloads.

Common Mistakes

Troubleshooting
Query Still Performs a Table Scan

Verify:

• Query predicates
• Index column order
• Updated statistics
• Parameter values
• Data selectivity

A scan is sometimes the optimal choice for very small tables or non-selective queries.

Insert Operations Become Slower
Review:

• Number of indexes
• Index maintenance
• Fragmentation
• Fill factor settings

Reducing unnecessary indexes often improves write performance.

High Fragmentation
Schedule regular index maintenance and monitor fragmentation levels using SQL Server's dynamic management views.

FAQs
Should every foreign key have an index?
Not always, but indexing foreign keys is often beneficial because they are commonly used in joins and filtering operations.

How many indexes should a table have?
There is no fixed number. Create indexes that support your workload while balancing read performance against write overhead.

What is a covering index?
A covering index contains all the columns required to satisfy a query, eliminating additional key lookups.

Should I always follow SQL Server's missing index suggestions?
No. Review each recommendation carefully. Some suggested indexes may duplicate existing ones or increase write costs without providing sufficient benefit.

How often should indexes be rebuilt?
The appropriate maintenance schedule depends on workload and fragmentation levels. Monitor index health regularly rather than rebuilding on a fixed schedule without analysis.

Conclusion
Effective indexing is essential for maintaining high-performance SQL Server OLTP systems. Well-designed indexes reduce query execution time, improve scalability, and minimize resource consumption, while poorly planned indexes can have the opposite effect. By understanding index types, analyzing execution plans, monitoring fragmentation, and validating changes through structured testing, you can build indexing strategies that support both fast reads and efficient transactional workloads in production environments.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

High-Scale Enterprise Use Cases, System-Versioning Patterns, and Architectural Mechanisms

Storing a database record's current state alone is no longer adequate in contemporary corporate software engineering. While corporate operations increasingly require "time-travel" capabilities to assess historical patterns, recreate former data states, and recover from unintentional data corruptions, regulations such as HIPAA, GDPR, and SOX necessitate strict auditability.

While techniques like custom audit triggers, Change Data Capture (CDC), or event sourcing exist, SQL Server’s System-Versioned Temporal Tables provide a built-in, declarative solution. By binding a current operational table to an automated system history table, temporal tables give developers point-in-time querying capabilities with zero application-level change tracking code.

1. Engine-Level Architecture: Dual-Table Binding
A temporal table consists of two separate, physically linked storage entities managed entirely by the database engine:
Current / Primary Table (dbo.Employees): Stores active, latest-state records.

History Table (dbo.EmployeesHistory): Stores superseded or deleted historical versions of records.


1. Period Columns (SysStartTime and SysEndTime)
Temporal tables require two non-nullable datetime2 columns designated as the system period. These determine the exact UTC validity window of every row version:

• SysStartTime: The precise UTC timestamp when the row version became active.
• SysEndTime: The UTC timestamp when the row version was modified or deleted. (Active rows in the main table have SysEndTime set to 9999-12-31 23:59:59.9999999).

2. DDL Implementation & Clean Schema Design
Creating a temporal table requires declaring the period columns and attaching the SYSTEM_VERSIONING table option. Marking period columns as HIDDEN prevents them from dirtying standard application SELECT * payloads.
CREATE TABLE dbo.Employees (
    EmployeeID INT NOT NULL PRIMARY KEY CLUSTERED,
    Name VARCHAR(100) NOT NULL,
    Position VARCHAR(100) NOT NULL,
    Salary DECIMAL(12, 2) NOT NULL,
    DepartmentID INT NOT NULL,

    -- Mandatory System-Versioning Period Columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory)
);

Automatic DML Mechanics
INSERT: The record enters dbo.Employees. SysStartTime is stamped with the UTC commit time; SysEndTime is set to max value (9999-12-31). Nothing is written to dbo.EmployeesHistory.

UPDATE: SQL Server executes a two-step atomic operation:

• The existing active row is copied to dbo.EmployeesHistory, updating its SysEndTime to the current transaction timestamp.
• The row in dbo.Employees is updated in-place with new values, updating its SysStartTime to the transaction timestamp.

DELETE: The row is removed from dbo.Employees and inserted into dbo.EmployeesHistory with SysEndTime set to the deletion timestamp.

3. Querying Temporal Tables: The Time-Travel Extensions
Applications query temporal tables by adding the FOR SYSTEM_TIME clause directly after the primary table name in T-SQL. The database engine automatically generates an execution plan that performs a UNION ALL across dbo.Employees and dbo.EmployeesHistory.

A. Point-in-Time Reconstruction (AS OF)
Retrieves the exact state of a record or entire database schema at a specific historical moment.
-- What was Employee 101's salary and position on October 15, 2025?
SELECT EmployeeID, Name, Position, Salary
FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2025-10-15 09:30:00'
WHERE EmployeeID = 101;

B. Full Audit Trail (ALL)
Returns every version of a record throughout its entire lifecycle.
-- Inspect the full career and compensation history for Employee 101
SELECT
    EmployeeID,
    Position,
    Salary,
    SysStartTime AS ValidFrom,
    SysEndTime AS ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 101
ORDER BY SysStartTime ASC;

C. Interval Filtering (BETWEEN ... AND ...)

Retrieves all row versions active at any point inside a date range.
SELECT *
FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-06-30'
WHERE DepartmentID = 4;

4. How Enterprise Applications Leverage Temporal Tables
Enterprise Use Case 1: Regulatory Compliance & Zero-Code Auditing

In financial, healthcare, and e-commerce systems (governed by SOX, HIPAA, or PCI-DSS), auditing who altered critical data—and what the previous value was—is mandatory.

Legacy Approach: Writing explicit AFTER UPDATE, DELETE triggers on every table or relying on application-level logging libraries.

Temporal Solution: Enabling system-versioning provides an immutable, engine-managed audit trail. Because application code cannot modify or delete rows in the history table directly, audit data remains tamper-proof.

Enterprise Use Case 2: Slow Changing Dimensions (SCD Type 2) in BI & Data Warehousing
Data warehouses require tracking historical changes to dimensions (e.g., tracking a customer's address changes over time to attribute past sales to the correct territory).

Enterprise Pattern: Instead of building complex ETL pipelines to handle Type 2 SCD logic, business intelligence tools (like Power BI or Azure Synapse) run point-in-time joins (FOR SYSTEM_TIME AS OF FactTable.TransactionDate) directly against temporal dimension tables.

Enterprise Use Case 3: "Point-in-Time" Financial Calculations & Invoicing
Billing systems often need to re-calculate invoices based on historical pricing rules or retroactively audit customer balances.

Enterprise Pattern: A SaaS application computes usage metrics by joining transaction logs against PricingPlans FOR SYSTEM_TIME AS OF UsageDate. Even if a pricing tier changes today, historical invoice calculations stay accurate.

Enterprise Use Case 4: Instant Data Repair & Accidental Mass-Delete Recovery

If a bug or improper UPDATE statement without a WHERE clause corrupts 100,000 active customer records, traditional recovery requires restoring a full database backup to a staging server.

Temporal Solution: Engineers execute an in-place MERGE or UPDATE query bringing data directly back from the history table:

-- Restore current table state from 1 hour ago
MERGE INTO dbo.Employees AS target
USING (
    SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF '2026-08-05 08:00:00'
) AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.Position = source.Position,
        target.Salary = source.Salary;

5. Enterprise Storage & Index Optimization
Because history tables grow indefinitely as data mutates, unoptimized temporal tables can bloat database storage and degrade query performance over time.

1. Indexing the History Table
For optimal performance with AS OF and interval queries, create a Clustered Columnstore Index or a composite B-Tree index on the history table structured around the period columns:
-- Optimal B-Tree Index for Time-Travel Lookups
CREATE CLUSTERED INDEX IX_EmployeesHistory_PK
ON dbo.EmployeesHistory (SysEndTime ASC, SysStartTime ASC, EmployeeID);

2. Automated History Retention Policies
SQL Server allows setting an automatic retention policy on history tables to drop historical records older than a configured threshold:
ALTER TABLE dbo.Employees
SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.EmployeesHistory,
        HISTORY_RETENTION_PERIOD = 12 MONTHS -- Automatically purges history older than 1 year
    )
);

Conclusion
SQL Server Temporal Tables transfer the burden of state history and auditability from the application logic to the database engine. Enterprise applications use declarative, maintainable SQL code to provide point-in-time time travel, smooth compliance audits, rapid data recovery, and streamlined analytical reporting by utilizing system-versioned history tables and FOR SYSTEM_TIME syntax.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

While developing an ASP.NET Core Web API using Entity Framework Core and SQL Server, one of the most common migration errors developers encounter is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 Error Number:2, State:0, Class:20

This error usually appears while executing:
Update-Database

or

dotnet ef database update

Although the message appears to indicate a network issue, the actual root cause is often related to SQL Server configuration, an incorrect connection string, startup project selection, or Entity Framework Core design-time configuration.

This article explains the complete troubleshooting process used by professional .NET developers to diagnose and resolve this issue.

Understanding the Error
When Entity Framework Core executes a migration, it attempts to establish a connection with SQL Server.

If SQL Server cannot be reached using the configured connection string, the following exception is thrown:
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This does not necessarily mean SQL Server is unavailable. It simply means the application could not establish a connection.

Step 1 – Verify SQL Server Service
First, verify that SQL Server is installed and running.

Open Command Prompt and execute:
sc query MSSQL$SQLEXPRESS

Expected Output:
SERVICE_NAME: MSSQL$SQLEXPRESS
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If the service is stopped:
Press Win + R
Type:
services.msc

Locate SQL Server (SQLEXPRESS)
Click Start

Step 2 – Verify SQL Server Connectivity
Before debugging your application, verify that SQL Server itself is accessible.

List Installed SQL Server Instances
sqlcmd -L

Example Output:
Servers:
    DEV-SERVER\SQLEXPRESS

If no server is listed, SQL Server Browser may not be running or your SQL Server instance may not be discoverable.

Connect to SQL Server
sqlcmd -S .\SQLEXPRESS -E

or

sqlcmd -S DEV-SERVER\SQLEXPRESS -E

Successful connection:

1>

This confirms:

• SQL Server is installed
• SQL Server service is running
• Windows Authentication is working
• SQL Server accepts connections

Step 3 – Verify SQL Server Information

After connecting successfully, execute the following SQL commands in cmd .
Check Connected Server

SELECT @@SERVERNAME;
2> GO click enter
                                                                                        Output
--------------------------------------------------------------------------------------------------------------------------------
DEV-SERVER\SQLEXPRESS

Check SQL Server Version
SeLECT @@VERSION;
2> GO click enter
                                                                                        Output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
        Oct  8 2022 05:58:25
        Copyright (C) 2022 Microsoft Corporation
        Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 26200: ) (Hypervisor)

(1 rows affected)

Verify Current Database
SELECT DB_NAME();
GO

Expected:
master

List All Databases
SELECT name
2> FROM sys.databases
3> ORDER BY name;
4> GO click enter

Output
name
--------------------------------------------------------------------------------------------------------------------------------
1000SQLDB
model
msdb
SqlInDepth
SQLJourney
SQLPracticeQuery
tempdb
TestData

Verify Current Login
SELECT SYSTEM_USER;
2> GO click enter
                                                                                        Output
--------------------------------------------------------------------------------------------------------------------------------
DEV-SERVER\HP

(1 rows affected)

Verify Authentication Mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');
GO

Output:

Exit sqlcmd

EXIT

Step 4 – Verify the Connection String
A large number of SQL Server connection issues are caused by incorrect connection strings.

Example:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=DEV-SERVER\\SQLEXPRESS;Database=SampleERPDB;Integrated Security=True;TrustServerCertificate=True;MultipleActiveResultSets=True"
  }
}

Parameter Explanation

The Server value should match the value returned by:
SELECT @@SERVERNAME;
GO

Step 5 – Register DbContext Correctly
Register Entity Framework Core inside the Infrastructure layer.
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(
        configuration.GetConnectionString("DefaultConnection"),
        sql =>
        {
            sql.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName);
        });
});

Avoid multiple DbContext registrations.

Step 6 – Verify Dependency Injection
Your API should register both Application and Infrastructure services.
builder.Services.AddApplication();

builder.Services.AddInfrastructure(builder.Configuration);

This ensures the DbContext receives the correct configuration.

Step 7 – Verify Startup Project
Incorrect startup project selection is one of the most common causes of migration failures.

Startup Project
SampleERP.API

Default Project
SampleERP.Infrastructure

When using Visual Studio Package Manager Console:

• Startup Project → API
• Default Project → Infrastructure

Step 8 – Verify appsettings.Development.json
ASP.NET Core loads configuration in this order:
    appsettings.json

    appsettings.Development.json

    Environment Variables

    User Secrets

If appsettings.Development.json contains another connection string, it overrides appsettings.json.

Example:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=OLD-SERVER\\SQLEXPRESS;Database=OldDatabase;"
  }
}

Always verify both files.

Step 9 – Check Design-Time DbContext Factory
Search your solution for:
IDesignTimeDbContextFactory

or

ApplicationDbContextFactory

Example:
public class ApplicationDbContextFactory
    : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        var builder = new DbContextOptionsBuilder<ApplicationDbContext>();

        builder.UseSqlServer(
            "Server=OLD-SERVER\\SQLEXPRESS;Database=OldDatabase;");

        return new ApplicationDbContext(builder.Options);
    }
}

During migrations, Entity Framework Core uses this factory instead of Program.cs.

Step 10 – Search for Multiple UseSqlServer Registrations
Search the entire solution:
UseSqlServer(

There should ideally be only one registration.

Step 11 – Display the Active Connection String
Temporarily print the active connection string.
var connectionString =
    builder.Configuration.GetConnectionString("DefaultConnection");

Console.WriteLine(connectionString);

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});

If the printed value differs from the expected connection string, another configuration source is overriding it.

Step 12 – Verify SQL Server Browser Service
Open:
services.msc

Ensure the following service is running:

SQL Server Browser
Although optional for local development, it assists clients in locating named SQL Server instances.

SQL Server Health Check Commands
Run these commands after connecting via sqlcmd.
SELECT @@SERVERNAME;
GO

SELECT @@VERSION;
GO

SELECT SYSTEM_USER;
GO

SELECT DB_NAME();
GO

SELECT name
FROM sys.databases
ORDER BY name;
GO

If all commands execute successfully, SQL Server is healthy and accepting connections.

Common Root Causes
Most Error 40 issues are caused by one or more of the following:

• Incorrect SQL Server instance name
• SQL Server service is stopped
• Invalid connection string
• Wrong startup project
• appsettings.Development.json overriding configuration
• Hardcoded connection string inside IDesignTimeDbContextFactory
• Multiple DbContext registrations
• SQL Server Browser service stopped
• Environment variables overriding configuration

Best Practices

• Store connection strings in configuration files or secret stores.
• Avoid hardcoding connection strings.
• Keep only one DbContext registration.
• Use dependency injection consistently.
• Keep Entity Framework Core package versions aligned.
• Use verbose logging during migration debugging.
• Verify the startup project before running migrations.
• Test SQL Server connectivity using sqlcmd or SQL Server Management Studio before debugging Entity Framework Core.

Before spending hours debugging, verify the following:

• SQL Server service is running.
• SQL Server instance name is correct.
• Connection string is valid.
• Database exists or migrations are ready to create it.
• Startup project is correct.
• DbContext registration is correct.
• No configuration overrides exist.
• Design-time DbContext factory uses the correct connection string.
• TCP/IP is enabled.
• SQL Server Browser service is running.

Conclusion
The "Named Pipes Provider, Error 40" exception is one of the most common SQL Server connectivity issues in ASP.NET Core applications.
In most cases, the problem is not SQL Server itself, but a configuration mismatch between the application, Entity Framework Core, and the SQL Server instance.
By following a structured troubleshooting approach—verifying SQL Server connectivity, validating the connection string, checking dependency injection, confirming the startup project, reviewing configuration files, and inspecting Entity Framework Core's design-time configuration—you can identify the root cause quickly and resolve it with confidence.

Always begin by confirming that SQL Server is reachable using sqlcmd or SQL Server Management Studio before investigating your application. A methodical process saves time, reduces frustration, and leads to faster, more reliable solutions.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Data that doesn't necessarily fit cleanly into defined database columns is frequently used in modern applications. Records may differ in terms of user choices, product characteristics, application settings, logs, and API answers. Many developers decide to save this kind of data as JSON rather than generating dozens of optional columns.

The JSONB data type in PostgreSQL stores JSON in a binary format that is ideal for indexing and querying. JSONB is a popular option for high-traffic applications because it combines the efficiency of a relational database with the flexibility of JSON.

But JSON data storage is insufficient on its own. As your data expands, JSONB columns may become a performance barrier if suitable indexing and query optimization are not implemented.

This post will teach you how JSONB functions, typical performance issues, and useful methods for PostgreSQL JSONB query optimization.

What Is JSONB?
JSONB is a PostgreSQL data type that stores JSON documents in a binary format.

Unlike plain JSON, JSONB:

• Removes unnecessary whitespace
• Stores data in a format optimized for searching
• Supports efficient indexing
• Allows fast querying of nested values

For example, you can store product details like this:
CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    Name TEXT,
    Details JSONB
);

A sample record might contain:
{
  "brand": "Contoso",
  "color": "Black",
  "storage": "256GB",
  "wirelessCharging": true
}

This approach allows you to store flexible attributes without constantly changing your database schema.

Querying JSONB Data
PostgreSQL provides operators to read values from JSONB columns.

For example, to retrieve the product brand:
SELECT Details ->> 'brand'
FROM Products;

To filter products by color:
SELECT *
FROM Products
WHERE Details ->> 'color' = 'Black';

These queries are easy to write, but performance can decrease if the table contains millions of rows and no indexes are available.

Use GIN Indexes
One of the biggest advantages of JSONB is that it supports indexing.
For most JSONB search scenarios, a GIN (Generalized Inverted Index) provides excellent performance.

Create a GIN index like this:
CREATE INDEX idx_products_details
ON Products
USING GIN (Details);

Instead of scanning every row, PostgreSQL can use the index to locate matching records much more efficiently.

For applications with frequent JSON searches, this is one of the most effective optimizations.

Avoid Storing Everything in JSONB
Although JSONB is flexible, it should not replace every database column.

For example, avoid storing frequently queried values like this:
{
  "price": 999.99,
  "category": "Laptop"
}

If your application filters or sorts by price or category regularly, these values should usually be stored in dedicated columns.

A better design might look like:
CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    Name TEXT,
    Category TEXT,
    Price NUMERIC,
    Details JSONB
);

Use JSONB for optional or dynamic attributes, while keeping frequently accessed fields in standard columns.

Query Only the Data You Need
Avoid returning entire JSON documents when you only need a few values.

Instead of:
SELECT Details
FROM Products;

Retrieve only the required field:
SELECT Details ->> 'brand'
FROM Products;

Fetching less data reduces network traffic and improves query performance.

Keep JSON Documents Small
Large JSON documents consume more storage and require more processing time.
Instead of storing unrelated information in a single JSONB column, split data into logical sections.
For example, avoid combining:

• Product specifications
• Customer reviews
• Inventory history
• Shipping information

into one large JSON document.
Smaller JSON documents are easier to query and maintain.

Monitor Query Performance
PostgreSQL provides tools to identify slow queries.

Use:
EXPLAIN ANALYZE
SELECT *
FROM Products
WHERE Details ->> 'brand' = 'Contoso';

This command shows how PostgreSQL executes the query and whether indexes are being used.

If a query performs a sequential scan instead of using an index, it may indicate that further optimization is needed.

Regular performance monitoring helps identify bottlenecks before they affect users.
Use JSONB Operators Efficiently

PostgreSQL includes several operators for working with JSONB.

Some commonly used operators are:

Choosing the appropriate operator can improve query readability and performance.

Best Practices
When working with JSONB in high-traffic PostgreSQL applications, follow these recommendations:

• Use JSONB instead of JSON when querying data frequently.
• Create GIN indexes for searchable JSONB columns.
• Store frequently filtered fields in dedicated database columns.
• Keep JSON documents as small as practical.
• Select only the JSON properties required by the application.
• Monitor query execution plans using EXPLAIN ANALYZE.
• Avoid unnecessary nesting in JSON documents.
• Test query performance with production-like datasets before deployment.

Conclusion
With JSONB, PostgreSQL can store semi-structured data with the same flexibility and relational database performance. It allows developers to create applications that can adapt to changing data structures without compromising query performance when utilized properly.

The secret to success is striking a balance between solid database design and adaptability. You can create high-traffic applications that are quick, scalable, and simple to manage as your data expands by indexing JSONB columns, keeping documents small, storing commonly accessed fields separately, and keeping an eye on query speed.