Unit and Integration Testing with Claude Code & Codex: .NET Core, Angular/React, and SQL Server

1 Architectural Strategy & AI Agent Workflows for Legacy Test Automation

Adding unit and integration tests to a mature .NET, Angular/React, and SQL Server application is rarely a simple test-generation exercise. The difficult part is understanding undocumented behavior, deciding what should be isolated versus tested against real infrastructure, and creating tests that developers can trust after the AI agent has finished.

Claude Code and OpenAI Codex are useful here because they can work across a repository rather than generating isolated test snippets. They can inspect implementation code, DTOs, interfaces, configuration, existing tests, SQL scripts, and build failures; modify files; execute test commands; and iterate on failures.

The recommended architecture is therefore not:

Source Code -> AI -> Generate Tests

It is:

Repository
   |
   v
Discover Architecture and Existing Conventions
   |
   v
Identify Test Boundary
   |
   +--> Characterization Tests for Legacy Behavior
   +--> Unit Tests for Isolated Business Logic
   +--> Integration Tests for APIs / SQL / External Boundaries
   |
   v
Generate Tests
   |
   v
Build + Execute + Analyze Failure
   |
   v
Refine Test or Production Code
   |
   v
Human Review
   |
   v
Commit

The difference matters. AI-generated tests become useful only when the agent is allowed to validate what it writes.

1.1 Comparing Modern AI Coding Agents: Claude Code CLI vs. OpenAI Codex / GitHub Copilot Agents

Claude Code and Codex both support repository-aware, agentic development, but teams should avoid designing a testing process around vendor-specific prompts.

Claude Code operates directly against the working repository and provides filesystem operations, shell execution, project instructions, skills, hooks, subagents, and MCP integrations. A project-level CLAUDE.md can describe architecture and testing standards that Claude should use across sessions.

Codex similarly supports repository-level instructions through AGENTS.md, which makes it possible to keep coding and testing conventions with the codebase rather than repeatedly embedding them in prompts.

GitHub has also expanded beyond the earlier Copilot Workspaces concept. Current Copilot agent capabilities can research repositories, make changes in isolated workspaces, run tests, work on branches, and create pull requests for human review. GitHub also supports Claude and Codex as third-party coding agents for eligible Copilot configurations.

For a development organization, the better abstraction is:

Requirement	Claude Code	Codex / Copilot Agent
Repository analysis	Strong	Strong
Modify multiple files	Yes	Yes
Execute builds/tests	Yes	Yes
Repository instructions	CLAUDE.md	AGENTS.md / Copilot instructions
Repeatable workflows	Skills	Agent/instruction workflows
Human PR review	Supported through workflow	Strong GitHub integration
Best use	Developer-driven local automation	Local and delegated repository tasks

Do not create completely different testing processes for each tool. Store the testing policy inside the repository and make prompts thin wrappers around that policy.

1.2 System Prerequisites & Codebase Readiness for AI-Assisted Testing

Before asking an agent to generate hundreds of tests, make the repository testable.

At minimum, the agent should be able to run these operations without manual intervention:

dotnet restore
dotnet build
dotnet test

npm ci
npm run test

The exact commands may differ, but they should be deterministic.

A useful repository structure is:

src/
  WebApi/
  Application/
  Domain/
  Infrastructure/

tests/
  Domain.UnitTests/
  Application.UnitTests/
  WebApi.IntegrationTests/
  Database.IntegrationTests/

frontend/
  src/
  tests/

database/
  schema/
  procedures/
  views/
  tests/

Legacy applications often fail this readiness test. Business logic may be embedded in controllers, static helpers may access databases directly, Angular components may create HTTP dependencies internally, and stored procedures may contain critical business rules with no executable specification.

Do not ask the AI to mock everything around these designs. First establish seams.

Incorrect:

public decimal CalculateTotal(int orderId)
{
    using var connection =
        new SqlConnection(Environment.GetEnvironmentVariable("DB"));

    // query database and calculate total
}

Better:

public sealed class OrderPricingService(IOrderRepository repository)
{
    public async Task<decimal> CalculateTotal(int orderId)
    {
        var order = await repository.Get(orderId);
        return order.Items.Sum(x => x.Quantity * x.UnitPrice);
    }
}

Now the calculation can be unit tested while repository behavior is covered separately through integration tests.

This is one of the areas where coding agents provide more value than simple code completion: they can identify these seams, perform a constrained refactoring, run existing tests, and then introduce new tests.

1.3 Context Indexing & Agent Memory Setup: Crafting CLAUDE.md and Custom Commands for Test Conventions

Prompt quality improves significantly when architectural rules are separated from individual tasks.

A small CLAUDE.md might contain:

# Testing Standards

Backend:
- Use xUnit.
- Use NSubstitute for application-layer dependencies.
- Prefer FluentAssertions.
- Do not mock EF Core DbContext for integration tests.
- API integration tests must use WebApplicationFactory.
- SQL-dependent integration tests must use SQL Server containers.

Frontend:
- Prefer Testing Library queries based on user-visible behavior.
- Use MSW for HTTP boundaries.
- Do not test private implementation details.

General:
- Follow Arrange / Act / Assert.
- Test observable behavior.
- Do not modify production behavior simply to make a test pass.
- Run the smallest relevant test suite after every change.

Claude Code reads CLAUDE.md as project context. Repeatable procedures can now be implemented as Skills rather than making CLAUDE.md enormous. Anthropic currently recommends skills for reusable workflows; existing .claude/commands files remain supported, but skills provide additional capabilities and can load only when required.

For example:

.claude/
  skills/
    generate-tests/
      SKILL.md

The skill could instruct the agent to inspect implementation code, locate existing tests, identify external dependencies, propose test cases, generate tests, run them, and fix test-only defects.

The equivalent principle should be applied to Codex through repository instructions rather than maintaining a large prompt library disconnected from source control.

1.4 Open-Source Testing Ecosystem Overview by Tier

AI does not replace the testing framework. It orchestrates the frameworks your engineering team has already selected.

1.4.1 Frontend: Vitest, React Testing Library, Spectator, and MSW

For React, a practical stack is:

Vitest
+ React Testing Library
+ user-event
+ MSW

Vitest executes tests, Testing Library validates components from the user’s perspective, and MSW intercepts network traffic at the API boundary.

For Angular, Spectator can reduce TestBed boilerplate while standard Angular testing facilities remain appropriate for dependency injection, guards, interceptors, Signals, and RxJS-heavy services.

Avoid creating large collections of shallow tests that assert implementation details such as private properties or internal method calls. The agent should primarily validate observable behavior.

1.4.2 Backend: xUnit, NSubstitute, FluentAssertions, WebApplicationFactory, WireMock.NET

For modern .NET applications, a useful division is:

Domain/Application
    -> xUnit
    -> NSubstitute
    -> FluentAssertions

HTTP API boundary
    -> WebApplicationFactory

External HTTP dependency
    -> WireMock.NET

WebApplicationFactory is particularly valuable because API tests can exercise routing, middleware, model binding, dependency injection, authentication configuration, and serialization rather than testing controllers as ordinary classes.

Mock only architectural boundaries. Mocking every class usually produces tests that reproduce implementation code rather than verify behavior.

1.4.3 Database: Testcontainers for SQL Server, Respawn, and tSQLt

SQL Server is where unrealistic mocks become particularly dangerous.

For integration tests, run an actual SQL Server instance using Testcontainers:

Test Process
   |
   +--> Start SQL Server Container
   |
   +--> Apply Schema / Migration
   |
   +--> Seed Minimal Test Data
   |
   +--> Execute Repository/API Test
   |
   +--> Respawn Database State

Respawn is useful when tests need a predictable database state without rebuilding the whole database between every test.

For logic implemented directly in stored procedures, views, functions, or triggers, tSQLt remains useful because the behavior can be tested inside SQL Server itself.

The trade-off is execution time. Database tests are slower than pure unit tests, so they should validate database behavior rather than duplicate every domain-level test.

1.4.4 Power BI / Data: DAX Studio, Tabular Editor, and Microsoft.PowerBI.Api

For data-heavy systems, testing cannot stop at the application API.

A sensible test pyramid may include:

SQL transformation tests
        |
        v
Dataset/model validation
        |
        v
DAX measure validation
        |
        v
Power BI refresh/API health checks

DAX Studio and Tabular Editor are useful for model and DAX analysis, while Power BI APIs can support operational verification such as dataset refresh state.

Keep these tests separate from ordinary application unit tests because they have different dependencies, execution times, credentials, and failure modes.

---

2 Prompt Engineering Frameworks for Test Generation

Good test-generation prompts specify boundaries and constraints rather than merely asking the model to “write unit tests.”

2.1 System Prompt Architecture: Setting Constraints, Test Determinism, and Mocking Rules

A reusable instruction should define what the agent may change.

Analyze the target code before modifying anything.

Rules:
1. Preserve existing production behavior.
2. Use the testing libraries already selected by the repository.
3. Mock only external architectural boundaries.
4. Tests must be deterministic.
5. Do not use Thread.Sleep or arbitrary timing delays.
6. Do not call production APIs.
7. Prefer behavior-based assertions.
8. Run the generated tests.
9. If a test fails, determine whether the test or implementation is wrong.
10. Do not change production logic solely to force a generated test to pass.

That final rule is important. Autonomous agents can otherwise “solve” a failing test by altering production code until the test becomes green.

2.2 Context Window Optimization: Scoping Interfaces, Schemas, and DTOs without Context Bloat

More context does not automatically mean better tests.

For a service test, the agent normally needs:

Target class
+ Interfaces it calls
+ Domain models / DTOs
+ Existing test conventions
+ Relevant configuration
+ Related business rules

It usually does not need the entire repository.

Ask the agent to discover dependencies first:

Analyze OrderService.cs.

Before writing tests:
- identify its direct dependencies,
- locate related DTOs and domain models,
- inspect existing Order-related tests,
- identify external boundaries,
- list the behaviors that require coverage.

Then generate tests only for OrderService.

This produces better results than dumping dozens of unrelated files into the prompt.

2.3 Prompt Pattern 2.3.1: Characterization Testing for Undocumented Legacy Logic

Characterization testing is often the safest first step for legacy code.

Analyze this legacy component as existing production behavior.

Do not refactor it yet.

Create characterization tests that capture:
- normal behavior,
- boundary conditions,
- null/empty inputs,
- exception behavior,
- important historical edge cases.

Run the tests against the current implementation.

Clearly identify any behavior that appears suspicious, but preserve it in
the characterization test unless there is evidence that it is a defect.

The objective is not to prove that legacy behavior is correct. It is to create a safety net before changing it.

2.4 Prompt Pattern 2.4.1: Test-Driven Refactoring for Untestable Code

Once characterization coverage exists, the agent can safely improve testability.

Review this class for testability problems.

First identify:
- static dependencies,
- hidden database access,
- direct HTTP calls,
- time dependencies,
- oversized methods,
- mixed responsibilities.

Propose the smallest refactoring that creates appropriate test seams.

Do not change externally observable behavior.

After refactoring:
1. run characterization tests,
2. add focused unit tests,
3. run the complete affected test suite.

This sequence is safer than asking an AI agent to simultaneously redesign production code and invent the expected behavior.

2.5 Prompt Pattern 2.5.1: Golden Master / Snapshot Testing for Complex Data Pipelines

Golden-master testing works well for complex transformations where expected output is large but deterministic.

For example:

SQL Data
   -> Transformation
   -> Business Rules
   -> Aggregation
   -> Report DTO

Instead of manually asserting hundreds of fields, capture representative approved outputs.

Given the existing transformation pipeline:

1. identify deterministic input datasets,
2. execute the current implementation,
3. normalize timestamps, generated IDs, and ordering,
4. capture approved output as golden-master fixtures,
5. create comparison tests,
6. produce a readable diff when output changes.

Never blindly update snapshots after a failure. A changed golden master should require review because the changed output may represent a regression.

---

3 Frontend Unit & Integration Testing: Angular and React

Frontend AI-assisted testing works best when tests model user behavior rather than component internals.

3.1 React Test Automation with Claude Code / Codex

3.1.1 Prompt: Unit Testing Custom Hooks and Complex State

For hooks that use Redux Toolkit or TanStack Query/React Query:

Analyze useCustomerOrders.

Create Vitest tests covering:
- initial state,
- successful query,
- loading state,
- API failure,
- query parameter changes,
- cache-sensitive behavior where relevant.

Create realistic providers required by the hook.
Do not mock React Query itself.
Mock the HTTP boundary using MSW.

Mocking React Query would remove much of what the test needs to verify.

3.1.2 Prompt: Integration Testing UI Components

Create integration tests for CustomerSearch.

Use React Testing Library and user-event.

Test:
- initial rendering,
- entering search criteria,
- submitting the search,
- loading feedback,
- successful results,
- empty results,
- API error behavior.

Prefer getByRole/getByLabelText queries.
Do not assert component state or private implementation details.

This allows internal refactoring without breaking tests unnecessarily.

3.1.3 Prompt: Mocking Network Layer Using MSW

Use MSW at the HTTP boundary:

http.get('/api/customers', ({ request }) => {
  const url = new URL(request.url);
  const name = url.searchParams.get('name');

  return HttpResponse.json([
    { id: 101, name: name ?? 'Test Customer' }
  ]);
});

The component continues making a real HTTP request from its perspective while MSW controls the response.

3.2 Angular Test Automation with Claude Code / Codex

3.2.1 Prompt: Testing Signals, RxJS, and Component Lifecycle

Analyze InventoryComponent.

Create focused Angular tests covering:
- initial Signal values,
- computed Signal updates,
- observable subscriptions,
- loading state,
- error state,
- cleanup during destruction.

Use Spectator where it reduces setup complexity.
Avoid arbitrary timers.
Use fakeAsync only where timing behavior actually requires it.

3.2.2 Prompt: Services, Interceptors, and Guards

Service tests should usually exercise Angular’s dependency-injection boundary rather than instantiate complicated services manually.

For HTTP services, use Angular’s supported HTTP testing facilities. For guards, configure realistic authentication state. For interceptors, verify observable HTTP behavior such as headers and error handling rather than calling private methods.

3.2.3 Prompt: Component Integration with Dependency Overrides

A useful agent prompt is:

Create integration tests for OrderEditorComponent.

Use the real component template and Angular dependency injection.

Replace only:
- external HTTP/service boundaries,
- authentication context,
- unstable infrastructure dependencies.

Keep internal application services real where practical.

Cover:
- initial data loading,
- validation,
- save,
- server-side validation failure,
- authorization-dependent controls.

Run the tests and correct compilation or test issues before finishing.

The important architectural rule across React and Angular is the same:

Mock infrastructure boundaries,
not the application itself.

That principle produces fewer tests, but those tests survive refactoring and catch substantially more meaningful regressions.

The AI coding agent should therefore be treated as a test engineer working inside an established testing architecture—not as a test-case generator. Once repository instructions, deterministic execution, test boundaries, and human review are in place, Claude Code and Codex can significantly accelerate legacy test automation without turning the test suite into generated technical debt.

---

4 Backend API & Microservices Testing (.NET Core)

Backend testing becomes more valuable when tests follow the same architectural boundaries as the application. For a typical .NET service, that means keeping domain and application logic fast and isolated, while API, persistence, messaging, and downstream service behavior are validated through integration tests.

The useful split is:

Domain rules
    -> Unit tests

Application handlers
    -> Unit tests with mocked boundaries

ASP.NET Core pipeline
    -> WebApplicationFactory

SQL Server
    -> Testcontainers

External HTTP services
    -> WireMock.NET

Message publishing / consuming
    -> MassTransit test harness or real broker integration tests

The AI agent should determine which layer it is testing before generating code. A command handler should normally not start SQL Server, while an endpoint test that depends on EF Core should normally not replace the entire persistence layer with mocks.

4.1 Unit Testing Core Business Logic & MediatR Handlers

Domain entities, application services, validators, and MediatR handlers are good candidates for focused unit testing because their behavior should be deterministic when infrastructure dependencies are substituted.

Consider an order command handler:

public sealed class ApproveOrderHandler(
    IOrderRepository repository,
    IEventPublisher publisher)
{
    public async Task Handle(ApproveOrder command, CancellationToken ct)
    {
        var order = await repository.GetAsync(command.OrderId, ct);

        order.Approve();

        await repository.SaveAsync(order, ct);
        await publisher.PublishAsync(
            new OrderApproved(order.Id),
            ct);
    }
}

The important behavior is not the internal sequence of every method call. The test should verify the domain outcome and meaningful interactions with architectural boundaries.

4.1.1 Prompt: Generating xUnit Tests for Domain Entities and Command Handlers using NSubstitute

A useful generation prompt gives the agent explicit behavioral boundaries:

Analyze ApproveOrderHandler and the Order domain entity.

Generate xUnit tests using NSubstitute.

Cover:
- valid order approval,
- already-approved order,
- missing order,
- domain validation failure,
- persistence behavior,
- OrderApproved event publication.

Rules:
- do not mock the Order entity,
- mock repository and event-publisher boundaries only,
- assert domain state where possible,
- verify important external interactions,
- do not verify incidental method-call ordering,
- run the generated tests before finishing.

A resulting test may look like:

[Fact]
public async Task Handle_ShouldPublishEvent_WhenOrderIsApproved()
{
    var repository = Substitute.For<IOrderRepository>();
    var publisher = Substitute.For<IEventPublisher>();

    var order = Order.Create(123);
    repository.GetAsync(123, Arg.Any<CancellationToken>())
        .Returns(order);

    var handler = new ApproveOrderHandler(repository, publisher);

    await handler.Handle(
        new ApproveOrder(123),
        CancellationToken.None);

    order.Status.Should().Be(OrderStatus.Approved);

    await publisher.Received(1).PublishAsync(
        Arg.Is<OrderApproved>(x => x.OrderId == 123),
        Arg.Any<CancellationToken>());
}

The agent should also look for negative-path behavior. If Order.Approve() rejects cancelled orders, that rule belongs in a domain test rather than being duplicated across every handler test.

4.1.2 Prompt: AAA Enforcement with FluentAssertions and Fixture Generators

Generated test suites often become difficult to maintain because every test creates large object graphs differently. Fixture generators can help, but uncontrolled random data creates another problem: failures become harder to reproduce.

Use fixtures for irrelevant data while keeping values involved in assertions explicit.

var customer = fixture.Build<Customer>()
    .With(x => x.Status, CustomerStatus.Active)
    .Create();

A suitable AI prompt is:

Refactor the tests for CustomerPricingService.

Use Arrange-Act-Assert consistently.

Use FluentAssertions for result assertions.

Use the project's existing fixture generator for unrelated object data,
but explicitly specify any value that affects business behavior.

Avoid:
- random values in expected calculations,
- shared mutable fixture state,
- assertions against every property,
- one test covering multiple business rules.

Run all affected tests after refactoring.

This gives generated tests a predictable shape without turning AAA into unnecessary ceremony.

4.2 Integration Testing Microservice Endpoints

Unit tests cannot verify route configuration, JSON serialization, middleware, filters, authentication, dependency injection, or actual persistence mappings. ASP.NET Core’s WebApplicationFactory<TEntryPoint> provides a test host for these scenarios and supports replacing test-specific services and configuration. Microsoft recommends keeping integration testing focused on important infrastructure scenarios rather than duplicating every unit-test permutation.

4.2.1 Prompt: WebApplicationFactory Setup with Custom In-Memory / Testcontainer Configuration

For business-critical persistence behavior, prefer the actual database engine over EF Core’s in-memory provider. SQL Server-specific behavior such as constraints, transaction semantics, computed columns, raw SQL, stored procedures, indexes, and collation will not be represented accurately by a generic in-memory store.

A test factory can inject a container connection string:

public sealed class ApiFactory : WebApplicationFactory<Program>
{
    private readonly string _connectionString;

    public ApiFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");

        builder.ConfigureAppConfiguration((_, configuration) =>
        {
            configuration.AddInMemoryCollection(
                new Dictionary<string, string?>
                {
                    ["ConnectionStrings:AppDb"] = _connectionString
                });
        });
    }
}

WebApplicationFactory starts the application through ASP.NET Core’s test infrastructure, allowing requests to pass through the application pipeline rather than invoking controller methods directly.

Use an AI prompt such as:

Create integration tests for POST /api/orders.

Use WebApplicationFactory<Program>.

Use the real application registrations except infrastructure that must be
replaced for testing.

Connect persistence to the existing SQL Server Testcontainer fixture.

Cover:
- successful creation,
- invalid request validation,
- duplicate business key,
- unauthorized request,
- persisted database result.

Assert both HTTP behavior and relevant persisted state.

4.2.2 Prompt: Mocking Downstream Microservice HTTP Calls using WireMock.NET

A microservice should not call another live service during normal integration testing. But replacing the typed HttpClient with a mocked C# interface can hide errors in URLs, headers, serialization, retry behavior, and HTTP status handling.

WireMock.NET provides a lightweight HTTP server where requests and responses can be defined explicitly. It can match paths, HTTP methods, headers, query parameters, and request bodies, and can also simulate delays and faults.

using var wireMock = WireMockServer.Start();

wireMock
    .Given(
        Request.Create()
            .WithPath("/api/inventory/ABC123")
            .UsingGet())
    .RespondWith(
        Response.Create()
            .WithStatusCode(200)
            .WithHeader("Content-Type", "application/json")
            .WithBody("""
            {
              "sku": "ABC123",
              "availableQuantity": 25
            }
            """));

The application’s downstream base URL is then pointed to wireMock.Url.

A strong agent prompt is:

Create integration tests for the inventory dependency used by Order API.

Use WireMock.NET rather than mocking HttpClient.

Test:
- HTTP 200 response,
- 404 product not found,
- HTTP 500,
- timeout behavior,
- malformed downstream response.

Verify the outgoing path and required headers.

Do not call the actual inventory service.

This tests the real HTTP client code while keeping the integration environment deterministic.

4.2.3 Prompt: Testing Asynchronous Event Producers & Consumers

Messaging requires a different mindset because successful method completion does not necessarily mean the business workflow completed.

For a command that publishes OrderCreated, test at least:

API request
   |
   v
Database commit
   |
   v
Message published
   |
   v
Consumer receives message
   |
   v
Expected consumer side effect

For fast component-level tests, MassTransit test infrastructure can be used to observe published, sent, and consumed messages. For higher-confidence integration testing, use a real RabbitMQ instance in a container and validate broker configuration, serialization, routing, retries, and consumer registration.

A useful prompt is:

Create messaging tests for OrderCreatedConsumer.

Validate:
- OrderCreated is consumed,
- correct business action occurs,
- duplicate message handling is idempotent,
- invalid messages do not corrupt state,
- transient dependency failures follow configured retry behavior.

Use the project's MassTransit test infrastructure for fast tests.

Where broker-specific behavior matters, create a separate integration test
against RabbitMQ rather than simulating broker semantics.

Avoid arbitrary Task.Delay calls while waiting for asynchronous results. Tests should observe the messaging framework or wait against a bounded condition; otherwise intermittent CI failures will eventually appear.

---

5 SQL Server Database Testing: Views, Stored Procedures, & Triggers

Database testing should verify behavior that actually belongs to SQL Server. This includes stored procedures, complex views, triggers, database constraints, EF Core mappings, Dapper queries, and production SQL that cannot be meaningfully validated using mocks.

The goal is not to test SQL Server itself. It is to verify that application assumptions and database behavior remain aligned.

5.1 Unit Testing Legacy Stored Procedures & Views

Legacy databases often contain significant business logic that is invisible to the .NET layer. Rewriting this logic purely to make it testable creates unnecessary migration risk.

tSQLt provides SQL Server-native testing capabilities including assertions, fake tables, fake functions, and procedure spies.

5.1.1 Prompt: Writing tSQLt Unit Tests for Complex SQL Functions and Views

Consider a reporting view that calculates invoice balances.

A focused tSQLt test can fake source tables, insert only relevant rows, execute the view, and compare the output against an expected table.

EXEC tSQLt.FakeTable 'Billing', 'Invoice';
EXEC tSQLt.FakeTable 'Billing', 'Payment';

INSERT INTO Billing.Invoice
    (InvoiceId, CustomerId, Amount)
VALUES
    (1, 100, 500.00);

INSERT INTO Billing.Payment
    (InvoiceId, Amount)
VALUES
    (1, 200.00);

SELECT InvoiceId, Balance
INTO actual
FROM Billing.vwInvoiceBalance;

CREATE TABLE expected
(
    InvoiceId INT,
    Balance DECIMAL(18,2)
);

INSERT INTO expected VALUES (1, 300.00);

EXEC tSQLt.AssertEqualsTable
    'expected',
    'actual';

tSQLt.AssertEqualsTable compares expected and actual table contents, making it useful for testing deterministic view or transformation output.

The generation prompt should require minimal test datasets:

Analyze Billing.vwInvoiceBalance and its dependent objects.

Generate tSQLt tests covering:
- no payments,
- partial payment,
- full payment,
- multiple payments,
- cancelled invoice behavior.

Fake only source tables required by the view.
Use the smallest dataset that proves each rule.
Use AssertEqualsTable for result comparison.

5.1.2 Prompt: Mocking Tables and Isolating Side Effects in Stored Procedure Tests

A stored procedure may update tables and invoke another procedure that sends notifications or performs unrelated work. Unit tests should isolate those dependencies.

tSQLt.SpyProcedure replaces a called procedure and records the parameters passed to it, allowing the parent procedure to be tested independently.

EXEC tSQLt.FakeTable 'Orders', 'Order';
EXEC tSQLt.SpyProcedure 'Notifications.SendOrderApproved';

INSERT INTO Orders.[Order]
    (OrderId, Status)
VALUES
    (101, 'Pending');

EXEC Orders.ApproveOrder @OrderId = 101;

EXEC tSQLt.AssertEquals
    'Approved',
    (SELECT Status
     FROM Orders.[Order]
     WHERE OrderId = 101);

The AI agent should distinguish between testing the procedure’s own logic and testing the procedure it invokes. Otherwise database tests quickly become tightly coupled chains of stored procedures.

5.2 Integration Testing SQL Layer via .NET Core

Database integration tests should execute the same SQL provider and schema used by production wherever practical.

5.2.1 Prompt: Testcontainers.SqlServer Provisioning and Auto-Migration Script Execution

Testcontainers for .NET provides a dedicated SQL Server module that starts SQL Server in a container and exposes a normal connection string that can be used by EF Core, Dapper, or direct ADO.NET.

private readonly MsSqlContainer _sql =
    new MsSqlBuilder(
        "mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04")
    .Build();

public async Task InitializeAsync()
{
    await _sql.StartAsync();

    await ApplyDatabaseMigrations(
        _sql.GetConnectionString());
}

A production-oriented prompt is:

Create a reusable SQL Server integration-test fixture.

Requirements:
- start SQL Server using Testcontainers,
- apply the application's real migrations,
- expose the generated connection string,
- fail immediately if migration fails,
- reuse the container across an appropriate test collection,
- dispose resources after the suite.

Do not hard-code host ports.

Container startup is relatively expensive, so sharing infrastructure at the test-collection level is usually preferable to launching SQL Server for every individual test.

5.2.2 Prompt: State Reset Strategy with Respawn between Integration Test Runs

Once the database schema is created, tests still need isolation.

Recreating the entire SQL Server container before every test provides strong isolation but is unnecessarily expensive for many suites. Respawn takes a different approach: it analyzes table relationships and resets data in dependency-aware order while allowing selected tables or schemas to be preserved.

_respawner = await Respawner.CreateAsync(
    connection,
    new RespawnerOptions
    {
        DbAdapter = DbAdapter.SqlServer,
        TablesToIgnore =
        [
            new Table("__EFMigrationsHistory")
        ]
    });

Before the next test:

await _respawner.ResetAsync(connection);

Keep reference data only when it is truly static. If tests depend heavily on shared seed data, they become order-dependent and harder to understand.

5.2.3 Prompt: Validating Dapper and EF Core Raw SQL Queries against Real Instances

Raw SQL is exactly where mocked database tests provide the least confidence. Column aliases, parameter types, null handling, joins, SQL syntax, stored procedure result shapes, and Dapper object mappings are runtime behaviors.

A useful test executes the actual query:

await using var connection =
    new SqlConnection(testDatabase.ConnectionString);

var result = await connection.QuerySingleAsync<OrderSummary>(
    """
    SELECT
        o.OrderId,
        o.OrderNumber,
        SUM(oi.Quantity * oi.UnitPrice) AS Total
    FROM Orders o
    JOIN OrderItems oi ON oi.OrderId = o.OrderId
    WHERE o.OrderId = @OrderId
    GROUP BY o.OrderId, o.OrderNumber
    """,
    new { OrderId = orderId });

result.Total.Should().Be(125.50m);

The same principle applies to EF Core’s FromSql, ExecuteSql, stored-procedure mappings, and database-generated values.

Use the agent with a narrow objective:

Review all Dapper and EF Core raw SQL in OrderRepository.

Create SQL Server integration tests for the highest-risk queries.

Validate:
- parameter binding,
- null handling,
- joins,
- decimal precision,
- expected result shape,
- empty-result behavior,
- database constraints.

Run against the existing SQL Server Testcontainer.

Do not replace the database with mocks or an in-memory provider.

This is where AI-assisted testing delivers practical value: the agent can trace SQL from repository code to schema definitions, construct minimal datasets, execute the real query, and turn previously implicit database assumptions into repeatable tests.

---

6 Power BI Data Model, DAX, & Report Verification

Application and database tests can pass while reporting is still wrong. A renamed SQL column, changed relationship, incorrect DAX filter, stale semantic model, or broken Row-Level Security rule can produce incorrect business reports without generating an application error.

For Power BI-heavy systems, treat the reporting layer as another deployable component:

SQL Server
    |
    v
Views / Reporting Tables
    |
    v
Power BI Semantic Model
    |
    +--> Relationships
    +--> Measures
    +--> RLS
    |
    v
Reports / Dashboards

Testing should therefore validate data calculations, security behavior, schema compatibility, and operational refresh status rather than only checking whether a .pbix file can be opened.

6.1 Automated DAX Measure Validation

DAX measures frequently contain business rules that are just as important as rules implemented in C# or SQL. Revenue, inventory availability, utilization, percentages, period comparisons, and KPI calculations should be validated against known input scenarios.

The safest pattern is to define small datasets where expected business results are independently known and execute measures against those datasets.

6.1.1 Prompt: Generating DAX Unit Tests via DAX Studio / C# Scripts for Tabular Editor

Consider a measure:

Net Sales :=
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice] - Sales[DiscountAmount]
)

The measure looks simple, but behavior becomes less obvious once filters, currencies, inactive relationships, and time intelligence are introduced.

A useful agent prompt is:

Analyze the Net Sales measure and all DAX measures it depends on.

Create validation scenarios covering:
- single order,
- multiple line items,
- discounts,
- zero quantity,
- blank values,
- customer filtering,
- date filtering.

For each scenario:
1. define the minimum source data,
2. calculate the expected result independently,
3. execute the measure against the model,
4. compare actual and expected values.

Do not derive the expected value by copying the DAX implementation.

DAX Studio can execute DAX queries against compatible semantic models, while Tabular Editor scripting can inspect and automate model-level operations. The important design principle is independence: expected values should come from business scenarios rather than reproducing the same expression in test code.

For example:

EVALUATE
ROW(
    "ActualNetSales", [Net Sales],
    "ExpectedNetSales", 1425.50
)

For larger suites, a C# test harness can submit queries, deserialize results, and produce standard test output for CI.

6.1.2 Prompt: Validating Row-Level Security Rules against Test User Profiles

RLS errors are particularly dangerous because the report may still work perfectly for developers or administrators.

Power BI RLS uses roles and DAX filter expressions to restrict rows available to a user. Dynamic implementations frequently rely on functions such as USERPRINCIPALNAME() and mapping tables.

A typical model might contain:

UserRegionAccess

UserEmail                Region
--------------------------------
alice@company.com         East
bob@company.com           West
manager@company.com       East
manager@company.com       West

An agent should test security as data behavior:

Create RLS validation scenarios for the RegionalSales role.

Test these identities:
- East-region user,
- West-region user,
- multi-region manager,
- user with no region assignment.

For each user verify:
- visible regions,
- visible customer count,
- sales total,
- absence of unauthorized records.

Do not only verify that the RLS role exists.
Verify the resulting data visibility.

Also test workspace permissions separately. Power BI documentation notes that RLS behavior depends on semantic-model and workspace permissions; users with elevated write-level workspace access are not constrained in the same way as report viewers.

6.2 Power BI Integration and Schema Parity Testing

Many reporting defects originate outside DAX. A deployment can succeed while the Power BI model still references an obsolete SQL column, unexpected data type, or renamed reporting view.

Schema compatibility should therefore be validated before semantic-model refresh.

6.2.1 Prompt: Cross-Verifying SQL Server View Outputs against Power BI Dataset Schemas

Suppose Power BI expects:

vwSalesSummary
    CustomerId      Int64
    CustomerName    String
    SalesDate       Date
    NetAmount       Decimal

The CI pipeline can query SQL metadata and compare it with the expected semantic-model contract.

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'vwSalesSummary'
ORDER BY ORDINAL_POSITION;

Use an agent prompt such as:

Compare all SQL Server reporting views consumed by the Power BI model
against the model's expected tables and columns.

Detect:
- missing columns,
- renamed columns,
- incompatible types,
- unexpected nullability changes,
- removed views.

Generate a machine-readable compatibility report.

Fail only on changes that can break the semantic model.
Report additive compatible changes separately.

This creates a deployment check at the correct boundary rather than discovering schema mismatches during the next scheduled report refresh.

6.2.2 Prompt: Automated REST API Verification for Dataset Refresh and Pipeline Health

A successful deployment should also verify that the semantic model can refresh.

Power BI exposes REST operations for starting refreshes and retrieving refresh history or execution details. A refresh request is asynchronous: the API can accept the operation before processing completes, so the pipeline must check the eventual result rather than treating the initial response as success.

A simplified C# check might be:

var response = await powerBiClient.PostAsync(
    $"groups/{workspaceId}/datasets/{datasetId}/refreshes",
    new StringContent("{}", Encoding.UTF8, "application/json"));

response.EnsureSuccessStatusCode();

var status = await WaitForRefreshCompletionAsync(
    workspaceId,
    datasetId,
    cancellationToken);

status.Should().Be("Completed");

The CI test should report refresh duration, failure details, dataset identity, and deployment version so operational failures can be traced quickly.

---

7 Cross-Service Microservices End-to-End & Contract Testing

Once individual services are tested independently, the next risk is compatibility between them. Running every microservice together for every pull request is expensive and often produces failures unrelated to the code being changed.

Consumer-driven contract testing addresses this by verifying the specific interactions that consumers actually depend on.

7.1 Consumer-Driven Contract Testing with PactNet

Pact treats an interaction as a contract between a consumer making a request and a provider supplying the response. PactNet currently supports Pact specification versions through v4, with documented implementation-specific limitations.

The flow is:

Consumer Test
    |
    v
Generate Pact
    |
    v
Pact Broker
    |
    v
Provider Verification
    |
    v
Deployment Compatibility Decision

This does not replace provider functional testing. Pact’s guidance specifically recommends keeping consumer tests focused on what the consumer needs rather than attempting to test every provider behavior.

7.1.1 Prompt: Generating Pact Consumer Tests in Angular/React and .NET

Imagine an Order service calling Customer API:

GET /api/customers/1001

The consumer only relies on:

{
  "id": 1001,
  "name": "Contoso School",
  "status": "Active"
}

A useful prompt is:

Analyze CustomerApiClient and its usage by Order Service.

Generate Pact consumer tests for every provider interaction
required by this client.

Define contracts for:
- active customer,
- missing customer,
- suspended customer.

Include only response fields actually consumed by Order Service.

Use Pact matchers where values may legitimately vary.
Do not create unnecessarily strict whole-response contracts.

This last point matters because requiring every provider field creates brittle contracts that prevent harmless provider changes.

7.1.2 Prompt: Generating Pact Provider Verification Tests in Backend Microservices

The provider pipeline should verify published consumer contracts against the running API.

Create PactNet provider verification for Customer API.

Requirements:
- start the API in test mode,
- retrieve applicable consumer contracts,
- configure provider states,
- seed minimum required data,
- verify every interaction,
- publish verification results in CI.

Do not mock the controller or HTTP endpoint being verified.
Mock only dependencies outside the provider's ownership boundary.

Provider states should establish business scenarios such as “customer 1001 exists and is active” before Pact executes the relevant request. Pact recommends provider verification as the mechanism that demonstrates the real provider satisfies consumer-generated interactions.

7.2 Orchestrated Local Integration Environments

Contract tests reduce the need for large environments, but some workflows still require multiple real components. Examples include authentication propagation, broker routing, database transactions across workflows, and startup/configuration validation.

These tests should remain limited to high-value journeys.

7.2.1 Prompt: Writing Docker-Compose & Testcontainers Scripts for Multi-Service Test Runs

A practical environment might contain:

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest

  rabbitmq:
    image: rabbitmq:management

  customer-api:
    build: ./Customer.Api

  order-api:
    build: ./Order.Api
    depends_on:
      - sqlserver
      - rabbitmq
      - customer-api

For .NET-controlled integration suites, Testcontainers provides programmatic lifecycle management and wait strategies, which is usually preferable when the test itself should create and destroy infrastructure.

Use Docker Compose when developers also need a manually accessible environment. Use Testcontainers when infrastructure belongs to automated test execution.

---

8 CI/CD Pipeline Automation, Maintenance, & Quality Gates

The final step is making testing part of normal delivery rather than a separate activity initiated manually.

A useful pipeline separates fast feedback from expensive verification:

Pull Request
   |
   +--> Build
   +--> Unit Tests
   +--> Frontend Tests
   +--> Contract Tests
   |
   v
Integration Stage
   |
   +--> SQL Server
   +--> API Integration
   +--> Messaging
   |
   v
Quality Gates
   |
   +--> Coverage
   +--> Mutation Testing
   +--> Security

8.1 Integrating AI Agents into Azure DevOps and GitHub Actions Pipelines

AI agents should analyze CI output, not become an uncontrolled deployment authority.

GitHub Actions can run normal dotnet build and dotnet test commands and persist test results as workflow artifacts for later inspection. A similar architecture can be implemented in Azure DevOps.

- name: Test
  run: >
    dotnet test
    --configuration Release
    --logger trx
    --results-directory TestResults

- name: Upload test results
  if: always()
  uses: actions/upload-artifact@v4
  with:
    name: test-results
    path: TestResults

An AI testing agent can consume failed logs, identify the owning component, inspect the related code, and propose a correction through a branch or pull request. The pipeline—not the AI—is still responsible for proving that the change passes required quality gates.

8.2 Automated Flaky Test Remediation Workflows

Flaky tests need diagnosis rather than automatic retries. Retries can temporarily reduce pipeline noise while hiding race conditions, shared state, timing dependencies, or environmental assumptions.

Classify repeated failures by evidence:

Failure
   |
   +--> Deterministic regression
   +--> Timing / concurrency
   +--> Shared database state
   +--> External dependency
   +--> Resource exhaustion
   +--> Test-order dependency

8.2.1 Prompt: Analyzing Failed Test Logs and Auto-Applying Fixes with Claude Code CLI

A controlled remediation prompt should be conservative:

Analyze the attached CI failure for CustomerApi.IntegrationTests.

Inspect:
- failing test,
- production code involved,
- previous failures if available,
- setup and cleanup logic,
- asynchronous waits,
- shared resources.

Determine whether the failure is:
1. production regression,
2. defective test,
3. flaky test,
4. infrastructure failure.

Only modify code when evidence supports the change.

Do not:
- increase arbitrary delays,
- remove assertions,
- skip the test,
- add unconditional retries.

Run the failing test repeatedly and then run the affected suite.
Provide the root cause with the proposed change.

This turns the agent into a diagnostic assistant instead of a mechanism for making pipelines artificially green.

8.3 Governance Metrics: Code Coverage Thresholds, Mutation Testing, and Security Audit

Coverage is useful for identifying untested code, but a high coverage percentage does not prove that assertions can detect defects. Mutation testing adds another signal by intentionally changing production code and checking whether tests fail.

Stryker.NET supports configurable mutation-score thresholds and can fail CI when the score drops below a defined break threshold.

{
  "stryker-config": {
    "thresholds": {
      "high": 80,
      "low": 65,
      "break": 60
    }
  }
}

Do not immediately enforce aggressive global thresholds on a large legacy system. Establish the baseline first, then enforce stronger requirements on newly changed or business-critical areas.

A balanced engineering scorecard is more useful:

Unit / Integration Tests        PASS
Critical Contract Tests         PASS
Changed-Code Coverage           >= agreed threshold
Mutation Score                  >= agreed baseline
High/Critical Security Issues   0
Flaky Test Rate                 monitored
Power BI Refresh Verification   PASS

Claude Code or Codex can help analyze these results and identify weak test areas, but governance thresholds should remain deterministic configuration owned by the engineering team. AI can recommend where coverage or mutation resistance needs improvement; the CI/CD platform should make the final pass/fail decision.